Post reply

create synonym to schema

  • January 28, 2013 at 8:18 am

    #267719

    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

  • January 28, 2013 at 8:23 am

    #1581649

    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!

  • January 28, 2013 at 8:25 am

    #1581650

    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

  • January 28, 2013 at 9:15 am

    #1581678

    thanks a lot for the replies guys

  • January 28, 2013 at 10:12 am

    #1581709

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply