Create Database Synonym - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Create Database Synonym

Author

Topic

olibara
Yak Posting Veteran

94 Posts

Posted - 2015-04-16 : 07:08:55

Hello
Is it possible with SQL Server 2012 to create a Synonym on a database name ?
The reason is because there is a Test Environnement using another server some view in one Database is using different Database on the server

But in the test environnement some DB are renamed, so I need to create a Synonym on it to not have to change the view

Thanks for your help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-04-16 : 12:32:31

No, but it can be handled in the application via the connection string, which should be configurable via a config file or similar.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

olibara
Yak Posting Veteran

94 Posts

Posted - 2015-04-17 : 10:23:54

Thank You tkizer

The connection string can't help all because the goal is to be able to run a view like

Select
*
FROM
dbo.Table1
INNER JOIN db2.dbo.Table2

and db2 is in fact named dbx
so I would have liked to create a synonym as dbx=db3

Kristen
Test

22859 Posts

Posted - 2015-04-17 : 10:47:55

Probably no help, but I have done it where I have one view per table in the (remote) database.


Select 
* 
FROM
dbo.Table1
INNER JOIN dbo.RemoteTable2View

CREATE VIEW RemoteTable2View
AS
SELECT *
FROM db2.dbo.Table2

we then have a script that recreates ALL the Remote Table Views - i.e. referencing the new database e.g.


CREATE VIEW RemoteTable2View
AS
SELECT *
FROM db3.dbo.Table2

but it is quite a bit more work to run the "Change to DB3" script than it would be just changing a synonym! For us the issue is different and the tables are not like-for-like, they are different databases for different versions, so there is not actually a 1;1 correlation of columns, so my view on my "Old Version Database" might have some extra columns (as NULL) so that the live application will still find values for those, expected, columns.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources