Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2002
    Location
    Beaumont, TX
    Posts
    18

    Unanswered: Data from linkedserver using ASP

    Hi Everyone,
    I've linked the Pervasive SQL database to MS SQL Server using Linked server. There is one matching field in both the tables. I need help with the code to access the table from the linked server tables. I tried some queries but getting errors. Please see what am I doing wrong.

    I tried the following code just to get data from the linkedserver, not even from both the tables and getting error saying Invalid object name:

    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
    ---------------------------------------

    Thanks,
    Jay.

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Why use a Linked Server at all? Go direct to the Pervasive engine. Using the code below works for me:
    Code:
    <%@ Language=VBScript %>
    <html>
    <head>
    <meta NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
    
    <body>
    
    <%
    Dim objConn
    dim rsTemp
    Set objConn = server.CreateObject("ADODB.Connection")
    'Using OLEDB
    'strCon = "Provider=PervasiveOLEDB;Data Source=Demodata;Location=MyServer"
    'Using a System DSN
    strCon = "DSN=DEMODATA"
    objConn.ConnectionString = strCon
    
    objConn.Open
    
    mySQL = "Select * from class"
    
    set rstemp=objConn.Execute(mySQL)
    if rstemp.eof then
    	Response.Write "No records matched<br>"
    	Response.Write mySQL & "<br>So cannot make table..."
    	objConn.Close
    	set objConn=nothing
    	Response.End
    end if
    %>
    <table border="1"><tr>
    <% 'Put Headings On The Table of Field Names
    for each whatever in rstemp.fields%>
           <td><b><%=whatever.name%></b></td>
    <% next %>
    </tr>
    
    <% ' Now lets grab all the records
    DO  UNTIL rstemp.eof %>
       <tr>
       <% for each whatever in rstemp.fields
          thisfield=whatever.value
          if isnull(thisfield) then
             thisfield=shownull
          end if
          if trim(thisfield)="" then
             thisfield=showblank
          end if%>
                 <td valign="top"><%=thisfield%></td>
       <% next %>
       </tr>
       <%rstemp.movenext
    LOOP%>
    </table>
    
    <%
    rstemp.close
    set rstemp=nothing
    objConn.Close
    set objConn=nothing
    %>
    </body>
    </html>
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

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

    Need to join tables with SQL Server tables

    Thanks mirtheil, I tried your code and it works fine. But my actual database is in MS SQL Server. So I have to join tables in SQL server with Pervasive SQL. Is there any other way you can join without linke both the databases?

    Thanks,
    Jay.

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    How about trying your connect string and SQL statement in my code? Does that change the behavior? I'm not aware of any other way to join the two databases except manually within your application (reading the records from each database then manipulating the recordsets to "join" them).
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Feb 2004
    Posts
    78
    I do this alot with no problems to compare data between sql server and pervasive, and between separate pervasive servers.

    Try running the following (not using a linked server) from QA:

    EDIT: Make sure you have defined the system ODBC dsn on the server where SQL SERVER IS INSTALLED.

    ----
    select *
    from openrowset('MSDASQL','DSN=[your pervasive odbc dsn]','select * from cdtbl1')
    ----

    This is assuming that cdtbl1 is a table in pervasive.

    Let's see if that works.

    Eric

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

    Thanks Dilyias

    Thanks Dilyias. that works.

Posting Permissions

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