Trapping BCP error from SP

[Matt]

HI

I am using the following code in an SP, it seems like an ugly hack I
have done to check if the BCP was working or not, I check the table it
shold have filled instead of checking the error from BCP itself.

Does anyone know how I can check the BCP errors directly?

this is the code I am using.

--------------------------------------
create procedure q_spr_autoinventering

AS

create table ##q_tbl_autoinventering (

ean13 varchar(13),
antal decimal,
signatur varchar(10),
lagstalle int
)

exec master..xp_cmdshell"bcp ##q_tbl_autoinventering in
c:\outpath\hd1\invent.txt -t ; -Usa -P13hla -c -C "

declare @invjournal int,
@lagstalle int,
@invdatum datetime,
@ean13 varchar(15),
@antal decimal,
@artnr varchar(50),
@lagplats varchar(20),
@lagsaldo decimal,
@mysubject nvarchar(4000)

IF EXISTS (select * from ##q_tbl_autoinventering)
begin
-----------------------------------------------

it seems so ugly to check the table instead of the BCP error itself so
any pointers would be gladly appreciated, I tried to check @@error but
that did not seem to worked how I needed it.

in short what I want is this

if bcp did not work break out of the SP and wait until called next
time by sql server agent. and by not working I only mean that there
was no file to fetch, if there is a file to fetch and the table is
created I have lots of checks in the SP to make sure the values are
correct

rgds

Matt

[Simon Hayes]

"Matt" <ma**@fruitsalad.org> wrote in message
news:b6*************************@posting.google.co m...

HI

I am using the following code in an SP, it seems like an ugly hack I
have done to check if the BCP was working or not, I check the table it
shold have filled instead of checking the error from BCP itself.

Does anyone know how I can check the BCP errors directly?

this is the code I am using.

--------------------------------------
create procedure q_spr_autoinventering

AS

create table ##q_tbl_autoinventering (

ean13 varchar(13),
antal decimal,
signatur varchar(10),
lagstalle int
)

exec master..xp_cmdshell"bcp ##q_tbl_autoinventering in
c:\outpath\hd1\invent.txt -t ; -Usa -P13hla -c -C "

declare @invjournal int,
@lagstalle int,
@invdatum datetime,
@ean13 varchar(15),
@antal decimal,
@artnr varchar(50),
@lagplats varchar(20),
@lagsaldo decimal,
@mysubject nvarchar(4000)

IF EXISTS (select * from ##q_tbl_autoinventering)
begin
-----------------------------------------------

it seems so ugly to check the table instead of the BCP error itself so
any pointers would be gladly appreciated, I tried to check @@error but
that did not seem to worked how I needed it.

in short what I want is this

if bcp did not work break out of the SP and wait until called next
time by sql server agent. and by not working I only mean that there
was no file to fetch, if there is a file to fetch and the table is
created I have lots of checks in the SP to make sure the values are
correct

rgds

Matt

Check the return code from xp_cmdshell:

declare @ret int
exec @ret = master..xp_cmdshell ...

The return code will be 0 if BCP ran without error, or 1 if there was an
error. If invent.txt exists but has no data in it, you'll get a return code
of 0, but it sounds like your checks will catch that anyway.

Simon