Pulling errors from a linked server

Question

Pulling errors from a linked server

tobygawne

SSC Enthusiast

Points: 134
More actions
January 28, 2013 at 9:06 am

#267720

Hi all,
I've got a linked server to an attomix database. When i send an erroneous statements to the server an error message is returned to the Message pane is SSMS as below:-
OLE DB provider "MSDASQL" for linked server "noble" returned message "[Noble Systems Corp.][ATOMIX ODBC Driver]Atomix error - (-217)Column (crap) not found in any table in the query.
".
Msg 7215, Level 17, State 1, Line 5
Could not execute statement on remote server 'noble'.
I'm trying to store this error using the ERROR_MESSAGE() function but i only get the second statement ('Could not execute statement on remote server 'noble'.') rather than the real error in the first line. The first line statement is displayed in black as a message rather than red for an error.
Anyone know how i can access the first error?
T

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

Toby I'm not sure if this is what you are looking for;

this is just a try-catch structure that is parsing out the elements of the error; i'm not sure what happens when you get multiple errors in this case:

Msg 50000, Level 16, State 1, Line 20
Invalid column name 'CRAP'.
@ErrorSeverity =16|@ErrorNumber = 207|@ErrorState = 1|@ErrorMessage = Invalid column name 'CRAP'.

BEGIN TRY

Begin Transaction

EXEC('SELECT EventDate,CRAP FROM DBSQL2K5.master.dbo.TRACETABLE')

Commit transaction

END TRY

BEGIN CATCH

DECLARE

@ErrorSeverity INT,

@ErrorNumber INT,

@ErrorMessage NVARCHAR(4000),

@ErrorState INT

SET @ErrorSeverity = ERROR_SEVERITY()

SET @ErrorNumber = ERROR_NUMBER()

SET @ErrorMessage = ERROR_MESSAGE()

SET @ErrorState = ERROR_STATE()

IF @ErrorState = 0

SET @ErrorState = 1

RAISERROR( @ErrorMessage, @ErrorSeverity, @ErrorState, @ErrorNumber)

PRINT '@ErrorSeverity =' + CONVERT(VARCHAR,@ErrorSeverity)

+ '|@ErrorNumber = ' + CONVERT(VARCHAR,@ErrorNumber)

+ '|@ErrorState = ' + CONVERT(VARCHAR,@ErrorState)

+ '|@ErrorMessage = ' + @ErrorMessage

IF XACT_STATE() < 0

ROLLBACK TRANSACTION

END CATCH

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tobygawne SSC Enthusiast Points: 134 More actions · Answer 2

Thanks for the reply, i'll give it a try although i think the problem is the ERROR_MESSAGE and ERROR_NUMBER functions do not hold the actual error from the linked server, only the generic "your query didn't work" one...This is probably something to do with the age of the attomix database i'm forced to work with.

rpalser Grasshopper Points: 12 More actions · Answer 3

We need to set up a Linked Server in SQL Server 2008 R2 using the Atomix Driver. It is not showing up as a Provider. The driver shows up as a system DSN. Were you able to set this up? If so, how? Thanks.