September 8, 2014 at 3:47 am
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.
September 8, 2014 at 5:14 am
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.
September 8, 2014 at 5:59 am
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. 😎
September 8, 2014 at 7:08 am
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!
September 8, 2014 at 7:15 am
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
September 8, 2014 at 8:02 am
Thanks for the question. I had it right and then second guessed myself, so I learned something today.
September 8, 2014 at 8:27 am
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
September 8, 2014 at 8:38 am
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.
😎
September 8, 2014 at 10:39 am
Interesting question.
But as Erikur pointed out, the correct answer is "It depends" (on your default schema) and is not available. :hehe:
Tom
September 8, 2014 at 10:50 am
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.
September 8, 2014 at 11:37 am
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.
September 8, 2014 at 11:55 am
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.
September 8, 2014 at 1:35 pm
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
September 8, 2014 at 2:52 pm
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.
September 8, 2014 at 3:59 pm
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