BCP Handling

Hi,

I am executing script like this. How to check for the errors if "master..xp_cmdshell @.bcpCommand" fails. Is there any way to verify that BCP is completed successfully

DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)

SET @.FileName = 'E:\TestBCPOut.txt'
SET @.bcpCommand = 'bcp "SELECT * FROM pubs1..authors ORDER BY au_lname" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -c -U -P'

EXEC master..xp_cmdshell @.bcpCommand

Thanks in Advance,declare @.ret int
EXEC @.ret=master..xp_cmdshell @.bcpCommand

|||Is is possible...........|||

In addition, you can also specify an error file for your bcp command, and then check it afterwards for any content:

DECLARE @.FileName varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName = 'E:\TestBCPOut.txt'
SET @.bcpCommand = 'bcp "SELECT * FROM pubs1..authors ORDER BY au_lname" queryout "'
SET @.bcpCommand = @.bcpCommand + @.FileName + '" -c -U -P -ee:\myBCPerror.txt'

declare @.ret int
EXEC @.ret=master..xp_cmdshell @.bcpCommand

CREATE TABLE #bcperr(input varchar(255) null)
INSERT #bcperr(input) EXEC master..xp_cmdshell 'type e:\myBCPerror.txt'
IF EXISTS(SELECT * FROM #bcperror WHERE input IS NOT NULL)
RAISERROR('There was an error with the BCP command.', 16, 1)
DROP TABLE #bcperr

You can also just do a SELECT * FROM #bcperr to get the actual error rows.

|||

thanks a lot..

any idea how to trap the number of rows that were transffered during the bcp out process...

|||

If you also specify an output file with the -o parameter, you can 'parse' it and grab the line with the rows total in it.

declare @.rc int
EXEC @.rc = master..xp_cmdshell 'find c:\myBCPoutput.txt "rows copied"'

output
NULL
- C:\MYBCPOUTPUT.TXT
23 rows copied.
NULL

(4 row(s) affected)

=;o)

/Kenneth

|||Hey how do I capture this in a table... betn thanks !!!|||

Here's an example.

create table #bcpResult
( result varchar(50) null )
go

declare @.rc int

insert #bcpResult
EXEC @.rc = master..xp_cmdshell 'find c:\myBCPoutput.txt "rows copied"'
go
select * from #bcpResult where result like '%rows copied%'
go
drop table #bcpResult
go

=;o)
/Kenneth