Check Connection before delete table 1

[dk2006]

Hi all,
I have this complex Stored Proc
1. Drip tables if exist
2. build table using SELECT INTO from Linked Servers

The problem is it will drop table before it makes connection to Linked Servers. It connection failed, I don't have tables and many application failed. I want to somehow check connectin to Linked Servers before attemp to drop and re-build table. Yes, I prefer to use DROP table than DELETE FROM for many reasons.

Can anyone point me to the right direction?

Thanks
dk

 

[gmmastros]

You could check your connection to the linked server by doing...

[tt]Select *
From OtherSever.OtherDatabase.Owner.Table
Where 1=2[/tt]

If the connection exists, you won't get an error or any data. If the connection does not exist, you will probably get an error.

Since you are using Where 1=2, this test should be really quick. Hope this helps.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dk2006]

That's exactly what I'm looking for.
Very good thinking. You defenitely get a star.

Thank you.

dk

 

[ESquared]

How about, with error handling,

build tabletemp using SELECT INTO from Linked Servers
drop table
sp_rename tabletemp table

aside from the space considerations of having the data stored twice, it seems to me that renaming a table is a smaller overhead than hitting the linked server twice.