Results 1 to 4 of 4

Thread: 2 databases

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: 2 databases

    How can you make two databases communicate with each other?? they both reside in sql server 2000. How would I go about doing that. Access 2000 is the interface

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If the databases are on the same server, use three part names, like:
    Code:
    SELECT *
       FROM thisdatabase.dbo.thisTable AS a
       FULL OUTER JOIN thatdatabase.dbo.otherTable AS b
    If the databases are on different servers, then you need to start making some choices. If you are allowed to make network decisions, and are sysadmin on both servers, then "introduce" them to each other (the exact steps can vary considerably). If you are not, you need to get someone to do that for you. Once the introductions have been made, you can just use four part names and go merrily on your way, something like:
    Code:
    SELECT *
       FROM dbo.localTable AS a
       LEFT JOIN otherServer.theirDb.dbo.thingamabob AS b
         ON (b.foo = a.bar)
    -PatP

  3. #3
    Join Date
    Aug 2002
    Location
    Beaumont, TX
    Posts
    18

    Similar Case

    Hi Pat & Everyone,
    I have a similar case. I have Pervasive SQL database on ServerP and MS SQL Server 2000 on ServerM. I've created a dsn on ServerM for Pervasive SQL (I can access the data on ServM), linked the Pervasive SQL database to MS SQL Server using Linked server. A table on SQL Server and a table on Pervasive SQL Server have common information. There is one matching field in both the tables. now my task is to create a RecordSet from both the tables using the matching field. I tried some queries but getting errors. Please see what am I doing wrong. The following are some of the querries I've tried:

    1)This code is just to get data from the linkedserver, not even from both the tables:

    Set MyConn = Server.CreateObject("ADODB.Connection")
    MyConn.Open "Provider=SQLOLEDB; Data Source = (local); Initial Catalog = EMMS; User Id = sa; Password=sqladmin"
    Set MyRec = Server.createobject("ADODB.Recordset")
    MyRec.Open "SELECT * FROM LinkServer.DBCLN.dbo.cdtbl1",MyConn

    For the above code, I get the following error in the page:
    ---------------------------------------
    Microsoft OLE DB Provider for SQL Server error '80040e14'

    OLE DB error trace [Non-interface error].

    /emms/dbpervasive.asp, line 7
    ---------------------------------------

    2) I found this query on the Internet, but I don't know if I have to use the code this way or I don't need to use this way. I mean as I already have linkedserver, it means I am not retrieving data from two different databases, hence the confusion. however my final task is to retrive data from both the tables(one from SQL server and one from LinkedServer). Please guide me.

    SELECT emp.EmloyeeID, ord.OrderID, ord.Discount FROM SQLServer1.Northwind.dbo.Employees AS emp, OracleSvr.Catalog1.SchemaX.Orders AS ord WHERE ord.EmployeeID = emp.EmployeeID AND ord.Discount > 0

    In the above query, instead of Oracle, I need to use dsn(which is created for Pervasive SQL). Any ideas.

    Thanks,
    Jay.

  4. #4
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Thank you

    thank you Pat thank you so much. Good grief I wish I was as good as you and Brett. I gotta long ways to go

    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •