May 6, 2008 at 6:43 am
Hmm. BOL isn't being too clear on this, so I'm going to post.
I know you can create synonyms for Linked Servers (or I assume you can), but do you have to point the synonym to a specific table object or can you just have the (ServerName).(DBName) as the object the synonym is pointing to?
I've tried creating the synonyms on my dev box pointing to my reporting box, but when I go to execute my proc that utilizes them, it comes back telling me that the object is invalid. I don't want to point the synonym to a specific table as I usually pull from several tables on my linked server. Here's a simplified version of what I'm doing:
Create Synonym MyDB FOR ServerName.MyDB
GO
Create Procedure MyProc AS
Select top 10 LastName
from MyDB.dbo.MyTable
GO
Am I missing something?
Thanks in advance for the assist. This is my first time actually using synonyms.
May 6, 2008 at 7:44 am
Hm.. according to BOL you cannot create a synonym for database, only for objects existing in database (roughly speaking)
Synonyms can be created for the following types of objects:
Assembly (CLR) Stored Procedure
Assembly (CLR) Table-valued Function
Assembly (CLR) Scalar Function
Assembly Aggregate (CLR) Aggregate Functions
Replication-filter-procedure
Extended Stored Procedure
SQL Scalar Function
SQL Table-valued Function
SQL Inline-table-valued Function
SQL Stored Procedure
View
Table (User-defined)
Piotr
...and your only reply is slàinte mhath
May 6, 2008 at 7:49 am
This is odd. I could swear I've heard people talk about creating synonyms for their Linked Servers....
I guess I have more research to do.
May 6, 2008 at 8:52 am
You can create a "synonym" for a linked server, but it's not the same thing as the synonyms created by "create synonym". (It's the English language definition of "synonym", as opposed to the SQL 2005 definition of "synonym", if that clears anything up. That's why it doesn't come up if you search BOL for "synonym".)
You do this by defining a name and a data source when you use sp_addlinkedserver.
sp_setnetname might also be relevant to this. BOL has the details on that.
Is that what you're looking for?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 9:02 am
It might be. I have to test it.
Interestingly enough, when setting up Linked SQL Servers in the 2005 GUI, it doesn't allow you to assign a different name than the actual Server name. I just verified this now.
Yet, this functionality was available in the SQL 2000 EM GUI... Hrm. Wonder why they removed it.
May 6, 2008 at 9:14 am
You can name the linked server whatever you like in GUI also:
in Linked server enter name you want to use
in Provider select SQL Native Client
in Data source enter server machine name.
HTH
Piotr
...and your only reply is slàinte mhath
May 6, 2008 at 11:26 am
The trick, if you want to name it, is don't select "SQL Server", select "Other" and then "Native SQL" as the provider. Then you can name it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 7, 2008 at 12:44 pm
AHHA! I knew I had to be missing something.
Thanks, Guys. That clears things up. @=)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply