Hey guys,
I have a SQL Server database with two databases in it.
Let's call them database1 and dataase 2.
I need a query that selects data from table1 in both databases.
How would I accomplish this?
Select field1
from database1.table1,
database2.table1
where something is =something
Also I need to build the database name for a query from a string
i.e. Org_xxxx where xxxx is passed in as a parameter.
Set DBName= 'Org_" + Cast(xxxx, as char(4)
any ideas?
Thanks
- Mike Ross
Blessed is the man who fears the LORD, who delights greatly in his commandments. Psalms 112:1
Mark Hensler posted this at 17:02—8th November 2002.
He has: 4,044 posts
Joined: Aug 2000
Concept sounds right. Did you try it and get an error?
Mark Hensler ["Max Albert"] [Email]
If there is no answer on Google, then there is no question.
mycoolross posted this at 17:04—8th November 2002.
They have: 82 posts
Joined: Oct 2001
Yup get an error on all of it.
Thanks
Mark Hensler posted this at 17:06—8th November 2002.
He has: 4,044 posts
Joined: Aug 2000
can you copy&paste the errors? (and code)
Peter J. Boettcher posted this at 01:22—9th November 2002.
They have: 812 posts
Joined: Feb 2000
I think you have to use a union and make sure whatever connection string you're using has the right permissions in both databases. To be on the safe side try adding the owner of the objects, for example:
SELECT field1 FROM database1.dbo.table1 WHERE something IS something
UNION
SELECT field1 FROM database2.dbo.table1 WHERE something IS something
ORDER BY field1
PJ | Are we there yet?
pjboettcher.com