Synonym with servername\instancename

  • I am trying to create a synonym as follows:

    Synonym name: Product

    Synonym schema: dbo

    Server name: 06dw

    Database name: [06websql\websql]

    Schema: dbo

    Object type: this is blank

    Object name: Product

    I have tried SO MANY different scenarios for the server name, with and without brackets, with the database name, et cetera. . .

    My linked server is listed as: 06WEBSQL\WEBSQL

    I cannot get ANYTHING to work as far as a synonym is concerned with this bloody linked server. However, I can use openquery, but I don't want to.

    Does anyone have any suggestions please?

    Thanks in advance,

    ~D

  • I think you confused server and database. Does this work?

    CREATE SYNONYM [dbo].[Product] FOR [06WEBSQL\WEBSQL].[06dw].[dbo].[Product]

    -- Gianluca Sartori

  • Gianluca beat me to it, but this is straight from BOL (Create synonym).

    Assuming you didn't reverse the db and server in your question, this should work :

    EXEC sp_addlinkedserver '06dw';

    GO

    USE tempdb;

    GO

    CREATE SYNONYM dbo.Product FOR [06dw].[06websql\websql].dbo.Product;

    GO

    The addlinkedserver needs to be executed only once for that server no matter how many synonyms you need.

    This is also another way to make sure the create synonym works.

    Do Select * FROM [06dw].[06websql\websql].dbo.Product

    When that query works, the create Syn. should also work.

  • Gianluca Sartori (6/16/2010)


    I think you confused server and database. Does this work?

    CREATE SYNONYM [dbo].[Product] FOR [06WEBSQL\WEBSQL].[06dw].[dbo].[Product]

    This worked! Thank you!

    I was about to throw my desk through the wall. . .

  • pics pl0x

    ______________________________________________________________________________________________
    Forum posting etiquette.[/url] Get your answers faster.

  • 🙂

  • You lose some flexibility with that linkserver:-D

Viewing 7 posts - 1 through 6 (of 6 total)

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