Synonyms 1

  • Eirikur Eiriksson (9/7/2014)


    ...but it works for Joe in HR (that's his default schema);-)

    😎

    It depends. It only works if Joe (or anyone else with default schema 'HumanResources') would create the synonym as described and runs the query.

  • Interesting question. I immediately thought of the default schema and had to re-read the question to make sure I didn't miss it somewhere. Thanks for the question...helps to wake up on a Monday morning.

  • Mighty (9/8/2014)


    Eirikur Eiriksson (9/7/2014)


    ...but it works for Joe in HR (that's his default schema);-)

    😎

    It depends. It only works if Joe (or anyone else with default schema 'HumanResources') would create the synonym as described and runs the query.

    It does depend.

    This should be stated in the answer.

    ---------------
    Mel. 😎

  • 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!

  • 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.

    There are no facts, only interpretations.
    Friedrich Nietzsche

  • Thanks for the question. I had it right and then second guessed myself, so I learned something today.



    Everything is awesome!

  • Hello Mr. Steve,

    thanks for question.

    I tried in SQL2012 and it works fine without any error.

    like this....

    CREATE SYNONYM Emp FOR Appconfig

    SELECT * FROM emp

    it will successfully show all records of table.

    Thanks,

    Sanjay Patel

  • itsanjayg (9/8/2014)


    Hello Mr. Steve,

    thanks for question.

    I tried in SQL2012 and it works fine without any error.

    like this....

    CREATE SYNONYM Emp FOR Appconfig

    SELECT * FROM emp

    it will successfully show all records of table.

    Thanks,

    Sanjay Patel

    As I pointed out earlier, there is an exception to this when the table is in the user's default schema in the current database.

    😎

  • Interesting question.

    But as Erikur pointed out, the correct answer is "It depends" (on your default schema) and is not available. :hehe:

    Tom

  • I'm guessing this doesn't work because of the database objects in the database you are using. But the syntax looks okay.

    This works fine for me:

    create synonym tracesynonym for tracelog

    select * from tracesynonym

    No errors and get all data in the select.



    Everybody wants some....Data that is.

  • I am able to create the synonym and select from the table just fine in 2008 R2. The question specifically states the table exists hence the command will work just fine.

  • Well, based on my selection of the answer, I got it wrong. The solution is supposed to be:

    Correct answer:

    I can create the synonym, but if I use it in a SELECT statement, I will get an invalid object error

    Explanation:

    The synonym will be created, but if I execute this:

    SELECT *

    FROM emp

    I'll get this error:

    Msg 5313, Level 16, State 1, Line 1

    Synonym 'emp' refers to an invalid object.

    This is because of deferred name resolution. The object the synonym refers to need not be created when the synonym is created. References are checked at run time.

    Ref: CREATE SYNONYM - http://technet.microsoft.com/en-us/library/ms177544%28v=sql.110%29.aspx

    So, the correct answer in short:

    "Correct answer:

    I can create the synonym, but if I use it in a SELECT statement, I will get an invalid object error"

    says it does not work just fine.



    Everybody wants some....Data that is.

  • Eirikur Eiriksson (9/8/2014)


    itsanjayg (9/8/2014)


    Hello Mr. Steve,

    thanks for question.

    I tried in SQL2012 and it works fine without any error.

    like this....

    CREATE SYNONYM Emp FOR Appconfig

    SELECT * FROM emp

    it will successfully show all records of table.

    Thanks,

    Sanjay Patel

    As I pointed out earlier, there is an exception to this when the table is in the user's default schema in the current database.

    😎

    +1

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Mark D Powell (9/8/2014)


    I am able to create the synonym and select from the table just fine in 2008 R2. The question specifically states the table exists hence the command will work just fine.

    The question states I'm working in the AdventureWorks2008 database. The table exists, but it's schema is the HumanResources schema. As a regular user, I default to the dbo schema, or potentially another schema. In that case, this doesn't work because the synonym needs to include enough of a name (2, 3, or 4 part) to correctly resolve.

    I could have stated the default schema for the user.

  • I don't use synonyms but had always wondered what would happen if I ran into a synonym such as this. My default schema is dbo and I tested it and it came out as per the question explanation. Makes sense to me. Thanks for the question Steve.

Viewing 15 posts - 16 through 30 (of 36 total)

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