How to query two MS SQL DB's on the same server inside a Stored Procedure

[JMack]

Okay, so I have a problem and I would be REALLY grateful for any
assistance anyone can offer because I have found little or no help on
the web anywhere.

I want to access and do joins between tables in two different SQL db's
on the same server. Heres what Im dealing with.

In one database resides all of my security features for our clients,
where it decides who can login, etc etc....

In another database, I need to cross reference with a few fields in my
security db.

See the issue Im running into here is that because the way the people
have their databases set up for different products, I would normally
have to put these tables with security features in every database...
which is horrible, because every time I do an update I would have to
do it in 12 different places. Thats not efficient at all.

So I thought if I had one central DB, where all security features are
controlled from, that would be perfect... now the issue is cross
referencing and doing joins with other tables that ARENT in the same
db....
have I lost you yet?

I appreciate all of your help!

THANKS!!

[Simon Hayes]

go****@digitallsd.com (JMack) wrote in message news:<47**************************@posting.google. com>...

Okay, so I have a problem and I would be REALLY grateful for any
assistance anyone can offer because I have found little or no help on
the web anywhere.

I want to access and do joins between tables in two different SQL db's
on the same server. Heres what Im dealing with.

In one database resides all of my security features for our clients,
where it decides who can login, etc etc....

In another database, I need to cross reference with a few fields in my
security db.

See the issue Im running into here is that because the way the people
have their databases set up for different products, I would normally
have to put these tables with security features in every database...
which is horrible, because every time I do an update I would have to
do it in 12 different places. Thats not efficient at all.

So I thought if I had one central DB, where all security features are
controlled from, that would be perfect... now the issue is cross
referencing and doing joins with other tables that ARENT in the same
db....
have I lost you yet?

I appreciate all of your help!

THANKS!!

As a general answer to your question, you can write code like this:

select *
from dbo.ThisTable t1
join ThatDatabase.dbo.ThatTable t2
on t1.KeyColumn = t2.KeyColumn

Assuming you have SQL2000 (you didn't mention the version), you should
review the "Using Ownership Chains" topic in Books Online for
information about cross-database ownership chains (and there is an
article in the current SQL Server Magazine also).

Simon

[Jeremy Mack]

well unfortunately I'm in SQL 7 so that option doesnt apply to me.

The only other way I can see around it, is taking the tables I need
available to all the db's and replicating them from one publishing db...

which is overkill, but because of the way the system is set up this is
the only other option i can think of aside from replication is trying to
get IT to updgrade to SQL 2000

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Jeremy Mack]

Oh damn buddy,

you have saved my life. IT WORKS.
THANK YOU SO MUCH!

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Simon Hayes]

"Jeremy Mack" <go****@digitallsd.com> wrote in message
news:40*********************@news.frii.net...

well unfortunately I'm in SQL 7 so that option doesnt apply to me.

The only other way I can see around it, is taking the tables I need
available to all the db's and replicating them from one publishing db...

which is overkill, but because of the way the system is set up this is
the only other option i can think of aside from replication is trying to
get IT to updgrade to SQL 2000

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Sorry if my explanation was misleading - joining on another database will
work fine in SQL7, but in SQL2000 the default cross-database chaining
behaviour changed in SP3, which is why it's worth reviewing the
documentation.

Simon

[Erland Sommarskog]

JMack (go****@digitallsd.com) writes:

Okay, so I have a problem and I would be REALLY grateful for any
assistance anyone can offer because I have found little or no help on
the web anywhere.

I want to access and do joins between tables in two different SQL db's
on the same server. Heres what Im dealing with.

In one database resides all of my security features for our clients,
where it decides who can login, etc etc....

In another database, I need to cross reference with a few fields in my
security db.

See the issue Im running into here is that because the way the people
have their databases set up for different products, I would normally
have to put these tables with security features in every database...
which is horrible, because every time I do an update I would have to
do it in 12 different places. Thats not efficient at all.

So I thought if I had one central DB, where all security features are
controlled from, that would be perfect... now the issue is cross
referencing and doing joins with other tables that ARENT in the same
db....

I see that you have got a solution working.

But I am a little wary of hard-coding database references. The day
you need to set up a test environment on the same server, you have
trouble...

One alternative would be to have a central database which you maintain,
and then use replication to push those updates to the other places.
Although admittedly, replication might be a little heavy-duty for this...
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp