"Dynamic Error Trapping"

Question

"Dynamic Error Trapping"

Craig Mold-491418

SSC Rookie

Points: 37
More actions
September 19, 2007 at 8:04 am

#178477

Hi all,
This is a beauty, and I don't think you can do anything about it, but hopefully someone out there will have a top solution. Ok. I need to dyamically import data using BULK Insert. It needs to be dynamic as the table and filename can be anything the user likes. What I'm having a real issue with is trapping Batch fail errors so that I can report exactly what went wrong, rather than just a generic "something went wrong sorry". What I do at the moment is this...
SET @Command = ' SET XACT_ABORT OFF '+
' SET ANSI_WARNINGS OFF'+
' Begin Transaction '+
' UPDATE Zed_Internet_Data.dbo.TblFtpProcess '+
' SET Processed = 2 '+
' WHERE [FileName] = '+Char(39)+@File+char(39)+
' COMMIT TRANSACTION'+
' BULK INSERT Zed_Internet_Data..'+@TableName+
' FROM '+char(39)+'e:\Imported\'+SUBSTRING(@File,10,50)+char(39)+
' WITH (FIRSTROW=2, KEEPNULLS, FIELDTERMINATOR='+char(39)+'þ'+char(39)+
', ROWTERMINATOR='+char(39)+'\n'+char(39)+')'+
' IF @@ERROR = 0 UPDATE master..testerror SET Error = 100'
As you can see I'm error handling by cheating. I set a flag to 2 to show that it got to this stage, and if nothing bad happend set error to 100. Back in the stored proc if 100 is found then that flag gets set from 2 to 1. So I can see that something happened, but not what. Has anybody any idea how I would trap a batch fail error from Dynamic SQL?
Thanks for your help.
Cheers
C

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004681 More actions · Answer 1

First off and as a bit of a side bar... you don't need the BEGIN/COMMIT pair because the default settings for SQL Server are to treat each UPDATE as an implicit transaction and you aren't doing any error checking on the UPDATE.

Second, take a look at sp_ExecuteSQL in Books Online where you can pass variables in and out of the Dynamic SQL to do what you want.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Craig Mold-491418 SSC Rookie Points: 37 More actions · Answer 2

Hi Jeff,

Because it's a batch failure, nothing gets passed back. It fails and returns 0, I tried getting @@Error returned. This is why I've error trapped the way I have. Thanks for the commit information, I was just making absolutly sure that the update happened if the batch failed. Do you have any other ideas?

Cheers

C

Sergiy SSC Guru Points: 110209 More actions · Answer 3

As Jeff suggested, use sp_ExecuteSQL.

Same way as you've been told in another your thread:

EXEC @Error = sp_ExecuteSQL @sql, N'@param1 datatype, @param2 datatype, ...', {parameters}

See BOL for more details.

_____________
Code for TallyGenerator