DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: Accessing tables from 2 different DB on SQL server

  1. #1
    John Guest

    Accessing tables from 2 different DB on SQL server


    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.

  2. #2
    Peter Merner Guest

    Re: Accessing tables from 2 different DB on SQL server


    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.



  3. #3
    Danny J. Lesandrini Guest

    Re: Accessing tables from 2 different DB on SQL server

    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.




  4. #4
    zac Guest

    Re: Accessing tables from 2 different DB on SQL server


    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.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center