If the databases are on the same server, use three part names, like:
FROM thisdatabase.dbo.thisTable AS a
FULL OUTER JOIN thatdatabase.dbo.otherTable AS b
If the databases are on different servers, then you need to start making some choices. If you are allowed to make network decisions, and are sysadmin on both servers, then "introduce" them to each other (the exact steps can vary considerably). If you are not, you need to get someone to do that for you. Once the introductions have been made, you can just use four part names and go merrily on your way, something like:
FROM dbo.localTable AS a
LEFT JOIN otherServer.theirDb.dbo.thingamabob AS b
ON (b.foo = a.bar)
Hi Pat & Everyone,
I have a similar case. I have Pervasive SQL database on ServerP and MS SQL Server 2000 on ServerM. I've created a dsn on ServerM for Pervasive SQL (I can access the data on ServM), linked the Pervasive SQL database to MS SQL Server using Linked server. A table on SQL Server and a table on Pervasive SQL Server have common information. There is one matching field in both the tables. now my task is to create a RecordSet from both the tables using the matching field. I tried some queries but getting errors. Please see what am I doing wrong. The following are some of the querries I've tried:
1)This code is just to get data from the linkedserver, not even from both the tables:
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = EMMS; User Id = sa; Password=sqladmin"
Set MyRec = Server.createobject("ADODB.Recordset")
MyRec.Open "SELECT * FROM LinkServer.DBCLN.dbo.cdtbl1",MyConn
For the above code, I get the following error in the page:
Microsoft OLE DB Provider for SQL Server error '80040e14'
OLE DB error trace [Non-interface error].
/emms/dbpervasive.asp, line 7
2) I found this query on the Internet, but I don't know if I have to use the code this way or I don't need to use this way. I mean as I already have linkedserver, it means I am not retrieving data from two different databases, hence the confusion. however my final task is to retrive data from both the tables(one from SQL server and one from LinkedServer). Please guide me.
SELECT emp.EmloyeeID, ord.OrderID, ord.Discount FROM SQLServer1.Northwind.dbo.Employees AS emp, OracleSvr.Catalog1.SchemaX.Orders AS ord WHERE ord.EmployeeID = emp.EmployeeID AND ord.Discount > 0
In the above query, instead of Oracle, I need to use dsn(which is created for Pervasive SQL). Any ideas.