Cross-db-referencing synonyms? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Cross-db-referencing synonyms?

Author

Topic

Henrik Svensson
Starting Member

25 Posts

Posted - 2008-11-19 : 03:28:00

Hi!

Is there a way to reference the internal synonyms of a database from another database context?

Example: assume three databases on the same SQL Server instance:
DB1
DB2
DB3

DB1 contains a table called Orders.

DB2 contains a synonym for DB1.Orders called OrdersDB.Orders, which makes DB2 able to use, for example:

SELECT * FROM OrdersDB.Orders

Now, what I am trying to do is to make use of DB2's synonym while querying in the context of DB3, something like:

USE DB3
GO

SELECT * FROM DB2..OrdersDB.Orders

..which does not work. My question is, is there a way, and what do the syntax look like in that case?

With best regards,
Henrik Svensson

-----
"Rhubarb, that is Barbapapa backwards. It's called a palindrome."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 03:43:33

Does not work? Do you get an error or no result at all?
Have you checked GRANT permissions for the synonym?

E 12°55'05.63"
N 56°04'39.26"

Henrik Svensson
Starting Member

25 Posts

Posted - 2008-11-19 : 11:27:24

quote:
Originally posted by Peso

Does not work? Do you get an error or no result at all?
Have you checked GRANT permissions for the synonym?

I am system administrator in this case (as for the permission-question), and the error I get from the query above is:

Msg 7202, Level 11, State 2, Line 1
Could not find server 'DB2' 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.

With best regards,
Henrik Svensson

-----
"Rhubarb, that is Barbapapa backwards. It's called a palindrome."

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-19 : 11:31:34

The problem is using a "." in the synonym name.

USE DB3
GO

SELECT * FROM DB2..[OrdersDB.Orders]

CODO ERGO SUM

Henrik Svensson
Starting Member

25 Posts

Posted - 2008-11-19 : 15:39:56

quote:
Originally posted by Michael Valentine Jones

The problem is using a "." in the synonym name.
USE DB3
GO

SELECT * FROM DB2..[OrdersDB.Orders]

Thank you. That did not work for me either, though. But I found out why: the synonym that was added to my system was created in the way that OrdersDB was a schema, not part of the synonym, so I had added one dot too many.

This is what works in that case:

USE DB3
GO

SELECT * FROM DB2.OrdersDB.Orders

Mea culpa, for not being observant enough :-).

With best regards,
Henrik Svensson

-----
"Rhubarb, that is Barbapapa backwards. It's called a palindrome."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources