asked on

How to check if SQL Server exists before executing the code

I am using the following code to dynamically create a linked server
:

 select @tsql =	'use [master];
					EXEC master.dbo.sp_addlinkedserver @server = N'''+@pservername+''', @srvproduct=N''sql server'';
					EXEC master.dbo.sp_serveroption @server=N'''+ @pservername+''', @optname=N''data access'', @optvalue=N''true'';
					EXEC master.dbo.sp_serveroption @server=N'''+ @pservername+''', @optname=N''rpc'', @optvalue=N''true'';
					EXEC master.dbo.sp_serveroption @server=N'''+ @pservername+''', @optname=N''rpc out'', @optvalue=N''true'';
					EXEC master.dbo.sp_serveroption @server=N'''+ @pservername+''', @optname=N''use remote collation'', @optvalue=N''true'';
					EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N''' + @pservername+ ''', @locallogin = null , @useself = N''true'';'    

--print @tsql
IF @PrintFlag = 0 PRINT @tsql
ELSE   exec (@tsql)

Open in new window

When a server does not exists I get the following error:

"OLE DB provider "SQLNCLI" for linked server "SERVER03" returned message "Login timeout expired". "

Aneesh

you can use this

select * from master..sysservers where srvname = 'YourLinkedServerName'

YZlat

ASKER

That's not what I meant. let's say I need to create a linked server from SERVER01 to SERVER03. SERVER03 was deleted. So executing sp_addlinkedserver would give an error.

How do I avoid that?

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial