Synonyms 1

  • in SQL 2012 I am able to create a synonym and select from it with no issues.

  • B's-Data (9/9/2014)


    in SQL 2012 I am able to create a synonym and select from it with no issues.

    Please re-read the question. If you use AdventureWorks and connect as sysadmin, you will not have this work as Employee will not resolve correctly do to schema differences.

  • Dave62 (9/8/2014)


    SqlMel (9/8/2014)


    It does depend.

    This should be stated in the answer.

    No, "it depends" is the default assumption for any SQL Server problem, question, or answer. :w00t:

    Enjoy!

    It depends is my favourite answer to anythng that has to do with SQL Server

    /Håkan Winther
    MCITP:Database Developer 2008
    MCTS: SQL Server 2008, Implementation and Maintenance
    MCSE: Data Platform

  • Nice question:-D

    Manik
    You cannot get to the top by sitting on your bottom.

  • barry.mcconnell (9/8/2014)


    I got it right but for a different reasoning. Since the HumanResources schema wasn't specified, the synonym would be created for the non-existant dbo.Employee table and the query would fail because that table didn't exist.

    No, MSSS use late binding for SYNONYMs. When created SYNONYM will be object of the current (when not specified explicitly) schema itself. But what schema it will be referencing depends on run-time (if not specified explicitly).

    For example,

    -- current schema is dbo

    drop synonym t1

    go

    drop synonym guest.t2

    go

    -- schema guest may not exist

    create synonym t1 for guest.test

    -- schema guest must exist

    create synonym guest.t2 for test

    select * from t1 -- references guest.test as explicitly specified at creation

    select * from guest.t2 -- references guest.test as explicitly specified at run-time

  • ouch, why have a assumed the table Employees exist !? :Wow:

  • Iulian -207023 (9/12/2014)


    ouch, why have a assumed the table Employees exist !? :Wow:

    the question says AdventureWorks database, which has a HumanResources.Employee table.

Viewing 7 posts - 31 through 36 (of 36 total)

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