Retrieving Data From Two Different Databases

Question

Post reply

Retrieving Data From Two Different Databases

Julie Marshall

SSC Rookie

Points: 33
More actions
September 28, 2006 at 9:29 am

#192593

Hello,
I'm trying to retrieve data from two different databases. these databases are preexisting, on the same server and can not be combined. I've tried the database.owner.table route in query analyzer and get an error for whichever database is not selected that says "Server: Msg 107, Level 16, State 2, Line 1
The column prefix 'Insert TABLE Name Here' does not match with a table name or alias name used in the query."
Can anyone please help me?
Is this possible even?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

You can use the following syntax

SELECT a.*,b.*

FROM dbo.currentdbtable a

JOIN

secondb..tablename b ON b.somecolumn = a.somecolumn.

Please post your query, so that it would be more clear.

Prasad Bhogadi
www.inforaise.com

Julie Marshall SSC Rookie Points: 33 More actions · Answer 2

I'm not sure what you mean by more clear, however I will endeaver to comply.

database1 database2

table1 table2

results set from

database1 - table1

database2 - table2

The data is not coming from two different tables in the same database, it is a compilation of data from two different databases.

Hope that clears things up a bit.

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 3

Ok, if you are having both the databases on the same server, which as per your post I believe is the case.

Just use databasename..tablename in your query to access the data from the second database in your query.

If you are connected to database1 and you want to access data from a table residing on database2

use the following syntax

select * from

database2..tablename

Prasad Bhogadi
www.inforaise.com

Julie Marshall SSC Rookie Points: 33 More actions · Answer 4

I figured out where I was going wrong.

I used the technique you had suggested above, minus the join, and it wasn't working, however. I was using tableName.columnName to access the data in the columns. Apparently SQL didn't understand the tableName part, I added an alias to the database.dbo.tablename line, like so : database.dbo.table as tableName and it works now.

I have managed to get the data from the two databases. Now I must figure out how to get the data into one dataSet or temporary table if you will. As the two databases have absolutely no columns that can be used in a join this may be a bit tricky.

Thanks for your help.

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 5

Write a stored procedure to insert data into a temporary table and you can manipulate on the data inserted to temp table to retrieve the output in your desired format.

Prasad Bhogadi
www.inforaise.com

sreeni pitti SSC Rookie Points: 33 More actions · Answer 6

On secuity tab of server properties set "Allow cross database ownership chaining' then your queries will work