Transaction question - 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

SQL Server Development (2000)

Transaction question

Author

Topic

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-02-15 : 04:02:04

Hi there,

a question with regards to a transaction. If a syntax error is returned is there any way to trap this and display the error? I have a table (tblCategories) which has an int field and a varchar field. I am forcing a syntax error by inserting the incorrect datatype into the tblCategories. I want to log the error in the stored proc "p_INSERT_ERRORLOG".

Take the following sql statement:

BEGIN TRAN INSERT_DATA

SET IDENTITY_INSERT tblCategories ON
go

DECLARE @MSG varchar(500)

Insert into tblCategories (CATID, TITLE)
VALUES ('asdf','2')
return

IF @@ERROR <> 0

BEGIN
ROLLBACK TRANSACTION INSERT_DATA
exec p_INSERT_ERRORLOG 'FAILED'
END

ELSE
BEGIN
COMMIT TRAN INSERT_DATA
exec p_INSERT_ERRORLOG 'SUCCESS'
END

But all I get is the syntax error returned:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'asdf' to a column of data type int.

Any ideas on this?

thanks - matt

matt_calhoon
Posting Yak Master

235 Posts

Posted - 2002-02-15 : 04:11:41

- i better add that this script is called from within a DTS 'execute SQL task' and not from a stored proc...just in case it mattered.