question

GPO avatar image
GPO asked

simplifying with synonyms

I posted a recent question about making large scale changes to SSRS rdl files. The discussion has evolved from cure to prevention, and the possible use of synonyms. The scenario is that we have thousands of rdls and a respectable plenitude of them have 4-part linked server syntax like: FROM [SERVER002\INSTANCE005].DATABASE009.dbo.client Obviously this is a real pain when a server or db name changes. Does a synonym have to replace all 4 parts, or can it just replace the first three parts? So can we get FROM hardware.client instead of FROM hardware_client ? Cheers GPO
linked-serverrdlsynonyms
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
GPO avatar image
GPO answered
Well I got home from work and tested it on good ol' adventureworks, and it seems that [SERVER002\INSTANCE005].DATABASE009.dbo.client will go to [SCHEMA].hadrdware_client. In other words, one synonym per object (table/view/sproc) rather than a synonym covering [SERVER\INSTANCE].[DATABASE].[SCHEMA] While this means more synonyms than I'd hoped for, that's a trivial issue in the grand scheme of things. Still a big improvement on the current situation. Note that what you create doesn't look like it's validated until runtime, so for AdventureWorks2008: USE [AdventureWorks2008] GO CREATE SYNONYM [dbo].dw FOR [STORM].[AdventureWorksDW2008].[dbo] GO works fine until you do this: select top 10 * from dw.FactCallCenter ...at which point you get "Msg 208, Level 16, State 1, Line 1 Invalid object name 'dw.FactCallCenter'."
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You're absolutely right, synonyms aren't resolved until runtime - they are actually just stored as bits of text (check out sys.synonyms). So they don't resolve to an object, per se - which means that you can't SCHEMABIND through a synonym. But they can be handy, for sure.
2 Likes 2 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.