Accessing multiple databases - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Accessing multiple databases

Author

Topic

Mannga
Yak Posting Veteran

70 Posts

Posted - 2002-05-29 : 08:28:47

Hi All,

Is it possible to access multiple databases from a single stored procedure?

eg.

SELECT * FROM database1.table WHERE database1.table.field = database2.table.field

That way doesn't work but is there a way?

Thanks,

OMB
Yak Posting Veteran

88 Posts

Posted - 2002-05-29 : 08:37:30

try this

SELECT * FROM database1..table t1
inner join database2..table t2
on t1.field = t2.field

Mannga
Yak Posting Veteran

70 Posts

Posted - 2002-05-29 : 08:41:40

:).... it works...

Thanks,
Gavin

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-29 : 08:43:49

the proper syntax is databasename.schemaowner.object. OMB has left out the schema owner on the assumption that the same login owns all the objects in your query. Includeing the schemaowner (if you don't know who that is, its probably dbo) is never a bad idea and may result in a performance improvement (at least in the case of executing stored procedures in another database).

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources