Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

create synonym to schema Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 8:18 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 7, 2016 6:35 AM
Points: 364, Visits: 732
Hi
I have a dev database and a live database

both have linked servers to other databases in the dev environments and live environments

I would like to know if it is posible to create a synonym to a schema rather than an object

so In the dev database my synonym would be created something like

create sysnonym Server.TheSchema for [DevServer].[Databade].[TheSchema]

and in live

create sysnonym Server.TheSchema for [LiveServer].[Databade].[TheSchema]

This way I would only need to alter the information in my synonym rather than each time i reference a table.

I know the about doesnt work so i was hoping that there was a way.

Thanks
Post #1412457
Posted Monday, January 28, 2013 8:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:22 PM
Points: 14,437, Visits: 37,833
nope a synonym must point to an object ( an object that would exist in a databases sys.objects view), and not part of the name of an object.

so by definitions, it is typically used for tables , views etc. very handy for aliasing four part objects, like CREATE SYNONYM myTable FOR OtherServer.Databasename.dbo.Tablename


Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #1412459
Posted Monday, January 28, 2013 8:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 14, 2016 3:58 AM
Points: 5,969, Visits: 6,063
Synonyms can be created for the following types of objects:

Assembly (CLR) Stored Procedure
Assembly (CLR) Table-valued Function
Assembly (CLR) Scalar Function
Assembly Aggregate (CLR) Aggregate Functions
Replication-filter-procedure
Extended Stored Procedure
SQL Scalar Function
SQL Table-valued Function
SQL Inline-table-valued Function
SQL Stored Procedure
View
Table (User defined) - Includes local and global temporary tables




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1412461
Posted Monday, January 28, 2013 9:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, July 7, 2016 6:35 AM
Points: 364, Visits: 732
thanks a lot for the replies guys
Post #1412495
Posted Monday, January 28, 2013 10:12 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:22 PM
Points: 3,644, Visits: 6,039
Nope, you have to synonym every object:

[LiveServer].[Databade].[TheSchema].[ObjectName1] --> [DevServer].[Databade].[TheSchema].[ObjectName1]

[LiveServer].[Databade].[TheSchema].[ObjectName2] --> [DevServer].[Databade].[TheSchema].[ObjectName2]

etc.



SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Post #1412533
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse