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
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'."
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.
