November 19, 2008 at 4:58 am
Hi all,
I Have a bit unusual environment and have some problems with Synonyms.
I have a main productions Server (2005 ENT).
That server uses Mirroring to copy data to a second server.
Since the DBs in the second server are on Restoring state, I created Snapshots on those DBs for the DEV & QA dept.
Each Dept. owns her own DB on the second server, and for the need to retreive data from the snapshot (through scalar functions), I created Synonyms to those functions.
The only problem is when I try to run the function through the Synonym, I get an error message:
Cannot find either column "dbo" or the user-defined function or aggregate "dbo.'SynonymName' ", or the name is ambiguous.
I tried to use prefix (schema name before the Synonym name), tried to use DB Name and nothing!
I can't use the function through the Synonym (I must say that Synonyms pointing to tables, work just fine).
Is there anything I should know when creating or using Synonyms to functions?
Please help.
THank you,
Oz
November 19, 2008 at 11:34 pm
No one knows?!
There must be a solution for creating Synonyms on functions.
:ermm:
November 20, 2008 at 2:51 am
Never mind... I found the problem and the solution.
Thanks anyway,
Oz
December 12, 2008 at 1:48 am
whats your solution?Can you post.I have similar such problem.Will check whether your solution can be used.
January 29, 2009 at 3:24 am
I had the same problem, but have not found a solution yet
The cause
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=370335
If someone has found a workaround I'd appreciate them posting it.
I'm currently using OPENQUERY(LinkedServerName,'Select [DatabaseName].[SchemaName].[UDFName](ParameterValue)') as a workaround, but would prefer to access to the linked servers to be in Synonyms for consistency.
August 15, 2011 at 3:09 pm
anyone found this solution? I am stuck with this issue and really need some fix soon.
Thanks!
May 23, 2012 at 10:23 am
can u post the solution its very urgent!!!!
October 16, 2025 at 6:49 pm
Oz-860645 was happy to come here for a quick answer but then says "Nevermind, I figured it out." and disappeared.
When I created the synonym I did it without the servername. I used this syntax in the synonym creation...
IF EXISTS(SELECT 1/0 FROM sys.synonyms WHERE name = 'syn_XXXXX')
DROP synonym syn_XXXXX
GO
CREATE SYNONYM [dbo].[syn_XXXXX] FOR [MyDB].[dbo].[XXXXX] --Do NOT provide a server if it is not a linked server.
GO
In the code that uses this synonym reference it like this...
SELECT Column1, dbo.syn_XXXXX(Column1,NULL) AS Test
INTO #temp
FROM syn_Table
WHERE ID = @BatchID;
That worked for me. The SELECT statement also does NOT work without dbo qualifying the function synonym.
October 17, 2025 at 6:49 pm
Oz-860645 was happy to come here for a quick answer but then says "Nevermind, I figured it out." and disappeared.
When I created the synonym I did it without the servername. I used this syntax in the synonym creation...
IF EXISTS(SELECT 1/0 FROM sys.synonyms WHERE name = 'syn_XXXXX') DROP synonym syn_XXXXX GO CREATE SYNONYM [dbo].[syn_XXXXX] FOR [MyDB].[dbo].[XXXXX] --Do NOT provide a server if it is not a linked server. GO
In the code that uses this synonym reference it like this...
SELECT Column1, dbo.syn_XXXXX(Column1,NULL) AS Test INTO #temp FROM syn_Table WHERE ID = @BatchID;
That worked for me. The SELECT statement also does NOT work without dbo qualifying the function synonym.
Looks good by me especially since it supports the 2 part naming convention. Also and as a side, the DROP SYNONYM IF EXISTS syntax works here (in SQL Server).
AND, Welcome Aboard!
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply