Synonym with servername\instancename

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Synonym with servername\instancename

Synonym with servername\instancename

Rate Topic

Display Mode

Topic Options

Author

Message

donato1026

Posted Wednesday, June 16, 2010 9:18 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 5:50 AM
Points: 108, Visits: 122

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

Post #938322

spaghettidba

Posted Wednesday, June 16, 2010 9:25 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, May 16, 2013 8:29 AM
Points: 4,804, Visits: 8,067

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

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

Get your two-cent-answer quickly
The Spaghetti DBA

Post #938332

Ninja's_RGR'us

Posted Wednesday, June 16, 2010 9:30 AM

SSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 21,357, Visits: 9,535

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.

Post #938338

donato1026

Posted Wednesday, June 16, 2010 9:41 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 5:50 AM
Points: 108, Visits: 122

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

Post #938349

calvo

Posted Wednesday, June 16, 2010 9:45 AM

Ten Centuries

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:31 AM
Points: 1,157, Visits: 3,077

pics pl0x

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.

Post #938354

donato1026

Posted Wednesday, June 16, 2010 10:21 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 5:50 AM
Points: 108, Visits: 122

Post #938391

« Prev Topic | Next Topic »

Permissions