Join tables from two source

Generally it is not possible to join databases using SQL. However, MSSQL provides a functionality called OPENROWSET that can actually get data from another source.

Some experts even do a inner join with it. However, when I tried using inner join, our sql server keeps giving me timeout error. Creating a view using the functionality is a success though.

I guess we can then join it to other tables or views to get the final result.

The following is how to get it working.

First we must configure the sql server to allow "AD Hoc Distributed Queries". This need to be done only once per server.

Go to sql management studio and navigate to the database in where you have your original data. Go to programmability, stored procedures. Create a new stored procedure but delete everything from the template. Copy and past the following in it.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Click on Execute from the window\'s menu. You should see some message appearing. Unless it is an error message, you have just finished doing the configuration. You don\'t have to save the stored procedure as you do not need to run it more than once.

Next go to views in the database and create a new view. Type your "select" statement as normal but change the table name after the "From" to something like below

OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=sgsvcit01;UID=sa;PWD=MyPass', wfm.dbo.wfm_case) AS wfm

Save the query and it will be done.wfm.dbo.wfm_case is the sample database. It means we are using wfm database and gets the wfm_case table.

You have to provide an alias otherwise sql server will make noise then provide one for you instead.For obvious reasons the UID and PWD is a dummy value.

You should use the actual UID and PWD.If you are geting data from msAccess table then the openrowset setting will be different. See below example.

OPENROWSET('Microsoft.Jet.OLEDB.4.0', c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) AS o

All these information were gathered from internet.