dyarbrough dyarbrough - 2 years ago 76
SQL Question

SQL join multiple tables from multiple database connections

I've been searching on this issue for 2 days with no luck so I guess it's time to make a question; I know this has been asked before, but nothing that I've found has been able to solve my issue. I have two tables from two different database connections that I want to perform a join on. So far I have:

$conn1 = odbc_connect("db-1","user","pass")
or die ("Couldn't Connect to Server");

$conn2 = odbc_connect("db-2","user","pass")
or die ("Couldn't Connect to Server");

$Query = 'SELECT PV_Job1.PlantCode,


LEFT OUTER JOIN spec.public.specification specification
ON PV_JobLine1.ItemCode=specification.customer_item_code

ON (PV_Job1.CompNum=PV_JobLine1.CompNum
AND PV_Job1.PlantCode=PV_JobLine1.PlantCode)
AND PV_Job1.JobCode=PV_JobLine1.JobCode';

$Result = odbc_exec($conn1,$Query);

which throws an error. Other answers to this question have recommended using something like server1.database1.dbo.table1, but what would server1 and database1 be in this case? I'm pretty new to SQL and I've never done anything like this. I also tried creating a linked server which didn't work.

Answer Source

I don't see in your code where you use connection 2.

To make this simpler, let's focus on this code in your SQL Server. Open up SQL Server Management Studio and connect to your database.

To write this query, you need a linked server. You should be able to create this in Management Studio. You'll need a user on the db-2 database, and then you can create a linked server on db-1 that uses that user's credentials to login in. When you run a query over the linked server, it will use this user's credentials, so please make sure it has db_datareader permissions level on the linked database.

In your question, you state that it didn't work. We need the error you're seeing when you try to use the linked server.

server1.database1.dbo.table1 is a reference to a linked server. When you use the fourth part of the name, server1, you're referring to a connection to your linked server.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download