bcp within batch file

I have a windows batch file that executes a SQL Server bcp command. I
would like to obtain a return code if the bcp command fails. However,
I cannot seem to find the return code (if any) for bcp. For example,
if the bcp command is improperly formatted, or has a bad password, I
want the batch file to return an error. Right now, my batch file
simply executes and returns success, even when the bcp command fails.
Has anyone run into this before?

Thanks!DBA (kaylisse@.yahoo.com) writes:
> I have a windows batch file that executes a SQL Server bcp command. I
> would like to obtain a return code if the bcp command fails. However,
> I cannot seem to find the return code (if any) for bcp. For example,
> if the bcp command is improperly formatted, or has a bad password, I
> want the batch file to return an error. Right now, my batch file
> simply executes and returns success, even when the bcp command fails.
> Has anyone run into this before?

The return status for a program called from a batch file is in
%ERRORLEVEL%, so this is the variable you should check.

I seem to recall that BCP does not always set this variable as one
may desire. It does set it, if the password is wrong. But I believe
it does not set %errorlevel% if some rows does not load.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Looks like most interesting bcp errors will set %errorlevel% to 1. An empty
input file, however, doesn't set the errorlevel. You can take action in a
..CMD file like this:

bcp <table> [in|out] <filespec> [switches]
if %errorlevel% 1 goto <label>
<normal processing steps here
:<label> echo something BAD happened to your BCP!
<steps to do something about it here
I didn't test it but I believe if you set the maxerrors switch, you won't
get the non-zero errorlevel unless you actually exceed that threshold. You
might want to test this yourself.

FYI - in our .CMD scripts, if I want to simply fail the job after the error,
I usually do this:

bcp <stuff>
if %errorlevel% 1 goto BCP_FAILED

and I don't bother using a BCP_FAILED label anywhere. Searching for it, the
job runs right past the end and aborts. You'll see a message saying "Can't
find label BCP_FAILED" or something similar as part of the job status report
if you run this through SQL Executive and, by convention here, that's the
diagnostic for the job.

"DBA" <kaylisse@.yahoo.com> wrote in message
news:ffe01bb8.0407151237.39fbef2c@.posting.google.c om...
> I have a windows batch file that executes a SQL Server bcp command. I
> would like to obtain a return code if the bcp command fails. However,
> I cannot seem to find the return code (if any) for bcp. For example,
> if the bcp command is improperly formatted, or has a bad password, I
> want the batch file to return an error. Right now, my batch file
> simply executes and returns success, even when the bcp command fails.
> Has anyone run into this before?
> Thanks!|||Hi

%ERRORLEVEL% will be 0 when a succesful import has been performed. If it
fails then it will return 1 (on my tests!).

John

"DBA" <kaylisse@.yahoo.com> wrote in message
news:ffe01bb8.0407151237.39fbef2c@.posting.google.c om...
> I have a windows batch file that executes a SQL Server bcp command. I
> would like to obtain a return code if the bcp command fails. However,
> I cannot seem to find the return code (if any) for bcp. For example,
> if the bcp command is improperly formatted, or has a bad password, I
> want the batch file to return an error. Right now, my batch file
> simply executes and returns success, even when the bcp command fails.
> Has anyone run into this before?
> Thanks!

Batch Operations of SQL Command.

Hi,

Im trying to write a class that compiles a list of SQLCommands and then executes them all at once. Im trying to reduce the amount of calls to the database.

Im also trying just to update the fields which have changed so I cannot use Stored Procedures as that would mean writing way too many stored procedures for every permutation on every table in my database.

So Ive decided to build sql commands and then execute them all with one call to the database. When I print the commandtext property of the sqlcommand to the page whilst debugging It shows something like insert into XXX (f1,f2) values (@.v1,@.v2). Is there any way to see the final sql string, with the @.v1 variables replaced by the actual values in the parameters I have added to the sql command?

Im building the commands by creating a new sqlCommand. Then I set the commandtext to "insert into XXX (f1,f2) values (@.v1,@.v2)". Then I Add Parameters to the sqlCommand. My Parameters count is showing the correct value.

I want to be sure now that when I go to send these commands to the database as part of one long string that it will work.

Thanks,

C

Hi ,

I think you are going right. For seeing the result with the value don't use the parameters. What you can do is directly substitute the values in the SqlCommand lkike this

CommandText="Insert into XXX(f1,f2) Value('" + Field1.Value +"','" + Field2.Value + "')"

and then you will be able to see the value. I think you will be able to texecute the multiple queries. Special care needs to be taken if you are returning multiple result sets i.e Select statements

|||

Hi Satya,

Thanks for the response. My class is creating commands for itself, and then calling a Save() method in all its member classes each to return a List<sqlCommand> of Sql commands. So at the end Ive a list of sql commands to execute which Ive got in the correct order for execution.

However at this stage I want to be able to see exactly what is being sent to the database to test it before i put it to use. Here is an example from the function that builds the sqlCommand objects.

