September 9, 2014 at 7:47 am
in SQL 2012 I am able to create a synonym and select from it with no issues.
September 9, 2014 at 8:51 am
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.
September 11, 2014 at 1:46 am
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
September 11, 2014 at 4:15 am
Nice question:-D
Manik
You cannot get to the top by sitting on your bottom.
September 12, 2014 at 3:06 am
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
September 12, 2014 at 3:44 am
ouch, why have a assumed the table Employees exist !? :Wow:
September 12, 2014 at 3:54 pm
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