0

In SQL Server (i'm using 2008) is it possible to dynamically access server by server name?

My scenario: I have a production server, a development server, and a test server. Their structure is the same. There is a fourth server with some additional data - let's call it a data server.

On the data server there is a procedure. One of it's parameters is a name of the requesting server:

proc sp_myProcedure(@myId int, @serverName nvarchar(100))

The procedure accesses tables from the data server and from the requesting server. At the moment, to query the requesting server I'm using a case expression:

-- code on the data server
select additionalData = case @serverName
    -- if the requesting server is production - query production
    when 'ProdServer' then (select field1 from [ProdServer].[MyDataBase].[dbo].[MyTable] ...
    -- if the requesting server is test - query test
    when 'TestServer' then (select field1 from [TestServer].[MyDataBase].[dbo].[MyTable] ...
    -- if the requesting server is development - query development
    when 'DevServer' then (select field1 from [DevServer].[MyDataBase].[dbo].[MyTable] ...
end

My question is if there is any other way to access the requesting server. I'd like to replace ifs and cases with something more dynamic. Is it, for instance, possible to use the server name variable to dynamically access specific server. Something similar to the following (mocked) query:

declare myServer <server type> = Get_Server(@serverName)
-- the query
additionalData = select field1 from [myServer].[MyDataBase].[dbo].[MyTable]
PiotrWolkowski
  • 8,408
  • 6
  • 48
  • 68

1 Answers1

0

I liked this approach

SELECT
    SERVERPROPERTY('MachineName') AS [ServerName], 
    SERVERPROPERTY('ServerName') AS [ServerInstanceName], 
    SERVERPROPERTY('InstanceName') AS [Instance], 
    SERVERPROPERTY('Edition') AS [Edition],
    SERVERPROPERTY('ProductVersion') AS [ProductVersion], 
    Left(@@Version, Charindex('-', @@version) - 2) As VersionName

Link

Another approach which we were using was

Creating one database called database_yourprojectname

So, for the explanation I'm using database name as northwind after that you can create one new database called northwind_db

Which has a following fields:

Servername,username(encrypted),password(encrypted),active

And then you can either make one page to insert/update/delete current database used there or you can add statically data to it..so, you can use the database which is active currently.

Or use simple one:

SELECT @@SERVERNAME

Which is already stated here

Community
  • 1
  • 1
Just code
  • 13,553
  • 10
  • 51
  • 93
  • I already have a server name. What I'd like to achieve is to somehow access the server instance by the server name. So I have servers A, B, C and from server D I want to access server A if the requesting server name is "A", B if the name is "B" and so on. – PiotrWolkowski Mar 18 '15 at 13:03
  • then your approach looks ok to me. can't find any other for now – Just code Mar 18 '15 at 13:08
  • I just hoped there is a way to get rid of all these if/case statements that clatter my code. Other problem with my current approach is that if I'll start querying the procedure from yet another server I'll have to manually update the procedure with additional conditions to include a new server name. – PiotrWolkowski Mar 18 '15 at 13:13
  • one more approach I would like to say but not sure it will work in your case.Just add one more database which will be accessed by all the servers then just redirect as per your need. so, for the instance if you need to change or want to change the server redirection you only have to change one database. – Just code Mar 18 '15 at 13:15
  • But, you must need to specify the direction as you have stated in your query. not sure if its easy or not. – Just code Mar 18 '15 at 13:15