switch (scb.TransactionType) {/* CREATE SQL INSERT STATEMENT */case"Insert" : first =true; sqlString.CommandText ="INSERT INTO " + scb.DBTableName +" (";foreach (string value in scb.changedFieldsArray) {if (first) first =false;else sqlString.CommandText +=","; sqlString.CommandText +=" " +value; } sqlString.CommandText +=" )"; sqlString.CommandText +=" VALUES ("; first =true;foreach (string value in scb.changedFieldsArray) {if (first) first =false;else sqlString.CommandText +=","; sqlString.CommandText +=" @." +value; } sqlString.CommandText +=" )";foreach (SqlParameter sqlParamin scb.changedParamArray) { sqlString.Parameters.Add(sqlParam); }break;

So as you can see Im first building the commandText using @.field for the fieldnames and then adding the parameters using a loop. I assume this is the correct way to build a command? This function then returns this command. What I want to know is when I finally have all my commands in a list, how can I then check the final SQL strings that will be executed. I dont want to replace the @.field at this stage, because I dont need to, and Im not sure if Im still using the escaping and size properties of the parameters ive created if i do.

So how, when I've the final list of sqlCommand objects can i preview the final sql string that will be executed by the SQL Server?

Thanks Again,

C

|||

Hi,

Actually, if you want to execute the command, then adding the parameters using a loop is ok. But in this way, you can't preview the final sql string. I suggest you to put the following code after yours.

Suppose your insert comand is in this format: Insert into tables ( Field1, Field2 ) values (@.p_a,@.p_b)

int i=1;foreach (SqlParameter sqlParamin scb.changedParamArray){if(i==1){ sqlString=sqlString.Replace("@.p_a",sqlParam) }else if(i==2){ sqlString=sqlString.Replace("@.p_b",sqlParam) } .... i++;}

And then you can preview the final sql command by sqlString variable.

Meantime, there's another method to achieve this.

Suppose your insert comand is in this format: Insert into tables ( Field1, Field2 ) values ( {0},{1})

string[] para_array =new string[para_num]/// in this sample, para_num=2int i=0;foreach (SqlParameter sqlParamin scb.changedParamArray){ para_array[i]=sqlParam; i++;}string sqlString_preview =string.Format(sqlString,para_array[0],para_array[1]);

In this way, sqlString_Preview also stands for the preview of sql command.

Thanks.

basic tlog question

whenever a transaction or a SQL statement executes, do the changes get
written to the log file first ? I know they do, but are they also in
memory.. I understand the part where dirty pages are written to disk.. at
checkpoints or lazy writers or thru worker threads...what im a bit confused
is when it talks about writing to disk, is it referring to the data files on
disk or the log files on disk..
Can someone just give me a 2 to 3 liner on the initial part before the data
actually gets written to the disk i.e. disk that contains the data files ?
Yes, the log records are written to memory too, to an area called 'log
cache', and SQL Server has a logic to make sure these cached log records are
written to log files, before the associated dirty pages are written to disk.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e3WwGOvaEHA.3524@.TK2MSFTNGP12.phx.gbl...
> whenever a transaction or a SQL statement executes, do the changes get
> written to the log file first ? I know they do, but are they also in
> memory.. I understand the part where dirty pages are written to disk.. at
> checkpoints or lazy writers or thru worker threads...what im a bit
confused
> is when it talks about writing to disk, is it referring to the data files
on
> disk or the log files on disk..
> Can someone just give me a 2 to 3 liner on the initial part before the
data
> actually gets written to the disk i.e. disk that contains the data files ?
>

basic tlog question

whenever a transaction or a SQL statement executes, do the changes get
written to the log file first ? I know they do, but are they also in
memory.. I understand the part where dirty pages are written to disk.. at
checkpoints or lazy writers or thru worker threads...what im a bit confused
is when it talks about writing to disk, is it referring to the data files on
disk or the log files on disk..
Can someone just give me a 2 to 3 liner on the initial part before the data
actually gets written to the disk i.e. disk that contains the data files ?Yes, the log records are written to memory too, to an area called 'log
cache', and SQL Server has a logic to make sure these cached log records are
written to log files, before the associated dirty pages are written to disk.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e3WwGOvaEHA.3524@.TK2MSFTNGP12.phx.gbl...
> whenever a transaction or a SQL statement executes, do the changes get
> written to the log file first ? I know they do, but are they also in
> memory.. I understand the part where dirty pages are written to disk.. at
> checkpoints or lazy writers or thru worker threads...what im a bit
confused
> is when it talks about writing to disk, is it referring to the data files
on
> disk or the log files on disk..
> Can someone just give me a 2 to 3 liner on the initial part before the
data
> actually gets written to the disk i.e. disk that contains the data files ?
>

basic tlog question

whenever a transaction or a SQL statement executes, do the changes get
written to the log file first ? I know they do, but are they also in
memory.. I understand the part where dirty pages are written to disk.. at
checkpoints or lazy writers or thru worker threads...what im a bit confused
is when it talks about writing to disk, is it referring to the data files on
disk or the log files on disk..
Can someone just give me a 2 to 3 liner on the initial part before the data
actually gets written to the disk i.e. disk that contains the data files ?Yes, the log records are written to memory too, to an area called 'log
cache', and SQL Server has a logic to make sure these cached log records are
written to log files, before the associated dirty pages are written to disk.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e3WwGOvaEHA.3524@.TK2MSFTNGP12.phx.gbl...
> whenever a transaction or a SQL statement executes, do the changes get
> written to the log file first ? I know they do, but are they also in
> memory.. I understand the part where dirty pages are written to disk.. at
> checkpoints or lazy writers or thru worker threads...what im a bit
confused
> is when it talks about writing to disk, is it referring to the data files
on
> disk or the log files on disk..
> Can someone just give me a 2 to 3 liner on the initial part before the
data
> actually gets written to the disk i.e. disk that contains the data files ?
>