Friday, April 29, 2011

make server name as a variable

I am trying to get data from different sever, and the sever name might change. So I set the server name as a parameter. The idea of my sql store procedure is something like this

CREATE PROCEDURE [dbo].[GetData]
        @ServerName
AS
BEGIN
    SELECT * FROM @ServerName.ClientDataBase.dbo.Client
END

Does anyone know how to achieve this?

The database version is sql server 2005

From stackoverflow
  • Use dynamic SQL, however evil this may be:

    exec('select * from ' + @ServerName + '.ClientDatabase.dbo.Client')
    
  • Look at using Synonym(s)

    Create syntax (from MSDN):

    CREATE SYNONYM [ schema_name_1. ] synonym_name FOR < object >
    
    < object > :: =
    {
        [ server_name.[ database_name ] . [ schema_name_2 ].| 
             database_name . [ schema_name_2 ].| schema_name_2. ] object_name
    }
    
    gbn : Good idea- Works for a limited number of server names
    uosɐſ : It seems to me that both the CREATE SYNONYM and the sp_setnetname suggestions here might have unintended consequences with concurrency since the operations have a global effect. That is, if two such operations overlapped, one or both of the operations might fail and leave the server configuration in an inconsistent state.
  • You could set up a linked server, says "BOB".

    Then you could call sp_setnetname to change the underlying target server but keep the name BOB.

    So your code would be:

    CREATE PROCEDURE [dbo].[GetData]
            @ServerName
    AS
    BEGIN
        EXEC sp_setnetname 'BOB', @ServerName
        SELECT * FROM BOB.ClientDataBase.dbo.Client
    END
    
    John Dibling : You would need to drop the linked server too, unless you run this SP only once.
    gbn : Why? sp_setnetname allows you to redefine the linked server target

0 comments:

Post a Comment