Synonyms 1

Question

Post reply

Synonyms 1

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

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

B's-Data SSChasing Mays Points: 607 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

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.

SQLWinther SSCertifiable Points: 6294 More actions · Answer 3

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

manik_anu SSCrazy Points: 2377 More actions · Answer 4

Nice question:-D

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

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 5

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

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 6

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 7

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.