?
Solved

Run SQL query on different servers WITHOUT linked servers

Posted on 2009-02-20
15
Medium Priority
?
526 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:MDauphinais1
  • 7
  • 5
  • 3
15 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 23695646
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
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23695795
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

0
 
LVL 39

Assisted Solution

by:BrandonGalderisi
BrandonGalderisi earned 2000 total points
ID: 23695866
The other thing you can try to do is merge the recordsets in ASP.

The syntax should be something like... (pseudocode)

set rs1=server.creatobject("adodb.recordset")
set rs2=server.creatobject("adodb.recordset")
set c1 = server.creatobject("adodb.connection")
set c2 = server.creatobject("adodb.connection")
c1.connectionstring="SERVER1....."
c2.connectionstring="SERVER2....."
 
c1.open
rs1.open "SELECT field1,field2 FROM SomeTable",c1
rs2.open "SELECT field1,field2 FROM SomeOtherTable",c2
 
if not rs1.eof and not rs1.bof then
 
  if not rs2.eof and not rs2.bof then
    rs2.movefirst
    while not rs2.eof
     rs1.addrow
    rs2.movenext
    wend
  end if
 
end if

Open in new window

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:MDauphinais1
ID: 23695918
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
)
0
 
LVL 25

Expert Comment

by:reb73
ID: 23695973
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 ..
0
 

Author Comment

by:MDauphinais1
ID: 23696148
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?
0
 
LVL 25

Expert Comment

by:reb73
ID: 23696212
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?
0
 

Author Comment

by:MDauphinais1
ID: 23696222
Yes, both SQL 2005
0
 

Author Comment

by:MDauphinais1
ID: 23696398
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?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 23696447
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.
0
 

Author Comment

by:MDauphinais1
ID: 23696553
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



0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 2000 total points
ID: 23698118
Here's a little sample that I threw together.  It connects to my SQL Server (192.168.1.3\sqlexpress) and gets the top 10 records each from master and msdb information_schema.tables.

It then merge them into a new recordset (one row at a time :( ) and sorts on table name.

At the end, it will show you that msdb and master tables are sorted together by table name.  

This won't be good for LARGE data sets as it adds to the interim recordset RBAR.  But it will work if you are UNABLE to get a linked server.
Dim rs1
Dim rs2
Dim rsM
Dim MSG
Set rs1 = CreateObject("adodb.recordset")
Set rs2 = CreateObject("adodb.recordset")
Set rsM = CreateObject("adodb.recordset")
Set conn1 = CreateObject("adodb.connection")
Set conn2 = CreateObject("adodb.connection")
 
'conn1.connectionstring = "Provider=SQLNCLI;Data Source=xeon;;Initial Catalog=master;Integrated Security=SSPI"
 
conn1.connectionstring = "Provider=sqloledb;Data Source=192.168.1.3\sqlexpress;Initial Catalog=master;Integrated Security=SSPI;"
conn2.connectionstring = "Provider=sqloledb;Data Source=192.168.1.3\sqlexpress;Initial Catalog=msdb;Integrated Security=SSPI;"
conn1.connectiontimeout=1
conn2.connectiontimeout=1
conn1.open
conn2.open
 
 
rs1.open "select top 10 table_catalog,table_name from information_schema.tables",conn1
rs2.open "select top 10 table_catalog,table_name from information_schema.tables",conn2
 
rsM.fields.append "table_catalog", 200,255
rsM.fields.append "table_name", 200,255
rsm.open
If Not rs1.eof And Not rs1.eof Then
     rs1.movefirst
     While Not rs1.eof
     rsm.addnew
     rsm("table_catalog") = rs1("table_catalog")
     rsm("table_name") = rs1("table_name")
     rsm.update
     rs1.movenext
     Wend
End If
 
If Not rs2.eof And Not rs2.eof Then
     rs2.movefirst
     While Not rs2.eof
     rsm.addnew
     rsm("table_catalog") = rs2("table_catalog")
     rsm("table_name") = rs2("table_name")
     rsm.update
     rs2.movenext
     Wend
End If
 
rsm.sort = "table_name"
 
rsm.movefirst
MSG=""
While Not rsm.eof
MSG =MSG & "Database=" & rsm("table_catalog") & "; Table=" & rsm("table_name") & vbcrlf
rsm.movenext
Wend
 
MsgBox msg
conn1.close
conn2.close
rsm.close
Set rs1 = Nothing
Set rs2 = Nothing
Set rsM=Nothing
Set conn1 = Nothing
Set conn2 = Nothing

Open in new window

0
 

Author Comment

by:MDauphinais1
ID: 23700637
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.
0
 
LVL 39

Expert Comment

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

Author Comment

by:MDauphinais1
ID: 23703273
"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.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question