synonyms' for multiple sql instances in my procedure

  • whole

    Old Hand

    Points: 376

    hi,

    i would like to create a synonym for all my sq server instances. which i can use them in my procedure to reference.

    but my question is for example i have 10 diff synonyms for 10 sql server isntances that i need to conncet to, the code in procedure decides wchih server it needs to conncet to then should i mention all my 10 synonyms based onmy dynamic value that which server ?

    please let me know.

  • Lowell

    SSC Guru

    Points: 323450

    whole (3/25/2011)


    hi,

    i would like to create a synonym for all my sq server instances. which i can use them in my procedure to reference.

    but my question is for example i have 10 diff synonyms for 10 sql server isntances that i need to conncet to, the code in procedure decides wchih server it needs to conncet to then should i mention all my 10 synonyms based onmy dynamic value that which server ?

    please let me know.

    synonyms need to point at an object,, like CREATE SYNONYM MyTABLE FOR [MyLinkedServer].production.dbo.Invoices.

    you cannot have it try to represent *part* of an object name, like CREATE SYNONYM MyServer FOR [MyLinkedServer].production.dbo

    the advantage of a synonym portability; you might even have the server name passed into your proc as a parameter, and have the proc du the following:

    1. drop the synonym. (dynamic SQL)

    2.recreate the synonym so it points to the server referenced as a parameter. (dynamic SQL)

    3. get the data. from the synonym-ed table

    can you explain a little more what you are retreiving from your 10 servers?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • whole

    Old Hand

    Points: 376

    hi,

    i wouldnt know which linked server i need to point to untill query in in SP decides it.

    so after that i am trying to use related synonym (one out 10 whcih were created before).

    is this the way i should do ? if yes then it will be like metioning synonym name 10 times as per the values i get in Sp select statement?

    do i have an easy way ?

  • whole

    Old Hand

    Points: 376

    i mean 10 different synonyms created for 10 different linked servers am pointing to.

  • whole

    Old Hand

    Points: 376

    currently we are storing those linked server/database details in a table and retrieving the values in SP based on the condtions .

    now we are trying to aviod building the connection string in SP from values from server details table and go for sysnonums.

    this is like a reserch to see if we could use synonyms in our scenario.

    hope it makes sence now.

  • Jeffrey Williams

    SSC Guru

    Points: 88531

    No, you cannot use synonyms this way. For this to work, you need to use dynamic SQL instead.

    If you try to recreate the synonym every time the procedure is called, then any concurrent calls to that procedure will be blocked. The drop/create of the synonym will not occur until a schema lock can be obtained - which won't happen until the current usage of that synonym has been completed.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • whole

    Old Hand

    Points: 376

    can i have like 10 diff synonyms (which has 10 linked serves mentioned in 10 sysnonyms).. and............

    in my SP...

    if @ID = 1

    //use Synonym1

    if @ID = 2

    //use Synonym2

    @ID = 1

    //use Synonym3.... etc ?

  • Lowell

    SSC Guru

    Points: 323450

    your if statement stuff would work, absolutely;

    what i was implying, is you could possibly rebuild the synonym every time, something like this:

    --Usage EXEC GetData 'SERVERNAME'

    Create Procedure GetData(@WhichServer varchar(30))

    AS

    BEGIN --PROC

    declare @isql varchar(8000)

    --i don't care if it has the same value or not, just rebuild my synonym dynamically

    SELECT isql = 'IF OBJECT_ID(''dbo.DistantObjects'', ''SN'') IS NOT NULL DROP SYNONYM dbo.DistantObjects;'

    EXEC(@isql)

    SELECT isql = 'CREATE Synonym DistantObjects FOR ' + QUOTENAME(@WhichServer) + '.master.dbo.sysobjects'

    EXEC(@isql)

    select * from DistantObjects

    END --PROC

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • whole

    Old Hand

    Points: 376

    yes , i understand the dynamic synonym creation too. πŸ™‚

    i was thinking to aviod the dynamic sql in the SP like ( set @sql = β€˜select * from …’ execute (@sql)

    in this case if i want to aviod the dynamic sql shall i go for the IF statement i was talking about.

    so that if there are any changes to the servers/schemas then DBA just update the synonyms ?

  • Jeffrey Williams

    SSC Guru

    Points: 88531

    Lowell (3/25/2011)


    your if statement stuff would work, absolutely;

    what i was implying, is you could possibly rebuild the synonym every time, something like this:

    --Usage EXEC GetData 'SERVERNAME'

    Create Procedure GetData(@WhichServer varchar(30))

    AS

    BEGIN --PROC

    declare @isql varchar(8000)

    --i don't care if it has the same value or not, just rebuild my synonym dynamically

    SELECT isql = 'IF OBJECT_ID(''dbo.DistantObjects'', ''SN'') IS NOT NULL DROP SYNONYM dbo.DistantObjects;'

    EXEC(@isql)

    SELECT isql = 'CREATE Synonym DistantObjects FOR ' + QUOTENAME(@WhichServer) + '.master.dbo.sysobjects'

    EXEC(@isql)

    select * from DistantObjects

    END --PROC

    Lowell, this will only work if you can guarantee that the procedure will never be called by another user at the same time. If that happens, the second user will hang waiting on the schema lock to drop the synonym.

    Using an IF statement will work, but if you are going to go that route then why not just use the linked server directly? The only reason for using synonyms at that point would be to be able to move the code to another system and reset the synonyms on that server because the linked server names are different.

    If the code is exactly the same, regardless of which server I was accessing I would just go with dynamic SQL and use the linked server names directly. I would build a table to hold the server names with an appropriate ID to identify which one and just use that to build the appropriate query and execute it using sp_executesql.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • whole

    Old Hand

    Points: 376

    thank you all. i appriciate your help πŸ™‚

    i will probabaly save server/database details on tables and pull them up to build a connstion string to call values from those database tables.

Viewing 11 posts - 1 through 11 (of 11 total)

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