inetserver asp db: [quoted text, click to view]

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.

[quoted text, click to view]

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.

[quoted text, click to view]
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.

[quoted text, click to view]

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

[quoted text, click to view]

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

Stephen Howe

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

"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> ha scritto nel
messaggio news:ugEpWX$GIHA.5980@TK2MSFTNGP04.phx.gbl...

[quoted text, click to view]

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

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

[quoted text, click to view]

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

[quoted text, click to view]

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