Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Referencing Remote Data Expand / Collapse
Author
Message
Posted Tuesday, January 10, 2012 9:45 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:34 AM
Points: 485, Visits: 1,173
We have a reporting system that contains monthly snapshots of our core system database. Each snapshot is a database with a naming convention like Snapshot20111231. We frequently have SSRS reports that compare this month vs last month. I use synonyms in the main report query in order to avoid the messiness of constructing a dynamic sql statement. The reports are based on sprocs where the first thing that happens is redefining the synonyms to point to the month end snapshots the user requests via report parameters. It works well. Only drawback I see is the elevated permissions required by the reporting login to drop and recreate the synonyms.


Post #1233357
Posted Tuesday, January 10, 2012 11:05 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:17 AM
Points: 243, Visits: 787
We frequently have SSRS reports that compare this month vs last month. I use synonyms in the main report query in order to avoid the messiness of constructing a dynamic sql statement. The reports are based on sprocs where the first thing that happens is redefining the synonyms to point to the month end snapshots the user requests via report parameters.



That's a pretty creative solution to managing repetitive files where the date is part of the names. I have a couple of processes to implement this way. Thanks for posting it.


Sigerson

"No pressure, no diamonds." - Thomas Carlyle
Post #1233407
Posted Friday, May 24, 2013 7:36 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 7:45 AM
Points: 473, Visits: 166
I like synonyms over views. They can be fully automated for remote servers during a restore process. Since they have a standardized form and their data is in sys.synonyms, they are especially easy to change over. Views have to be scripted to properly change the objects and that's a pain.
Post #1456500
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse