Merging recordsets from two different DBs

I have an ASP (classic ASP) page which queries data from two databases
residing on the same SQL Server 2005 instance; it takes advantage of this by
using a query like this:

SELECT Something FROM DB1.dbo.SomeTable
UNION
SELECT SomethingElse FROM DB2.dbo.SomeOtherTable

The two tables have, of course, the same schema.

The two databases are being moved to different instances, so this syntax is
not going to work anymore. I could use an OPENROWSET statement or a database
link, but security issues are pressing toward opening two distinct
connections to the two servers and perform two queries.

The question: how can I merge the two RecordSets returned from the two
queries? They also need to be ordered by some specific columns, so just
appending rows from one to the other wouldn't be enough.

Sadly, rewriting the page in ASP.NET is not an option, so I'm stuck with
RecordSets instead of DataSets.


Massimo

Re: Merging recordsets from two different DBs

"Massimo" wrote in message
news:O0LoNF$GIHA.4228@TK2MSFTNGP02.phx.gbl...
>I have an ASP (classic ASP) page which queries data from two databases
>residing on the same SQL Server 2005 instance; it takes advantage of this
>by using a query like this:
>
> SELECT Something FROM DB1.dbo.SomeTable
> UNION
> SELECT SomethingElse FROM DB2.dbo.SomeOtherTable
>
> The two tables have, of course, the same schema.
>
> The two databases are being moved to different instances, so this syntax
> is not going to work anymore.

Oh yes it can. You just make sure 1st server is aware of the 2nd servers
existence.
Then if you connect to the 1st server you specify the remote server in the
SELECT.
Result: You still get the the same recordset.
Nearly all the databases I know have a way of registering a remote server.
See SQL Server 2005 Books Online

You want to run the system SP:

sp_addlinkedserver
Creates a linked server, which allows access to distributed, heterogeneous
queries against OLE DB data sources. After creating a linked server with
sp_addlinkedserver, this server can then execute distributed queries. If the
linked server is defined as Microsoft® SQL ServerT, remote stored procedures
can be executed.
Syntax
sp_addlinkedserver [ @server = ] 'server'
[ , [ @srvproduct = ] 'product_name' ]
[ , [ @provider = ] 'provider_name' ]
[ , [ @datasrc = ] 'data_source' ]
[ , [ @location = ] 'location' ]
[ , [ @provstr = ] 'provider_string' ]
[ , [ @catalog = ] 'catalog' ]

Then afterwards

SELECT Something FROM DB1.dbo.SomeTable
UNION
SELECT SomethingElse FROM remoteserver.DB2.dbo.SomeOtherTable

Stephen Howe

Re: Merging recordsets from two different DBs

Massimo wrote:
> I have an ASP (classic ASP) page which queries data from two databases
> residing on the same SQL Server 2005 instance; it takes advantage of
> this by using a query like this:
>
> SELECT Something FROM DB1.dbo.SomeTable
> UNION
> SELECT SomethingElse FROM DB2.dbo.SomeOtherTable
>
> The two tables have, of course, the same schema.
>
> The two databases are being moved to different instances, so this
> syntax is not going to work anymore. I could use an OPENROWSET
> statement or a database link, but security issues are pressing toward
> opening two distinct connections to the two servers and perform two
> queries.
>
> The question: how can I merge the two RecordSets returned from the two
> queries? They also need to be ordered by some specific columns, so
> just appending rows from one to the other wouldn't be enough.

Why not? Just open a recordset on the first database, disconnect it,
open another recordset on the second database, loop through it to add
its data into the disconnected recordset, close the second records, and
use the disconnected recordset's Sort method to sort it.

Seems easier to use a linked server, but you say that's not allowed ...
What's the security issue with this approach?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Merging recordsets from two different DBs

> ...but security issues are pressing toward opening two distinct
> connections to the two servers and perform two queries.

Oh sorry I did not see that.
I go along with Bob's approach

Stephen Howe

Re: Merging recordsets from two different DBs

"Stephen Howe" ha scritto nel
messaggio news:ugEpWX$GIHA.5980@TK2MSFTNGP04.phx.gbl...

> Nearly all the databases I know have a way of registering a remote
> server.
> See SQL Server 2005 Books Online
>
> You want to run the system SP:
>
> sp_addlinkedserver

I'm well aware of this :-)
But, as I said (maybe you missed it), we don't want to establish connection
between the *servers*, due to possible security issues and also to the fact
that we just don't have administrative rights on them.


Massimo

Re: Merging recordsets from two different DBs

"Bob Barrows [MVP]" ha scritto nel messaggio
news:eDNwWX$GIHA.6044@TK2MSFTNGP04.phx.gbl...

> Why not? Just open a recordset on the first database, disconnect it,
> open another recordset on the second database, loop through it to add
> its data into the disconnected recordset, close the second records, and
> use the disconnected recordset's Sort method to sort it.

I thought about this, but just wanted to know if there was something easier.

> Seems easier to use a linked server, but you say that's not allowed ...
> What's the security issue with this approach?

We don't have administrative rights on those servers, we don't want to
establish permanent connections between them, and we also don't want to give
away login informations.


Massimo

Re: Merging recordsets from two different DBs

Massimo wrote:
> "Bob Barrows [MVP]" ha scritto nel
> messaggio news:eDNwWX$GIHA.6044@TK2MSFTNGP04.phx.gbl...
>
>> Why not? Just open a recordset on the first database, disconnect it,
>> open another recordset on the second database, loop through it to add
>> its data into the disconnected recordset, close the second records,
>> and use the disconnected recordset's Sort method to sort it.
>
> I thought about this, but just wanted to know if there was something
> easier.

Something Mark McGinty suggested the other day could be a possibility.
Persist the two recordsets to xml documents. Then use selectNodes to
create a collection of the rs:data nodes in one of the documents, loop
through the collection and use appendChild to append them to the other
xml document. Then use rs.Open xmldoc to open a recordset on the merged
xml document. I've never done this but I see no reason it would not
work.

>
>> Seems easier to use a linked server, but you say that's not allowed
>> ... What's the security issue with this approach?
>
> We don't have administrative rights on those servers, we don't want to
> establish permanent connections between them, and we also don't want
> to give away login informations.
>
Given that you are using those login informations in your application,
this seems somewhat overzealous to me. but so be it.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Merging recordsets from two different DBs

"Bob Barrows [MVP]" wrote in message
news:OsJDHNAHIHA.4296@TK2MSFTNGP04.phx.gbl...
> Massimo wrote:
>> "Bob Barrows [MVP]" ha scritto nel
>> messaggio news:eDNwWX$GIHA.6044@TK2MSFTNGP04.phx.gbl...
>>
>>> Why not? Just open a recordset on the first database, disconnect it,
>>> open another recordset on the second database, loop through it to add
>>> its data into the disconnected recordset, close the second records,
>>> and use the disconnected recordset's Sort method to sort it.
>>
>> I thought about this, but just wanted to know if there was something
>> easier.
>
> Something Mark McGinty suggested the other day could be a possibility.
> Persist the two recordsets to xml documents. Then use selectNodes to
> create a collection of the rs:data nodes in one of the documents, loop
> through the collection and use appendChild to append them to the other
> xml document. Then use rs.Open xmldoc to open a recordset on the merged
> xml document. I've never done this but I see no reason it would not
> work.


It will work, within a few limits. This function assumes both recordsets
are
based on identical schema... hmm, actually, as long as rsSrc's field set is
a perfect subset of rsDest's field set, it will work, given that the fields
in rsDest only will tolerate null values, and provided both use the same
key field. Further, the key field values must be unique across the whole
destination recordset. ADO takes the integrity of the key pretty much on
faith; if/when that integrity is violated, the recordset will either fail
to open,
or shortly thereafter. If there is a chance of key value crossover between
the two sets, you can still add the rows as if inserted, provided you
remove
the key field attribute from each source row node before appending.
(See notes (* 1) and (* 2) if that is the case.)

