Accessing tables from 2 different DB on SQL server

[John]

Hi all. I am trying to access 2 tables from the same server (SQL Server 7.0)
but in different databases. I am trying to find the records in one table
that do not exist in the other. I am currently using ADO. But cannot figure
out how to compare the returned recordsets to see which records are NOT in
one set that exist in the other. CAn anyone please help. I am failry new
to VB and SQL Server although I know SQL and can write the SQL statements
that will perfrom this as a view or stored procedure.

[Peter Merner]

John, I too am new to all this and happen to have a similar problem which
you can see in the posts just behind your thread. One of the suggestions
posted to me would make sense for you too. What you want to do can be done
with a stored procedure and you can access that stored procedure with ADO.
Just create a connection object to the SQL Server having the stored proc
and then execute syntax like that below.

rs.open yourstoredproc,yourconnection,adcursortype, adlocktype _
adCmdStoredProc

If you wish to execute an action query you would use a command rather than
a recordset object. The syntax is similar but not identical. Just type mycmd.execute
into the IDE, highlight the word execute and press F1 to get the correct
syntax.

Hope this helps.

"John" <johnbscorpio@hotmail.com> wrote:
>
>Hi all. I am trying to access 2 tables from the same server (SQL Server
7.0)
>but in different databases. I am trying to find the records in one table
>that do not exist in the other. I am currently using ADO. But cannot figure
>out how to compare the returned recordsets to see which records are NOT
in
> one set that exist in the other. CAn anyone please help. I am failry
new
>to VB and SQL Server although I know SQL and can write the SQL statements
>that will perfrom this as a view or stored procedure.

[Danny J. Lesandrini]

You need to fully qualify the table names.

tblCustomer isn't really tblCustomer. It's really
Database.Owner.table

So, you could write SQL like this ...

SELECT Or.*, St.*
FROM DB1.dbo.tblOrder As Or
INNER JOIN DB2.dbo.tblStatus As St
ON Or.StatusID=St.StatusID

--
Danny Lesandrini
www.dea.com/datafast/downloads.asp
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Outlook Appointments, ASP and vCalendar
Problematic or Programmatic ODBC
www.swynk.com/friends/Lesandrini/
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -


"John" <johnbscorpio@hotmail.com> wrote in message news:39edbb70$1@news.devx.com...
>
> Hi all. I am trying to access 2 tables from the same server (SQL Server 7.0)
> but in different databases. I am trying to find the records in one table
> that do not exist in the other. I am currently using ADO. But cannot figure
> out how to compare the returned recordsets to see which records are NOT in
> one set that exist in the other. CAn anyone please help. I am failry new
> to VB and SQL Server although I know SQL and can write the SQL statements
> that will perfrom this as a view or stored procedure.

[zac]

John

I am writing something similiar here is what I had to do.

My Problem: Find the address that match in rsData1 & rsData2

Solution:

Once you have the recordsets created you will have to do a comparison on
a record vs record basis. I am doing my comparison on the associated primary
keys. If you do not have associated primary keys then you are going to have
to select several fields from the tables to compare.


with rsData1
.movefirst 'moves to the first record in rsData1

do while not .eof
with rsdata2

compare the primarykeyfield in rsdata1 with the associated field
in the second recordset or rsdata2

if ( rsdata1!primarykeyfield1 = rsdata2!primarykeyfield1) and
( rsdata1!primarykeyfield2 = rsdata2!primarykeyfield2) then

'note rsdata3 is a dump table to insert match data
with rsdata3
.addnew
rsdata3!dumpfield1 = rsdata1!primarykeyfiled1
etc..........
.update
end with
.movenext
end with
.movenext
loop

end with



Zachary - i hope this helps









"John" <johnbscorpio@hotmail.com> wrote:
>
>Hi all. I am trying to access 2 tables from the same server (SQL Server
7.0)
>but in different databases. I am trying to find the records in one table
>that do not exist in the other. I am currently using ADO. But cannot figure
>out how to compare the returned recordsets to see which records are NOT
in
> one set that exist in the other. CAn anyone please help. I am failry
new
>to VB and SQL Server although I know SQL and can write the SQL statements
>that will perfrom this as a view or stored procedure.