Is there an "IF EXISTS" test for a linked server?
45,094
Solution 1
Check in sys.servers
:
if not exists(select * from sys.servers where name = N'myserver\devdb_1')
Solution 2
IF NOT EXISTS ( SELECT TOP (1) * FROM sysservers WHERE srvname = 'myserver\devdb_1' )
Author by
Ogre Psalm33
The way of a foolish programmer seems right to him, but a wise man listens to advice of his fellow coders! -Proverbs 12:15 (Paraphrased :-) #SOreadytohelp
Updated on May 10, 2020Comments
-
Ogre Psalm33 about 4 years
I want to be able to programmatically (in T-SQL) check if a specific linked server already exists for my current server and database (so that if the link doesn't exist yet, I can create it). I tried stuff like this:
IF OBJECT_ID('myserver\devdb_1') IS NULL BEGIN PRINT 'Does not exist, need to create link' EXEC master.dbo.sp_addlinkedserver @server = N'myserver\devdb_1', @srvproduct=N'SQL Server' END ELSE PRINT 'Link already exists'
But the OBJECT_ID test always returns null, even if the link already exists. Any way to do this check in T-SQL, so that the rest of my code can assume the link always exists?
-
Ogre Psalm33 about 14 yearsEssentially, nearly the same answer as Remus Rusanu, I up-voted you both, but gave him the check for including the link. ;-)
-
seebiscuit over 6 yearsInteresting. What's the diff between
sysservers
andsys.servers
. I noticed that the column names are different! -
Joe C over 6 years@seebiscuit The first is a table and considered 'the old way', the second is a system view which is the recommended approach. docs.microsoft.com/en-us/sql/relational-databases/system-tables/…
-
Dejan Dozet almost 5 years+ and is_linked = 1