Capturing SQL error message in addition to @@Error

Question

Post reply

Capturing SQL error message in addition to @@Error

mdenner

SSC Eights!

Points: 833
More actions
November 5, 2003 at 3:35 pm

#160057

We have a system written in T-SQL that essentially migrates data from one application to another. Because there are lots of possible errors, we run this as a cursor that migrates the records one by one. If the migration fails (typically data integrity issues where a value is in one database and not in the other), we capture the basic information about the error in a table. We currently capture the value of @@ERROR, which is useful, but what we really want to capture is the error message generated by SQL itself -- i.e.,
**
INSERT statement conflicted with COLUMN
FOREIGN KEY constraint 'FK_Table1_Field1'. The conflict occurred in database 'CFD2', table Table1, column 'Field1'.
The statement has been terminated.
**
I know there's a whole variable replacement process that goes on against data stored in sysmessages, but obviously I can't really replicate this process to capture the error (since the error's already happened!). Basically I need the equivalent of both Err.Number and Err.Description from Visual Basic. Does anyone have any insights?
Thanks
Mark Denner

Viewing 7 posts - 1 through 6 (of 6 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Not much help, I guess.

http://www.sqlservercentral.com/forum/link.asp?TOPIC_ID=17451

In addition, I know there is a T-SQL command FORMATMESSAGE, but after reading this https://www.appsecinc.com/Policy/PolicyCheck2021.html I decided not to use

Frank

http://www.insidesql.de

http://www.familienzirkus.de

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

GRN Hall of Fame Points: 3260 More actions · Answer 2

I do not know any native T-SQL method to do this .. this information would be available from the client though..

if you are restricted to a SQL Environment - a possible workaround would be to execute your statements using OLE within the SQL batch..you might want to check the performance implications though

giving below is a DMO script which executes SQL statements and can capture these errors

DECLARE @oServer int

DECLARE @oDatabase int

DECLARE @object int

DECLARE @hr int

DECLARE @src varchar(255), @desc varchar(255)

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @oServer OUT

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @oServer

RETURN

END

EXEC @hr = sp_OACreate 'SQLDMO.Database', @oDatabase OUT

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @oDatabase

RETURN

END

EXEC @hr = sp_OAMethod @oServer, 'Connect', NULL, 'server','user','pass'

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @oServer

RETURN

END

EXEC @hr = sp_OAMethod @oServer, 'Databases("Pubs")',@oDatabase OUT

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @oServer

RETURN

END

EXEC @hr = sp_OAMethod @ODatabase, 'ExecuteImmediate ( "<Statement>",2)'

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @ODatabase , @src OUT, @desc OUT

PRINT 'Source :' + @Src

PRINT 'Desc :' + @desc

RETURN

END

EXEC @hr = sp_OADestroy @ODatabase

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @ODatabase

RETURN

END

EXEC @hr = sp_OADestroy @oServer

IF @hr <> 0

BEGIN

EXEC sp_OAGetErrorInfo @oServer

RETURN

END

nigelrivett SSCertifiable Points: 5472 More actions · Answer 3

see

http://www.nigelrivett.net/spFormatOutputBuffer.html

Cursors never.

DTS - only when needed and never to control.

Cursors never.
DTS - only when needed and never to control.

GRN Hall of Fame Points: 3260 More actions · Answer 4

GRN

Hall of Fame

Points: 3260

November 6, 2003 at 4:17 am

#481032

excellent solution , never thought of that..

ThomasH SSCertifiable Points: 7437 More actions · Answer 5

I have some utilitarian procedures that

create and execute SQL Server Agent Jobs.

I noticed the output of the T-SQL PRINT

statements along with any other Query Anal.

type output is in the msdb..SysJobHistory

table.

I would never do the following in a loop

But for one-at-a-time, or long running

SQL code...

Write a procedure that:

Accepts a T-SQL script as a parameter

Create a JOB to execute the script

Start the JOB

Wait until JOB is completed

Query the JOB's history for errors

Delete the JOB

Notes:

SysJobHistory.Message is UNICODE

SQL Jobs can be configured to output the "message" to a DOS file.

Hope this helps

Once you understand the BITs, all the pieces come together

mdenner SSC Eights! Points: 833 More actions · Answer 6

Nigel -

You *are* my hero -- that was perfect.

I touched up your procedure a little -- my processing isn't as sophisticated as yours, but it's a lot simpler (it does, however, replace all '.' characters whether they're actual punctuation or unprintable characters).



IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.proc_GetLastErrorMsg') AND OBJECTPROPERTY(id, N'IsPROCEDURE') = 1)

DROP PROCEDURE proc_GetLastErrorMsg

GO



CREATE PROCEDURE proc_GetLastErrorMsg

@SPID Int,

@ErrorMsg VarChar(8000) OUTPUT

AS

/*

SET nocount on

DECLARE @s VarChar(8000)

INSERT INTO MHGroup.Custom1 VALUES('000004', 'UCB Actias', 'Y', NULL)

EXEC proc_GetLastErrorMsg @@SPID, @s OUTPUT

SELECT @s

SELECT @@SPID

*/

DECLARE @Cmd VarChar(100)



CREATE TABLE #OutputBuffer (HexString VarChar(100))

SELECT @Cmd = 'DBCC OUTPUTBUFFER(' + Cast(@SPID as VarChar(10)) + ')'

INSERT #OutputBuffer(HexString) EXEC(@Cmd)



SELECT @ErrorMsg = ''

SELECT @ErrorMsg = @ErrorMsg + Replace(SubString(HexString, 62, 17), '.', '') from #OutputBuffer

DROP TABLE #OutputBuffer

GO

quote:
see
http://www.nigelrivett.net/spFormatOutputBuffer.html
Cursors never.
DTS - only when needed and never to control.

Edited by - mdenner on 11/06/2003 1:47:27 PM

Edited by - mdenner on 11/06/2003 1:49:12 PM

Edited by - mdenner on 11/06/2003 1:51:00 PM