Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: Wednesday, December 11, 2013 8:06 AM
Points: 66, Visits: 122
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 @ 2:48 PM
Points: 12,909, Visits: 32,009
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
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: Wednesday, December 11, 2013 8:06 AM
Points: 66, Visits: 122
Thanks for the info. Synonym has been created!


Post #910689
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse