Creating a SQL 2008 synonym to named instance

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Creating a SQL 2008 synonym to named instance...

Creating a SQL 2008 synonym to named instance

Rate Topic

Display Mode

Topic Options

Author

Message

DataBase Assassin

Posted Thursday, April 22, 2010 4:02 PM

Valued Member

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:02 AM
Points: 66, Visits: 108

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

Lowell

Posted Thursday, April 22, 2010 4:11 PM

SSChampion

Group: General Forum Members
Last Login: Today @ 8:29 PM
Points: 11,645, Visits: 27,738

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

DataBase Assassin

Posted Monday, April 26, 2010 2:12 PM

Valued Member

Group: General Forum Members
Last Login: Monday, February 11, 2013 8:02 AM
Points: 66, Visits: 108

Thanks for the info. Synonym has been created!

Post #910689

« Prev Topic | Next Topic »

Permissions