BCP error Trapping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

BCP error Trapping

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-06-11 : 09:54:55

hari bhaskar writes "Iam using BCP to extract a data from a formated Text FIle and put into the Table. If the text file is not in the specified format actually it is not updating table, Now I would to like trap the error, due to which reason the file is not able to update the table. Kindly suggest me how to Trap the error while using BCP using xp_cmdshel. This is a urgent requirement."

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-08 : 04:37:26

You can write bcp utility output (-o option) to output.txt file and then read this text file:

exec master..xp_cmdshell
'bcp myDB.dbo.t in c:\my.txt -c -t\, -r\n -o c:\output.txt -Sz -Usa -P'

declare @fso int, @f int, @s varchar(255)
exec sp_oacreate 'scripting.filesystemobject', @fso out
exec sp_oamethod @fso, 'opentextfile', @f out, 'c:\output.txt', 1
exec sp_oamethod @f, 'skipline'
exec sp_oamethod @f, 'skipline'
exec sp_oamethod @f, 'skipline'
exec sp_oamethod @f, 'readline', @s out
exec sp_oadestroy @f
exec sp_oadestroy @fso

---- print @s

Now, @s variable is something like this:
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file

- Vit

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-08 : 09:21:14

... or (instead of the OA stuff):

declare @s varchar(255)
create table #t (n varchar(255))
insert into #t exec master..xp_cmdshell 'type c:\output.txt'
select @s=n from #t --- where left(n, 5)='error'
drop table #t

print @s

- Vit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources