Menu
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?
893
Run SQL server Profiler for a month against the target server (on a spare machine) to see if the linked server login name appears, this is the login name of the connection set up in the linked server configuration.
Right-click on the Linked Server node and choose New Linked Server. In the General tab of the New Linked Server window, choose a name for your linked server, then choose the type of the server you need to connect to using that linked server.
If you find yourself needing to use Transact-SQL to get a list of all linked servers in SQL Server, below are two ways you can go about doing this. In the first example I use the sp_linkedservers system stored procedure to return the linked servers. In the second example I use the sys. servers system catalog view.
Check in sys.servers:
if not exists(select * from sys.servers where name = N'myserver\devdb_1') 
IF NOT EXISTS ( SELECT TOP (1) * FROM sysservers WHERE srvname = 'myserver\devdb_1' ) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