-Mark


''''''''''''''''''''
Public Sub ConcatRecordsets( _
ByRef rsDest As ADODB.Recordset,
ByRef rsSrc As ADODB.Recordset
)

Dim SrcXml As MSXML.DOMDocument
Dim DestXml As MSXML.DOMDocument
Set SrcXml = New MSXML.DOMDocument
Set DestXml = New MSXML.DOMDocument

rsDest.Save DestXml, adPersistXML
rsSrc.Save SrcXml, adPersistXML

Dim DestInsNode As MSXML.IXMLDOMNode
Dim SrcRowNode As MSXML.IXMLDOMNode
Dim SrcRowNodes As MSXML.IXMLDOMNodeList

DestXml.async = False

Set DestInsNode = DestXml.selectSingleNode("xml/rs:data")
'''' (* 1)
Set SrcRowNodes = SrcXml.selectNodes("xml/rs:data/z:row")

If Not SrcRowNodes Is Nothing Then
For Each SrcRowNode In SrcRowNodes
'''' (* 2)

DoEvents
DestInsNode.appendChild _
SrcRowNode.parentNode.removeChild(SrcRowNode)
Next
End If

rsDest.Close
Set rsDest.ActiveConnection = Nothing

rsDest.Open DestXml

End Sub



'' (* 1) To add the rows as if ther were inserted, use the
'' following to assign DestInsNode, in place of the
'' one-line assignment in the function.
''
Dim DestDataNode As MSXML.IXMLDOMNode
Dim TmpNode As MSXML.IXMLDOMNode
Set DestDataNode = DestXml.selectSingleNode("xml/rs:data")
Set TmpNode = DestXml.createNode(NODE_ELEMENT, _
"rs:insert", DestDataNode.namespaceURI)
Set DestInsNode = DestDataNode.appendChild(TmpNode)


'' (* 2) The line below will remove a field value
'' attribute from a row node. ("KeyFieldName"
'' is, of course, just a place holder for the
'' field name.
''
SrcRowNode.Attributes.removeNamedItem "KeyFieldName"














>>> Seems easier to use a linked server, but you say that's not allowed
>>> ... What's the security issue with this approach?
>>
>> We don't have administrative rights on those servers, we don't want to
>> establish permanent connections between them, and we also don't want
>> to give away login informations.
>>
> Given that you are using those login informations in your application,
> this seems somewhat overzealous to me. but so be it.
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: Merging recordsets from two different DBs

"Mark J. McGinty" ha scritto nel messaggio
news:u0pCDhqHIHA.2268@TK2MSFTNGP02.phx.gbl...

> It will work, within a few limits. This function assumes
> both recordsets are based on identical schema...

They actually are; as I stated, what I need to do is to replace a "SELECT
SomeThing FROM DB1.Table1 UNION SELECT SomeThingElse FROM DB2.Table2" with
two distinct connections, two distinct queries, two distinct resulting
RecordSets and a merge operation.

Thanks for the code, but is this whole XML thing really needed? Can't
something simpler like "append each row from rs2 to rs1 and then sort rs1"
be used?


Massimo

Re: Merging recordsets from two different DBs

Massimo wrote:
> "Mark J. McGinty" ha scritto nel messaggio
> news:u0pCDhqHIHA.2268@TK2MSFTNGP02.phx.gbl...
>
>> It will work, within a few limits. This function assumes
>> both recordsets are based on identical schema...
>
> They actually are; as I stated, what I need to do is to replace a
> "SELECT SomeThing FROM DB1.Table1 UNION SELECT SomeThingElse FROM
> DB2.Table2" with two distinct connections, two distinct queries, two
> distinct resulting RecordSets and a merge operation.
>
> Thanks for the code, but is this whole XML thing really needed?

No. You can open two disconnected recordsets, loop through one appending its
data to the other and call the merged recordset's Sort property ... as
stated in my intitial response.

> Can't
> something simpler like "append each row from rs2 to rs1 and then sort
> rs1" be used?
>

See above ... that's basically the solution I suggested.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Merging recordsets from two different DBs

Bob Barrows [MVP] wrote:
> No. You can open two disconnected recordsets, loop through one
> appending its data to the other and call the merged recordset's Sort
> property

Of course, this should more properly be stated: " ... _set_ the merged
recordset's Sort property."
>
>> Can't
>> something simpler like "append each row from rs2 to rs1 and then sort
>> rs1" be used?
>>
Just to expand:
IF an ADODB Recordset object had a Rows or Records collection to access the
records contained in it., then this would be possible. Unfortunately for
you, this is not the case: the ADO Cursor Library provides a _cursor_ to
allow access to the records. I assume this was done to allow a single object
to handle both client-side and server-side cursors. In ADO,Net, they broke
with this paradigm by creating DataSets. Note that datasets do not provide
cursor functionality (movefirst, movenext, etc) - they are true .Net objects
with collections, etc..

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Merging recordsets from two different DBs

"Massimo" wrote in message
news:uPDWoTvHIHA.284@TK2MSFTNGP02.phx.gbl...
> "Mark J. McGinty" ha scritto nel messaggio
> news:u0pCDhqHIHA.2268@TK2MSFTNGP02.phx.gbl...
>
>> It will work, within a few limits. This function assumes
>> both recordsets are based on identical schema...
>
> They actually are; as I stated, what I need to do is to replace a "SELECT
> SomeThing FROM DB1.Table1 UNION SELECT SomeThingElse FROM DB2.Table2" with
> two distinct connections, two distinct queries, two distinct resulting
> RecordSets and a merge operation.
>
> Thanks for the code, but is this whole XML thing really needed? Can't
> something simpler like "append each row from rs2 to rs1 and then sort rs1"
> be used?

That depends on how many columns and rows you are dealing with. Doing it by
appending a recordset means a separate assignment for each field, which
includes validating data type compatibility for each assignment. It must
then validate any nullness constraints when it inserts the row. The XML way
only has to handle one node per row, without any validation overhead (other
than any implemented by you.

If the number of columns * rows is appreciable, or you will have to do this
with any frequency, the performance you'd stand to gain could be
substantial.


-Mark


> Massimo
>

Re: Merging recordsets from two different DBs

"Bob Barrows [MVP]" ha scritto nel messaggio
news:%232cilkvHIHA.3940@TK2MSFTNGP05.phx.gbl...

>> Thanks for the code, but is this whole XML thing really needed?
>
> No. You can open two disconnected recordsets, loop through one
> appending its data to the other and call the merged recordset's Sort
> property ... as stated in my intitial response.

That's good. Could you please show me some sample code to do this?
I'm trying to copy data from one RecordSet to another, but I can't create
and open the new one, since it says it wants an active database
connection...

Unfortunately I'm *not* a VB programmer, and I'm stuck with fixing this ASP
application.

I can get two RecordSet filled with data from the two databases, but I don't
actually know how to create a new, empty one and copy data from the first
two ones to it.

Can it reale be *so* difficult?!?

Thanks for any help


Massimo

Re: Merging recordsets from two different DBs

Massimo wrote:
> "Bob Barrows [MVP]" ha scritto nel
> messaggio news:%232cilkvHIHA.3940@TK2MSFTNGP05.phx.gbl...
>
>>> Thanks for the code, but is this whole XML thing really needed?
>>
>> No. You can open two disconnected recordsets, loop through one
>> appending its data to the other and call the merged recordset's Sort
>> property ... as stated in my intitial response.
>
> That's good. Could you please show me some sample code to do this?
> I'm trying to copy data from one RecordSet to another, but I can't
> create and open the new one, since it says it wants an active database
> connection...
>
> Unfortunately I'm *not* a VB programmer, and I'm stuck with fixing
> this ASP application.
>
> I can get two RecordSet filled with data from the two databases, but
> I don't actually know how to create a new, empty one and copy data
> from the first two ones to it.
>
> Can it reale be *so* difficult?!?
>
You're making it more difficult than it needs to be. You don't need a third
recordset.
I don't have any code because I've never had to do this. However, keep in
mind that this is untested "air" code:

Open the two connections:
set conn1 = createobject("adodb.connection")
conn1.open
set conn2 = createobject("adodb.connection")
conn2.open

Open the first recordset, making sure it's a client-side recordset:

set rs1=createobject("adodb.recordset")
rs1.cursorlocation = 3 'adUseClient
rs1.open sqlstatement, conn1,,,1 '1=adCmdText

Set its ActiveConnection property to Nothing (this disconnects it)

set rs1.ActiveConnection = nothing
conn1.close: set conn1 = nothing

Open the second recordset (this one can be a default cursortype):

set rs2=conn2.execute(sqlstatement2,,1)

loop through rs2, adding its records to rs1:

Do until rs2.EOF
rs1.AddNew
for i = 0 to rs1.Fields.Count - 1
rs1(i).value = rs2(i).value
Next
rs1.Update
'since the recordset is disconnected this Update statement
'has no effect on data in the database
rs2.MoveNext
Loop
rs2.close: set rs2=nothing
conn2.close:set conn2 = nothing

Do what you need to do with rs1

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Merging recordsets from two different DBs

"Bob Barrows [MVP]" ha scritto nel messaggio
news:OF%23svM6HIHA.3400@TK2MSFTNGP03.phx.gbl...

> I don't have any code because I've never had to do this.
> However, keep in mind that this is untested "air" code:

Ok, thanks for the code, I'll test it.

What I was having problem with was exactly how to use and manage
connected/disconnected RecordSets.


Massimo

Re: Merging recordsets from two different DBs

"Bob Barrows [MVP]" ha scritto nel messaggio
news:OF%23svM6HIHA.3400@TK2MSFTNGP03.phx.gbl...

I tested it, but on this line

>rs1.AddNew

I get a 800a0cb3 error, complaining the current RecordSet doesn't support
updates.


Massimo

Re: Merging recordsets from two different DBs

>>rs1.AddNew
>
> I get a 800a0cb3 error, complaining the current RecordSet doesn't support
> updates.

Did you open the recordset with a lock type other than ReadOnly?

Stephen Howe

Re: Merging recordsets from two different DBs

Massimo wrote:
> "Bob Barrows [MVP]" ha scritto nel
> messaggio news:OF%23svM6HIHA.3400@TK2MSFTNGP03.phx.gbl...
>
> I tested it, but on this line
>
>> rs1.AddNew
>
> I get a 800a0cb3 error, complaining the current RecordSet doesn't
> support updates.
>
>
Without seeing your code, I have to take it on faith that you've
followed my example. Let's try this. Instead of:

rs1.open sqlstatement, conn1,,,1 '1=adCmdText

try this:

rs1.open sqlstatement, conn1,,4,1
'4=adLockBatchOptimistic 1=adCmdText

If that does not solve the issue, then you will need to show me how to
reproduce the issue. Are there IDENTITY columns involved?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Merging recordsets from two different DBs

"Stephen Howe" ha scritto nel
messaggio news:OgSaaY7HIHA.6068@TK2MSFTNGP05.phx.gbl...

>>>rs1.AddNew
>>
>> I get a 800a0cb3 error, complaining the current RecordSet
>> doesn't support updates.
>
> Did you open the recordset with a lock type other than ReadOnly?

I don't even know how to do it... as I said, I'm quite out of my knowledge
here.
I'll give it a try tomorrow, thanks anyway.


Massimo

Re: Merging recordsets from two different DBs

using OpenDataSource() would be a good way to connect to the remote DB server