June 13, 2007 at 8:31 am
I need to create a sql statement that reaches across multiple databases. How can you reference a table in a different d/b in a sql stmt? I know in Oracle I could create a db link to reach across databases, or just reference the schema name when going across schema's.
Any help is appreciated.
-k
June 13, 2007 at 8:54 am
Kevin,
If you need to reach an object on the same server in a different database use:
databasename.schemaname.objectname like MyOtherDB.dbo.MyTable
the login who is running code has to have permissions to the other database too.
if you need to reach to another server create a Linked Server (read Books Online on this topic) and use the syntax
LinkedServerName.DatabaseName.Schemaname.Objectname
Regards,Yelena Varsha
June 13, 2007 at 9:00 am
I tried that and am getting the message:
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.business_unit'.
The table name is business_unit, owned by dbo, on the dw database.
I am trying to access it from the stage d/b.
Am I missing something small or making a dumb mistake?
June 13, 2007 at 9:13 am
Post the whole query you're running (or the proc text if it's a proc).
June 15, 2007 at 4:47 am
In SQL Server you can use the 3 or 4 part naming convention.
LINKEDSERVER.DATABASENAME.OWNERNAME.OBJECTNAME for REMOTESERVER
DATABASENAME.OWNER.OBJECTNAME for LOCAL SERVER DBs.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 15, 2007 at 9:01 pm
in 2005 it's not OWNER anymore, but SCHEMA:
LINKEDSERVER.DATABASENAME.SCHEMA.OBJECTNAME for REMOTESERVER
DATABASENAME.SCHEMA.OBJECTNAME for LOCAL SERVER DBs.
---------------------------------------
elsasoft.org
June 18, 2007 at 7:14 am
To access large remote objects, you should check out the openquery() options for data access to linked servers. It can often provide better performance by executing the query on the remote server and only returning the resultset rather than the entire rowset needed to run the query locally.
June 22, 2007 at 5:55 am
It is till not working for me.
I am in the SQL Worksheet with the STAGE d/b selected.
I run the following query:
select *
from [DSTAGE.dbo.SALES]
I get the following results:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DSTAGE.dbo.SALES'.
What am I doing wrong?
Kevin
June 22, 2007 at 7:20 am
the query should be select * from [DSTAGE].dbo.SALES.
your syntax is wrong.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply