Data from linkedserver using ASP

jayDBF · #1 (**permalink**) 08-02-04, 09:59

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.

mirtheil · #2 (**permalink**) 08-02-04, 10:52

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>

jayDBF · #3 (**permalink**) 08-02-04, 12:24

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.

mirtheil · #4 (**permalink**) 08-02-04, 12:33

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

Dilyias · #5 (**permalink**) 08-13-04, 17:30

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

jayDBF · #6 (**permalink**) 08-16-04, 11:49

Thanks Dilyias. that works.

Internet Services

Web Development

Digital Marketing

Consumer Tech