显示标签为“iwould”的博文。显示所有博文
显示标签为“iwould”的博文。显示所有博文

2012年3月27日星期二

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!

2012年3月8日星期四

BCP fmt and Create Table

Hello,
I am being sent a fmt file and a data file without headers which I
would like to import into my sql 2000 server. However the data files
have couple of 100's of columns, how could I avoid manually creating a
table. Can I create a table using a fmt file thru bcp/bulk insert?

Thanks
sd<danths@.gmail.com> wrote in message
news:1117041460.483784.193120@.g47g2000cwa.googlegr oups.com...
> Hello,
> I am being sent a fmt file and a data file without headers which I
> would like to import into my sql 2000 server. However the data files
> have couple of 100's of columns, how could I avoid manually creating a
> table. Can I create a table using a fmt file thru bcp/bulk insert?
> Thanks
> sd

I don't believe there's an automatic way to create a table using only a
format file, but why not just ask the person sending the data to include a
CREATE TABLE script?

If that's not possible (which would be strange), one solution would be to
write a script to parse format files and produce the corresponding CREATE
TABLE statement - if you'll need to do this often, and if the data will
always be in different formats, it could be worth it.

Alternatively, for a one-off task, it might be easier to use DTS via the
Import and Export Data Wizard. That will give you the option to create the
destination table, although depending on what's in the format file, you
might have to spend a lot of time rearranging column order and so on.

But getting the table script is probably the best option, otherwise there's
no way to know what the exact data types are, what the key columns are,
which columns are NULL/NOT NULL, if there are any CHECK constraints etc.

Simon