Post reply

Creating a SQL 2008 synonym to named instance

  • April 22, 2010 at 4:02 pm

    #231976

    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?

  • April 22, 2010 at 4:11 pm

    #1156506

    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!

  • April 26, 2010 at 2:12 pm

    #1157872

    Thanks for the info. Synonym has been created!

Viewing 3 posts - 1 through 2 (of 2 total)

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