How to execute SQL that queries multiple databases using rdo

How to execute SQL that queries multiple databases using rdo

Post by hhunsake » Fri, 02 Aug 2002 04:15:14



Hello.

I am a VB novice and am trying to modify a program to
exexcute a SQL statement that uses two databases.
It uses a single bulk SQL INSERT statement to insert data
into a table in one database that are read from a table in
another database. Is this possible in VB? RDO?
If so, is it possible to execute a single SQL statement
that access two databases on different servers?
Is there a good documentation source for this topic?
Any help is appreciated.

Thanks.

 
 
 

How to execute SQL that queries multiple databases using rdo

Post by Leo Walte » Sat, 03 Aug 2002 01:53:32


As far as writing a query that will insert data from one table in a database
into a table in another database, that is easy....

Insert into database2..table(field1, field2)
Select database1..table.field1, database1..table.field2
from database1..table

If you need to use data from two different servers, you will need to create
a connection and a recordset for the source server, then create a separate
connection and a command object for the destination server.  Cycle through
each record in the source recordset appending data to the destination server
using the command object.

If this is being done completely between SQL Servers, the you can create
Linked or Remote servers and reference them within SQL

Hope this helps....


Quote:> Hello.

> I am a VB novice and am trying to modify a program to
> exexcute a SQL statement that uses two databases.
> It uses a single bulk SQL INSERT statement to insert data
> into a table in one database that are read from a table in
> another database. Is this possible in VB? RDO?
> If so, is it possible to execute a single SQL statement
> that access two databases on different servers?
> Is there a good documentation source for this topic?
> Any help is appreciated.

> Thanks.


 
 
 

How to execute SQL that queries multiple databases using rdo

Post by Matt » Sat, 03 Aug 2002 23:50:24


The best way is to create Linked Server and connect
between two servers using that Link.

For example, if you want to Get info from database B and
insert them into database A, create a linked server in
database A that connects to database B and perform your
query in database A like this:

Insert into database1.dbo.table(field1, field2)
Select field1, field2
from [YourLinkedServer].database2.dbo.table

it will work perfect. BTY, it supports all transaction
that you create over two servers. I mean if any thing
happens during transaction, it rolls back all
transactions, IF you are using TRANSACTION in your code or
Stored Procedures.

Good luck.

Matt.

Quote:>-----Original Message-----
>As far as writing a query that will insert data from one
table in a database
>into a table in another database, that is easy....

>Insert into database2..table(field1, field2)
>Select database1..table.field1, database1..table.field2
>from database1..table

>If you need to use data from two different servers, you
will need to create
>a connection and a recordset for the source server, then
create a separate
>connection and a command object for the destination

server.  Cycle through
>each record in the source recordset appending data to the
destination server
>using the command object.

>If this is being done completely between SQL Servers, the
you can create
>Linked or Remote servers and reference them within SQL

>Hope this helps....



>> Hello.

>> I am a VB novice and am trying to modify a program to
>> exexcute a SQL statement that uses two databases.
>> It uses a single bulk SQL INSERT statement to insert
data
>> into a table in one database that are read from a table
in
>> another database. Is this possible in VB? RDO?
>> If so, is it possible to execute a single SQL statement
>> that access two databases on different servers?
>> Is there a good documentation source for this topic?
>> Any help is appreciated.

>> Thanks.

>.

 
 
 

1. Executing Stored procedure with multiple select statement using RDO

We have the following problem.

Assume a stored procedure with the following statements



if we open a resultset through VB/RDO with the SP name as the execute
string, with rdoOpenKeySet, we get the following error.

Cannot open the cursor for a stored procedure which has more than one
select statement.

Is there any mistake in our result set setting ie type or is it nay other
problem. We do not get this error if we use the ODBC Driver that ships
with SQL Server 4.2 ieODBC Driver v2.0 or earlier.

Any help in this will be appreciated

Prakash

2. SQL Anywhere 5.5 vs. Sybase System 10/11

3. Executing Access 7.0 action queries from VB4 using RDO

4. Jaz drives & CDRW

5. Multiple Resultsets using SQL Server and RDO?

6. Using type 'LONG' with Oracle - How???

7. Query performance degrades when multiple connections execute the same query

8. Defining An Installation Password

9. Executing multiple SQL queries

10. Howto execute one query over multiple query results

11. ADO problem with multiple SQL statements using execute method

12. Multiple database - multiple query - multiple server

13. ADO problem with multiple SQL statements using execute method