Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Synonym with servername\instancename Expand / Collapse
Author
Message
Posted Wednesday, June 16, 2010 9:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Wednesday, June 16, 2010 9:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:18 AM
Points: 5,014, Visits: 10,515
I think you confused server and database. Does this work?

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



--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #938332
Posted Wednesday, June 16, 2010 9:30 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:08 PM
Points: 21,385, Visits: 9,602
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
Posted Wednesday, June 16, 2010 9:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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
Posted Wednesday, June 16, 2010 9:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, July 21, 2014 10:07 AM
Points: 1,260, Visits: 3,545
pics pl0x

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
Post #938354
Posted Wednesday, June 16, 2010 10:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 5:50 AM
Points: 108, Visits: 122
Post #938391
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse