Post reply

Synonym with servername\instancename

  • June 16, 2010 at 9:18 am

    #233902

    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

  • June 16, 2010 at 9:25 am

    #1181100

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

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

    -- Gianluca Sartori

  • June 16, 2010 at 9:30 am

    #1181104

    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.

  • June 16, 2010 at 9:41 am

    #1181115

    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. . .

  • June 16, 2010 at 9:45 am

    #1181120

    pics pl0x

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

  • June 16, 2010 at 10:21 am

    #1181154

    🙂

  • September 12, 2014 at 11:44 pm

    #1745224

    You lose some flexibility with that linkserver:-D

    Jason
    http://dbace.us
    😛

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

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