June 29, 2004 at 6:53 pm
I have a linked server set up - I've set it up deliberately with incorrect security settings because I want to be able to test that it is Ok or not before actually using it for a real task.
I am executing this simple statement which will work if the linked server (labelled 'TEST') is set up correctly:
EXECUTE ('SELECT DISTINCT TABLE_CATALOG FROM OPENQUERY([TEST], ''select * from information_schema.TABLES'')')
As I'm expecting an error, I've tried to trap it in the next line as follows:
IF @@ERROR <> 0
PRINT 'Error occurred'
However, all I get is the following (my PRINT statement never runs):
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005: ].
At this point I don't care what the error actually IS - I just want to be able to trap the fact that an error has occurred and that the linked server is not set up correctly.
Is there some other method of error trapping that I should be using when working with Linked Servers?
June 29, 2004 at 8:03 pm
I suspect that your problem occurs before the Select statement has a chance to run on the linked server, so your @error checking never executes.
Your method of error trapping will trap Transact SQL execution errors, but not connection/security errors.
As to how you can trap these errors more effectively ... I'll leave that to someone else in this group who knows more than I. I'm assuming that you don't want to use a separate executable, where your error trapping would be relatively straightforward.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 29, 2004 at 8:28 pm
Phil, thanks for your reply. As you mentioned, if I was using something like SQL-DMO from VB or .NET this would probably be picked up. But I'm stuck in T-SQL. However I might try experimenting with writing a little something in DTS with VB Script and see what happens...
July 1, 2004 at 7:53 am
If your query hits a fatal error, it will terminate immediately and control won't continue to the If statement. Fatal errors are typically severity 20 - 25.
Read this:
http://www.sqlteam.com/item.asp?ItemID=2463
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply