Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating a SQL 2008 synonym to named instance Expand / Collapse
Author
Message
Posted Thursday, April 22, 2010 4:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 12, 2016 9:44 AM
Points: 66, Visits: 139
I am trying to create a synonym to a SQL 2005 named instance from a SQL 2008 server. Initially the synonym created used the named instance SQLCLUS3\BIZTALK:

CREATE SYNONYM [spAX].[BizTalkMsgboxDb_Tablename] FOR [SQLCLUS3\BIZTALK].[BizTalkMsgboxDb].[dbo].[Tablename]

which generated this error:

Could not find server 'SQLCLUS3\BIZTALK' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.


Taking a clue from the error, a linked server was created (connectivity confirmed) and the synonym was dropped and recreated using the link server name SQLCLUS3BIZTALK. This command was used:

CREATE SYNONYM [spAX].[BizTalkMsgboxDb_Tablename] FOR [SQLCLUS3BIZTALK].[BizTalkMsgboxDb].[dbo].[Tablename]

and again this error came back:

Could not find server 'SQLCLUS3BIZTALK' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.


What is the correct step(s) to create a synonym to a named instance?



Post #909067
Posted Thursday, April 22, 2010 4:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 14,326, Visits: 37,418
do a select * from sys.servers
quite often, the alias for your linked server is different than the name of the actual server.
it's the name of the linkedserver, not the name of the real server, that you need to use to construct the synonym.

so for example, while the actual server might be named [SQLCLUS3\BIZTALK], i could create the Alias for it as BIZ or MyLinkedServer or whatever i felt was a good mnemonic, so that would be the right portion to use for your synonym.

[BIZ].[BizTalkMsgboxDb].[dbo].[Tablename]
[MyLinkedServer].[BizTalkMsgboxDb].[dbo].[Tablename]


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!
Post #909074
Posted Monday, April 26, 2010 2:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 12, 2016 9:44 AM
Points: 66, Visits: 139
Thanks for the info. Synonym has been created!


Post #910689
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse