How to make a single recordset with two recordsets?

[gvielliard]

Hi all,
I am new in ASP, my problem is that I get datas in two SQL servers, the store procedures are the same and they retrieve two recordsets which have exactly the same field names.
I need only one recordset because I use Crystal Report and it allows only one recordset.
I have already tried the GetRow method on the first recordset, then the AddNew method on the second with the array I had , but it doesn't work , I get this message:
"Object or provider is not capable of performing requested operation"
Actually what I really need is how to merge two recordsets into one.

Thank you for your help.
Gilles

 

[jamiec1]

You need to use a datashape connection string and proceedure.

 

[gvielliard]

Sorry but I don't have any idea of what could be a datashape connection string, where can I find informations?

 

[jonscott8]

AFAIK, data shaping is not the answer for this situation. It creates hierarchical recordsets, which is still multiple recordsets, not just one.

But since you mention you're using SQL Server, I'd harness it. If your data is in 2 seperate servers, setup a linked server from one to the other. If your data is in 2 seperate databases on the same server, you're already good to go.
Use a UNION join to combine the results.

If 2 seperate servers:
SELECT MyField FROM MyServerOne.MyDB.dbo.MyTable UNION SELECT MyField FROM MyServerTwo.MyDB.dbo.MyTable

If 1 server, 2 seperate databases:
SELECT MyField FROM MyDBOne.dbo.MyTable UNION ALL SELECT MyField FROM MyDBTwo.dbo.MyTable

FWIW, remember that the UNION join does not include duplicate rows unless you include the ALL clause with it. Jon Hawkins

 

[gvielliard]

Thank you Jon, but I wasn't clear in my first question, I know we can use the UNION with the SELECT statment, but my problem is that I use a very complicated storeproc with a lot of options and parameters (a real nightmare), and what I was looking for is something like a UNION but which works with EXEC statment, as I didn't find anything like this in SQL, I thought it was possible to do it in the ASP page.

In fact what I wanted to do is something like this

EXEC MyServerOne.MyDBOne..Storeproc(.....)
UNION
EXEC MyServerTwo.MyDBTwo..Storeproc(.....)

but of course it doesn't work!

 

[cbokowski]

Have you explored having the SPs build temp tables and then
doing a query from them?

 

[link9]

Also, you can open two recordsets in one by separating the SQL statements with a semicolon and then issuing the open command with that one long string.

dim sql, rs
set rs = server.createObject("ADODB.Recordset")
sql = "SELECT * FROM table1;SELECT * FROM table2"
rs.open sql, someConnectionObject

'Do something w/ first recordset
rs.nextRecordset
'Do something w/ second recordset

What you wind up with are two recordsets in one, where you use the first, and then issue the .nextRecordset command on the recordset, and that moves you to the second.

Problem is that you can't get back to the first.