Post reply

multiple linked servers and stored procs

  • March 25, 2009 at 8:30 am

    #129992

    Hi,

    Not sure if I've asked this before.. I know I've spent a lot of time looking around on it.

    I have a database which pulls some information from other databases using Linked Servers and uses the values returned to do some inserts/updates or just plain selects.

    The databases it talks to are just about identical except for the data in them and the name of the database. (each database is on its own server)

    So for example, I have

    Db1 - My main database that uses the linked servers

    Db2 - My 1st 3rd party application database

    Db3 - My 2nd 3rd party application database

    Dbn - My nth 3rd party application database

    So I have some stored procedures in Db1 which would work in Db2 and Db3 -- So in my infinite wisdom (ugh) I decided I would pass the linked server name and database as parameters (@svr and @dbname) in my stored procedures that use these linked servers

    Note, I can NOT change my business model here.. I MUST access the data in Db2/Db3/Dbn from Db1.

    So in Db1 i have 3 links

    LNKDb2, LNKDb3, LNKDbn

    So inside my sql I can write

    SELECT * FROM LNKDb2.Db2.dbo.myTable

    And I get results, which is great, but only works for a single database

    OR i can have

    IF @dbname = 'Db2'

    BEGIN

    SELECT * FROM LNKDb2.Db2.dbo.myTable

    END

    IF @dbname = 'Db3'

    BEGIN

    SELECT * FROM LNKDb3.Db3.dbo.myTable

    END

    But I'll need to add the same logic to potentially hundreds of stored procs for each database I add..

    So instead, what I have is..

    DECLARE @strSP NVARCHAR(4000)

    SET @strSP = N'

    SELECT * FROM ' + @svr + '.' + @dbname + '.dbo.myTable'

    EXEC sp_executesql @strSP

    Which is great except it doesn't return results.. I also have a slightly more complex example which does return results.. it works fine.. BUT.. its a huge pain in the backside, makes it horrible to debug and limits me to 4000 characters for my sql.

    So finally -- to my question.

    Is there a way I can push a parameter to my FROM clause

    I know i can't do

    SELECT * FROM @svr.@dbname.dbo.myTable

    but is there a way I can wrap the parameters in something so it see's them correctly..

    Or.. does someone have a better approach to the changing linked servers?

    Thanks

    --------------------------

    I long for a job where my databases dont have any pesky users accessing them 🙂

  • March 25, 2009 at 9:20 am

    #966036

    Hi

    If you are using SQL Server 2005 or later you should have for the CREATE SYNONYM clause. Snippet from BOL:

    EXEC sp_addlinkedserver Server_Remote;

    GO

    USE tempdb;

    GO

    CREATE SYNONYM MyEmployee FOR Server_Remote.AdventureWorks.HumanResources.Employee;

    GO

    Greets

    Flo

  • March 25, 2009 at 10:32 am

    #966104

    To add to what Flo has given you, what you would do is create the synonym dynamically based upon the server passed in and use that synonym in your query.

    The downside to this is that your procedure could only be run for a single server at a time. Trying to run it from multiple connections will cause problems because you have to drop and recreate the synonym for each server.

    For something like this, I would move it outside of SQL Server and use a combination of Powershell and SQLCMD. This way, you could write the queries as straight .sql files without specifying the servername - and execute it against the appropriate server.

    With SQLCMD - if you needed variables for the objects - you can do that very easily. For example, this is legal in SQLCMD:

    SELECT col1 FROM $(servername).$(database).dbo.MyTable;

    Where servername and database are variables defined at run time.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • March 25, 2009 at 1:56 pm

    #966258

    Thanks guys..

    the synonym thing looks like it has legs..

    I'm gonna have a play 🙂

    --------------------------

    I long for a job where my databases dont have any pesky users accessing them 🙂

  • March 25, 2009 at 3:29 pm

    #966332

    Things didn't work out quite well enough for me with the synonym thing

    i created my synonyms, but I could hit the occasion where the same stored proc should hit Db2 and Db3 at the same time, and it'll only go to one or the other depending on what one starts first..

    so my genius idea was to affix the order id to the end of the synonym name.. which worked well.. until 30 minutes later when I realized that to get it working.. i was using dynamic sql to add order id to my table names

    urrgghhh

    back to the drawing board..

    thanks guys

    --------------------------

    I long for a job where my databases dont have any pesky users accessing them 🙂

  • March 25, 2009 at 3:46 pm

    #966336

    The downside to this is that your procedure could only be run for a single server at a time. Trying to run it from multiple connections will cause problems because you have to drop and recreate the synonym for each server.

    So - you found that out 😉

    Take a look at using SQLCMD - which will allow for using variables for the servername and/or database. Put it together with a powershell script - and you eliminate the requirement for linked servers and just need to define the list of servers you want to run against.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply