Changing Server names Dynamically in Stored Procedure

[indian143]

Hi, I have a stored procedure, in I want to get data from two different Servers. For example Server1 is the one in which Stored procedure is going to be executed. But the Server2 is different. And for the convenience of moving the Stored proc in different environments easily I want it to be changed dynamically. Like Server2 name can be different for Dev env, Test env and production env. Without much changes I want it to be changed easily either by using a variable, parameter or alias name. I know these are the way I can implement that. If somebody can give me a link or a code snippet that would be really helpful. Please help me, I need it urgent, I am also googling for it. But your support helps me a lot. Thanks in advance. Sincerely

Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA

[Richard Deeming]

1. Using the SQL Server Configuration Manager, create an alias[^] pointing to the correct Server2 for the current environment. The alias name should be the same in all environments.
2. In SQL Server Management Studio, create a linked server[^] pointing to the alias you just set up.
3. In your stored procedure, load the data from the linked server.

With this approach, your code doesn't need to change. It will always be querying a linked server with a fixed name, which is pointing to an alias with a fixed name. The only thing that changes between environments is where the alias is pointing.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[indian143]

Hi, I have created the Alias for the server, then I looked for the linked server, it was already created in my Management Studio. But when I am trying to query using the Alias name, I am getting error message saying that the Server doesnt exist. Can you please help me how to query using Alias name in SQL Server 2008 R2. I am doing it in the following way.

select distinct xxid FROM CedarsLinkServer.CEDARS.dbo.SomeTestDemographic where recEndDate is null

I am getting the following error.

Msg 7202, Level 11, State 2, Line 1
Could not find server 'CedarsLinkServer' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

Please help me in resolving it.

Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA

[Richard Deeming]

The linked server needs to point to the alias, and your query needs to reference the linked server by name. Judging by the error message, you don't have a linked server called CedarsLinkServer set up.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[indian143]

Ok I got you where I am missing. I will try with that, if I get any problems I will send you a message. Thanks for the help Richard.

Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA

[GuyThiebaut]

As a prezzie here's a stored procedure to connect to and disconnect from a linked server:

--connects to and disconnects from the mysql online database
--example call to connect:
--dbo.LinkedServerConnect_SP @connect = 1
--example call to disconnect:
--dbo.LinkedServerConnect_SP @connect = 0

CREATE PROC dbo.LinkedServerConnect_SP
(@connect as int)
AS
BEGIN

if @connect = 1
begin

if not exists(select name from sys.servers where name = 'yadayada') 
begin	
	
	EXEC sp\_addlinkedserver 'yadayada', 'MySQL', 'MSDASQL', Null, Null, 'Driver={MySQL ODBC 3.51 Driver};DB=yadayada;SERVER=mysql.yadayada.com;option=512;uid=yadayada;pwd=yadayada'

end

end

if @connect = 0
begin

if exists(select name from sys.servers where name = 'yadayada') 
begin	
	
	EXEC sys.sp\_dropserver @server = 'yadayada', @droplogins = 'droplogins'; 

end

end

end

GO

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens