Synonyms
in [Programming SQL]
|
Prev: recursive call in TSQL
Next: Trigger Syntax
From: SetonSoftware on 18 Jun 2010 09:44 We have a number of stored procs/functions in a support databse which looks to a production database for the data. Rather than do this in each procedure: SELECT col1 FROM ProdServer.dbo.MyTable We were hoping to create a synonym, say SourceDB, for ProdServer.dbo and prepend it to the table name like this: SELECT col1 FROM SourceDB.MyTable Then we can easily change the data source simply by changing the synonym. Unfortunately this doesn't work. I've only been able to create a synonym that points to a specific databse object but that means I need to create and maintain one synonym for each object. Is there any way to do what I'm asking? Thanks Carl
From: Eric Isaacs on 18 Jun 2010 13:26 Synonyms point to specific database objects. They do not apply to the server in general. But your idea of changing the synonym is sound and is a way to make your code dynamic without doing dynamic SQL. The table and column information is stored in the Inforation_schema.tables and information_schema.columns views. You could create synonyms to say each information_schema.columns view in each of your databases, then select * from the columns views in each database to determine which tables have col1 and create a synonym on the fly that points to that table in that database and execute the other procedure that utilizes the synonym. -Eric Isaacs
From: John Bell on 18 Jun 2010 15:55 On Fri, 18 Jun 2010 06:44:17 -0700 (PDT), SetonSoftware <seton.software(a)verizon.net> wrote: >We have a number of stored procs/functions in a support databse which >looks to a production database for the data. Rather than do this in >each procedure: > >SELECT col1 FROM ProdServer.dbo.MyTable > >We were hoping to create a synonym, say SourceDB, for ProdServer.dbo >and prepend it to the table name like this: > >SELECT col1 FROM SourceDB.MyTable > >Then we can easily change the data source simply by changing the >synonym. > >Unfortunately this doesn't work. I've only been able to create a >synonym that points to a specific databse object but that means I need >to create and maintain one synonym for each object. Is there any way >to do what I'm asking? > >Thanks > >Carl How about using views? John
From: Erland Sommarskog on 18 Jun 2010 17:38 SetonSoftware (seton.software(a)verizon.net) writes: > We have a number of stored procs/functions in a support databse which > looks to a production database for the data. Rather than do this in > each procedure: > > SELECT col1 FROM ProdServer.dbo.MyTable > > We were hoping to create a synonym, say SourceDB, for ProdServer.dbo > and prepend it to the table name like this: > > SELECT col1 FROM SourceDB.MyTable > > Then we can easily change the data source simply by changing the > synonym. > > Unfortunately this doesn't work. I've only been able to create a > synonym that points to a specific databse object but that means I need > to create and maintain one synonym for each object. Is there any way > to do what I'm asking? No, but you can vote for this feature here: https://connect.microsoft.com/SQLServer/feedback/details/311079/expand- synonym-to-other-entities-database-linked-server In the meanwhile, you could write a stored procedure that loops over of the synonyms, and retargets them to a different database. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: SetonSoftware on 21 Jun 2010 11:55
On Jun 18, 5:38 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > SetonSoftware (seton.softw...(a)verizon.net) writes: > > We have a number of stored procs/functions in a support databse which > > looks to a production database for the data. Rather than do this in > > each procedure: > > > SELECT col1 FROM ProdServer.dbo.MyTable > > > We were hoping to create a synonym, say SourceDB, for ProdServer.dbo > > and prepend it to the table name like this: > > > SELECT col1 FROM SourceDB.MyTable > > > Then we can easily change the data source simply by changing the > > synonym. > > > Unfortunately this doesn't work. I've only been able to create a > > synonym that points to a specific databse object but that means I need > > to create and maintain one synonym for each object. Is there any way > > to do what I'm asking? > > No, but you can vote for this feature here:https://connect.microsoft.com/SQLServer/feedback/details/311079/expand- > synonym-to-other-entities-database-linked-server > > In the meanwhile, you could write a stored procedure that loops over > of the synonyms, and retargets them to a different database. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text - > > - Show quoted text - Very good enhancement idea! I just voted for it. Thanks for pointing it out. Carl |