March 25, 2011 at 12:22 pm
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.
March 25, 2011 at 1:00 pm
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
March 25, 2011 at 1:17 pm
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 ?
March 25, 2011 at 1:19 pm
i mean 10 different synonyms created for 10 different linked servers am pointing to.
March 25, 2011 at 1:23 pm
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.
March 25, 2011 at 1:35 pm
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
“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, 2011 at 1:41 pm
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 ?
March 25, 2011 at 1:58 pm
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
March 25, 2011 at 2:17 pm
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 ?
March 25, 2011 at 4:55 pm
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
“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 28, 2011 at 10:29 am
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 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply