May 27, 2008 at 12:26 pm
I am building dynamic SQL that will query a remote database. The dynamic SQL gets the linked server and the source table from a table on the local side. I would like to test that the linked server and the source table in the openquery will work. Is there a simple dynamic SQL I can make that will test the connection in e TRY..CATCH block?
Perhaps it is unnecessary. I am trying to refresh a local table from a remote table, based on data in a table. I am truncating the local table and then doing an insert. However, I do not want to truncate the table if the insert is going to fail.
Any suggestions would be welcomed.
May 27, 2008 at 8:50 pm
To test the connection to a linked server, you may try
sp_testlinkedserver
May 27, 2008 at 10:47 pm
How about:
Select count(*)
From (Select TOP 1 *
From ServerName.DBName.Schema.TableName)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2008 at 2:02 am
Hmmm... there are certain limitations to using TRY..Catch, and - as I understand it - nonexistent objects (tables) are one of the things that doesn't work because of deferred name resolution. The problem is further complicated by the fact, that you are using dynamic SQL. I'm not sure how to do the tests, but you could try something along these lines (I'm not sure what all you need to test, so I included all levels):
SELECT * FROM sys.servers WHERE is_linked = 1 AND name = 'linked_server_name'
SELECT * FROM linked_server_name.master.dbo.sysdatabases WHERE name='database_name'
SELECT * FROM linked_server_name.database_name.INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'your_table_name' AND TABLE_SCHEMA = 'your_schema'
Dynamic SQL is the main problem... if it weren't for that, you could write a few nested IF (BEGIN..END) which will test for existence, and do the update only if everything is OK. I tried to reproduce that in the dynamic SQL, but so far I failed.
May 28, 2008 at 6:03 am
Vladan (5/28/2008)
Hmmm... there are certain limitations to using TRY..Catch, and - as I understand it - nonexistent objects (tables) are one of the things that doesn't work because of deferred name resolution. The problem is further complicated by the fact, that you are using dynamic SQL.
That's a good point, Vladan, however, believe it or not, these two problems will cancel each other out!
Try this:
BEGIN TRY
EXEC('
Select count(*)
From (Select TOP 1 *
From ServerName.DBName.Schema.TableName)
')
END TRY
BEGIN CATCH
Print 'caught!'
END CATCH
Even leaving the 4-part tablename as I have it above (which is a syntax error because the ServerName does not exist) it will still catch it. (Thank-You Adam Machanic!)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2008 at 6:43 am
Nice! I didn't know that, Barry - thanks. I'm still fairly new to 2005 and have only basic knowledge of how TRY..CATCH works.
Well, this seems to be the solution to posted problem.
May 28, 2008 at 7:35 am
Thanks for the replies. I think I have solved the problem with solutions similar to those suggested here. I used the sys.servers table, without the Is_Linked logic, to verify the linkedserver exists. I am not sure if the linked server name works until the dynamic SQL queries the remote system. And that's fine.
I am curious though how I can use the sp_testlinkedserver. This seems to work for me, but there could be a simpler method.
SET @Link1 = 'linkedservername'
begin try
exec ('sp_testlinkedserver ' + @Link1)
-- gets here it must work
end try
begin catch
raiserror ('linked server [%s] does NOT work',16,1,@Link1)
end catch
The other "gotcha" related to the suggestions here are something I forgot to mention. The remote database is Oracle. So I have adapted the test query a bit, but the dynamic SQL I created was a little different.
BEGIN TRY
SET @test_sql = 'if not exists (select 1 from openquery(' + @linked_server +
',''select 1 from ' + @source_schema + '.' + @source_tablename + ' where rownum < 2'')) ' +
'RAISERROR(''Remote source table [ %s ] is EMPTY.'',16,1, ''' + @source_tablename + ''')'
EXECUTE (@test_sql)
END TRY
BEGIN CATCH
RAISERROR('Remote source table [ %s ] does not exist',16,1, @source_tablename )
END CATCH
-- The dynamic SQL ends up looking like ...
if not exists (select 1 from openquery(Link1, 'select 1 from Schema.TableA where rownum < 2') )
RAISERROR('Remote source table [ %s ] is EMPTY.',16,1, 'TableA')
I needed to write a query that would test the existence of the table being read (also tests the linked server) in the quickest amount of time. The ROWNUM<2 is similar to the TOP 1 suggested. While I did that I found that an empty table would "not exist". If the table does "not exist", then I raise an error that the table is empty. However, if the table really does not exist, then the EXECUTE will fail and be picked up by the TRY..CATCH block.
The other part of the solution that occurred to me after posting the question... transactions. I am now putting the TRUNCATE and the INSERT into a transaction. If there is any problem with the INSERT after the TRUNCATE, then I just rollback. This works fine and probably could just be used instead of the pre-checking, but I spent so much time figuring out the checking, I will just be satisfied that I have better error messages and processing stops earlier if I know it won't work. 🙂
May 28, 2008 at 7:56 am
You mentioned that dynamic SQL against remote database is a little quirky. Does that explain why I get one message in the output log and a separate error message returned in the CATCH? I would prefer to know the output error message.
In the output log ...
7412: OLE DB provider "OraOLEDB.Oracle" for linked server "CSPRD" returned message "ORA-00942: table or view does not exist".
In the ERROR_MESSAGE() message captured in the CATCH block....
An error occurred while preparing the query " " for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "CSPRD"
May 28, 2008 at 6:05 pm
Yeah, compound error messages can be a problem, though I am not sure if it is TRY..CATCH or EXEC(string) that is eating the inner error message.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply