asked on

Run SQL query on different servers WITHOUT linked servers

OK, I have two different databases, on two different SQL servers. I am not allowed to link the servers on the backend because my company feels it is a security vulnerability.

What can I do (even if it is not a glammerous method) to ultimately run a SELECT query displaying results with the data from both of these tables combined in ASP like you can in MS Access?

The current user does have authentication rights on both servers and databases.

reb73

You should be able to use the OPENDATASOURCE command like -

SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName1;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories

UNION ALL

SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName2;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories

BrandonGalderisi

Actually one modification would be that since you are running against server1, you only need to opendatasource server2

SELECT   *
FROM Northwind.dbo.Categories
 
UNION ALL
 
SELECT   *
FROM      OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=ServerName2;User ID=MyUID;Password=MyPass'
         ).Northwind.dbo.Categories

Open in new window

SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MDauphinais1

ASKER

How do I implement the OPENDATASOURCE code? Does it go into a query in the SQL server or do I put it in the ASP page? If I put it into the ASP page... how to I "open" the recordset with it like I would with a regular SQL connection?

i.e.: rs.open(SELECT *
FROM Northwind.dbo.Categories

UNION ALL

SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName2;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories
)

reb73

Create a procedure on one server and call this procedure from asp..

Merging recordsets in asp (using Brandon's template) can be easier assuming your asp page does not require to handle a significant number of records ..

MDauphinais1

ASKER

ok, when I tried running the procedure on the server itself (not calling it from ASP yet), I get this error:

Cannot create an instance of OLE DB provider "SQLNCLI" for linked server "(null)".

Since linked servers are not allowed, is this method not going to work?

reb73

Since linked servers are not allowed, is this method not going to work?

I presume it is a policy as opposed to a restriction.. Are both servers SQL 2005?

MDauphinais1

ASKER

Yes, both SQL 2005

MDauphinais1

ASKER

With Brandon's suggestion, I get this error:

Object doesn't support this property or method: 'addrow'

At what point do I actually "print" (i.e.: response.write or something) the results in this?

BrandonGalderisi

Do you need to sort the data on the page in an order that could mingle the records? If not, then you don't even need to merge the records. And I don't know the exact syntax for adding a row. Like I said, it was PSEUDO CODE to outline HOW the logic should work.

MDauphinais1

ASKER

hmm... ok, I got it to print the results. But yes, I would need to be able to sort them like... sort alphabetically, by date, whatever.

Right now it is printing all records from table 1, then all records from table 2. So I'm not really sure how to do that.

This is what I did to get it to work:

if not rs5.eof and not rs5.bof then

      while not rs5.eof

      response.write rs5("BookNo") & "<br> Table 1"

      rs5.movenext
      wend

End If

if not rs6.eof and not rs6.bof then

      while not rs6.eof

      response.write rs6("BookNo") & "<br> Table 2"

      rs6.movenext
      wend

End If

ASKER CERTIFIED SOLUTION

BrandonGalderisi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MDauphinais1

ASKER

Brandon I appreciate your help. I eneded up going the recordset route although the time it takes stinks due to the amount of records being processed. But at least it gets the job done.

BrandonGalderisi

Glad I can help. Although there isn't really a risk involved with using linked servers so long as they are properly managed.

MDauphinais1

ASKER

"Although there isn't really a risk involved with using linked servers so long as they are properly managed."

Ohh... I know... I know. Our IT folks have lost it. And this is only the beginning. I've heard of their upcoming ideas and I'm scared.