Synonyms 1

Question

Synonyms 1

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

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

Mighty SSCrazy Eights Points: 9063 More actions · Answer 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.

Ed Wagner SSC Guru Points: 287024 More actions · Answer 2

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SqlMel SSCrazy Points: 2901 More actions · Answer 3

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

Dave62 SSCertifiable Points: 7459 More actions · Answer 4

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!

barry.mcconnell Default port Points: 1447 More actions · Answer 5

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

Dana Medley SSCertifiable Points: 6764 More actions · Answer 6

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

Everything is awesome!

itsanjayg SSC Enthusiast Points: 114 More actions · Answer 7

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 8

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.

😎

TomThomson SSC Guru Points: 104773 More actions · Answer 9

Interesting question.

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

Tom

steveflatbush SSC Rookie Points: 38 More actions · Answer 10

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.

Mark D Powell SSCarpal Tunnel Points: 4775 More actions · Answer 11

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.

steveflatbush SSC Rookie Points: 38 More actions · Answer 12

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.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

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

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 14

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.

Ken SSCoach Points: 17191 More actions · Answer 15

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.