How to detect BCP success/fail in batch file?

We are calling BCP from a batch file but cannot figure out how to detect
whether it was successful or if it failed. With osql we use EXIT() with a
value to indicate success or failure. Is there a way to do this with BCP? Or
does it report success or failure to the calling process or set an operating
system error value?
TIA
Michael MacGregor
Database Architect
Michael MacGregor (macnoknifespam@.noemailspam.com) writes:
> We are calling BCP from a batch file but cannot figure out how to detect
> whether it was successful or if it failed. With osql we use EXIT() with
> a value to indicate success or failure. Is there a way to do this with
> BCP? Or does it report success or failure to the calling process or set
> an operating system error value?
BCP does indeed set %ERRORLEVEL%. However, your and BCP's idea of what
is an error may not be the same. BCP will return with an error status
if for instance the data file cannot be found. It may also set a return
status if the end of file is in the middle of a record (which usually
means that your format specification is wrong). But I seem to recall
that if one more rows fail to import, that it does not set ERRORLEVEL -
not even if all records fail.
You can use the -e option to get error information into a file, this
captures problems with individual records.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||Aha! When I check the ERRORLEVEL value, whether the BCP IN succeeds or fails
(failure caused by PK constraint), the value is the same, 0. However I
realised that I might not be using the latest version of BCP and checked, it
was BCP v8, so I updated my PATH to use BCP v9 and then it sets the
ERRORLEVEL correctly.
So I guess it's best to use the latest version.
Michael MacGregor
Database Architect
|||Michael MacGregor (macnoknifespam@.noemailspam.com) writes:
> Aha! When I check the ERRORLEVEL value, whether the BCP IN succeeds or
> fails (failure caused by PK constraint), the value is the same, 0.
> However I realised that I might not be using the latest version of BCP
> and checked, it was BCP v8, so I updated my PATH to use BCP v9 and then
> it sets the ERRORLEVEL correctly.
Interesting. That's seems to be an improvement.
However, I would test a lot more error situations to see if all set
ERRORLEVEL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||I've got round the immediate problem by using a SQL script incorporating
BULK INSERT. It does the job with the error handling I need.
Although I will take your advice under due consideration as I have another
process that needs to be "error aware" that also uses BCP right now, and
also is using v8.0. So I will have to update that to v9.0 and test it to
make sure it catches the types of errors that tend to occur.
Thanks.
Michael MacGregor
Database Architect

How to deploy msde database in web matrix

Dear Sir,
How do I deploy database to remote server in web matrix. It does not have enterprise manager.
I have to use osql.exe command line that runs msde statements that are exuceted against database. Can you
please let me know how to use osql.exe.
Regards,
Farhan

Web Matrix is free so is MSDE so you can buy the Developer edition for $37 to manage MSDE or test drive SQL Server 2005 from the first link below. I would do the former. Hope this helps.
http://www.microsoft.com/sql/downloads/trial-software.mspx

http://www.provantage.com/buy-22053391-microsoft-backoffice-sql-server-2000-developer-edition-shopping.htm

How to delete (drop) a database with OSQL

Hi,
I imported DBF files into a new SQL server database and I've been
developing a VB.Net application.
Now I would like to delete the current database and re-import a more
current set of DBF files into SQL.
Only problem, I can't seem to find a way to delete the database from
MSDE. I tried the OSQL 'drop database <name>' but it complains the
'database is in currently in use'.
I must be missing something simple.
Thanks
Richard
Hi ,
You might be in the same database while you are trying to execute the
command.
connect to the server and change the context to the master database and
also make sure that you dint have any sessions currently accessing the
database either remotely or from the same machine. This time the command
should go ahead fine
girish sundaram
This posting is provided "AS IS" with no warranties, and confers no rights.
|||It is possible that there are still connections to the database. If you are
on MSDE 2000 the following is a sure way to drop a database:
-- Kick everyone out of the database
ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- Go somewhere else yourself as well
USE master
-- Now drop it
DROP DATABASE <database name>
Jacco Schalkwijk
SQL Server MVP
"Richard Fagen" <no_spam@.my_isp.com> wrote in message
news:eBGaP5K3EHA.2676@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I imported DBF files into a new SQL server database and I've been
> developing a VB.Net application.
> Now I would like to delete the current database and re-import a more
> current set of DBF files into SQL.
> Only problem, I can't seem to find a way to delete the database from MSDE.
> I tried the OSQL 'drop database <name>' but it complains the 'database is
> in currently in use'.
> I must be missing something simple.
> Thanks
> Richard
|||Thanks guys, you were right, there was a connection to the database.
At first I tried a 'use master' in qsql but got the same results. Then
it dawned on me that Visual Studio itself had a connection to the
database. Once I closed it, I could delete the database.
To be sure I understood it, I restored the database, checked in VS.Net
to see it, then I deleted it in osql while VS.Net was open (but no
connection), this also worked.
Thanks for you help!
Richard
Jacco Schalkwijk wrote:
> It is possible that there are still connections to the database. If you are
> on MSDE 2000 the following is a sure way to drop a database:
> -- Kick everyone out of the database
> ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
> -- Go somewhere else yourself as well
> USE master
> -- Now drop it
> DROP DATABASE <database name>
>