bulk insert and error handling

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

SQL Server Administration (2000)

bulk insert and error handling

Author

Topic

rubs_65
Posting Yak Master

144 Posts

Posted - 2004-01-20 : 13:19:50

Hi,

We are copy data from flat files to tables using stored procedure for particular month. It is working fine except that when we specify the wrong database name : parameter dbname it does not go to the @@error block and return before that with the following error:
Server: Msg 208, Level 16, State 82, Line 1
Invalid object name 'dbdummy..tab1.

Following is the code from stored procedure:
select @sql1 = 'bulk insert ' + @dbname + '..tab1 from ''' + @path + '\tab1' + cast (@month as char (5)) + '.txt'' WITH
(
FIELDTERMINATOR = ''\t'',
ROWTERMINATOR = ''\n'',
)'
exec (@sql1)
if (@@error <> 0)
begin
set @result = 1000-operation failed-->error in usage bulk insert operation'
rollback tran
return
end

We want the procedure to go to @@error block if it don’t find the table in db as dbname is wrong and rollback the tran.
How to catch this kind of errors?

Thanks
--rubs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-20 : 13:27:00

How would it know if the wrong database name has been specified? If it is just because of a typo, then just add an IF statement:


IF (SELECT COUNT(*) FROM master.dbo.sysdatabases WHERE name = @dbname) = 1
BEGIN
  BULK INSERT command
END

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources