SqlServer and Pervasive SQL

[cmwDelphiGuy]

Is there a way to make Pervasive SQL talk to an MS SqlServer? I have data on both types of servers. When one is updated I need to trigger an update on the other database.

 

[bucket]

Did you find an answer to this ? we are looking for the same solution.

 

[Guest_imported]

I could use the answer to this also.

 

[Guest_imported]

You can use extended Stored Procedure in SQLServer to update your Pervasive SQL. As far I know you can't do the same things in Pervasive SQL. If you find one way to make it possible let me know.

CDC
cdc_13@hotmail.com

 

[MyWurld]

Incase your still looking for the answer to this question. It is possible in MS SQL SERVER 7.0, a feature was added called linked servers. You can setup a linked server in MS SQL that connects to Pervasive. Let me know if you need more help. Look at the help files for MS SQL "Linked Servers"

 

[Guest_imported]

From pervasive web site:

Does Pervasive OLEDB provide support for Linked Servers?
Problem Description:
When trying to use Microsoft SQL Server linked server options, I am unable to create a connection using the Pervasive OLEDB provider.

Problem Environment:
Pervasive OLEDB

Pervasive.SQL 2000i

Microsoft SQL Linked Server

Enhancement 33903


Solution:
At this point, using the linked server option via Pervasive OLEDB is unsupported. You can use this option via the ODBC driver at this time. This is currently an enhancement request for the OLEDB provider. There is not an estimated timeframe for this feature.

Published Jan2002

 

[mrarthur]

The Data Agent Server tool from WorkWise Software (

http://www.workwise.com)

will update data from a Pervasive SQL database to an MS SQL Server database and vice-versa. The updates are configured through a 'no programming required' wizard interface. Every DBA could use this tool. You can also execute SQL statements/pass parameters, launch scripts, send event-based notifications, print files, launch other programs and more.

 

[Guest_imported]

Does Pervasive SQL2000 support Cubes for OLAP clients
like ProClarity ?. If not, can I create Cube from Pervasive
SQL2000 database using MS SQL DTS ?

 

[Guest_imported]

PervasiveIsTough,
can you clarify how I could create a linked server via ODBC driver? I did so using the OLEDB Provider for ODBC and it connects fine, gets all the tables, but I cannot open any tables?

Kinetic-media.com

 

[philhege]

Kinetic,

You can use the OPENQUERY function to retrieve data. If you have a linked server named "PERVASIVE", and a table in your Pervasive DB named "mytable", the syntax is

SELECT * FROM OPENQUERY(PERVASIVE,'SELECT * FROM mytable')

Since you can't use parameters in the OPENQUERY statement, you have to narrow the query results outside of the function:

SELECT * FROM OPENQUERY(PERVASIVE,'SELECT * FROM mytable')
WHERE MYFIELD = MYPARAMETERS

Slow (especially if the Pervasive table has a lot of records), but it works.

HTH,
Phil Hegedusich

 

[Guest_imported]

Does anyone know of any known problems with running a pervasive sql database on the same box as a Microsoft SQL database? Two completely different applications.

All input will be greatly appreciated.

 

[mirtheil]

As long as the server has enough resources (memory, processor, RAM, Hard Drive space), it shouldn't be a problem to run both on the same server.
mirtheil@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[Guest_imported]

I am having the same problem. I try and create a linked server via enterprise manager. It shows the linked server, but I can't list the tables. have also tried from Query Analyser.

What is a typical SQL statement to execute to create a linked server "EXEC sp_addlinkedserver ????????"

 

[jmaz]

SQL Server 7.0
Pervasive.SQL 7.0 (Service Pack 6)
Re: Linked Servers

I have:
1) Linked a Pervasive Databse to an MS Sql Server
2) Successfully retrieved records using the OPENQUERY('ServerName','SQLStmnt') syntax from within MS Query Analyzer.

However, I need to update data on a Linked Server Table using values from a Local SQL Server lookup table.

How can I update values on a table on a linked sever by joining a linked server table, and a local SQL server table?

 

[philhege]

jmaz,

It's a bit twisted, but doable. If you stare at the UPDATE syntax in BOL long enough, it might come to you. Let me save you the headache:

SQL treats the OPENQUERY results just like a table. So you put your OPENQUERY statement in place of a "normal" table name. In the OPENQUERY statement, SELECT only the fields you need to update (including the where clause fields).

UPDATE openquery(yourlinkedserver,'select myremotetablekeyfield,myfield1, myfield2 FROM Myremotetable')
SET myfield1 = myreturnedvalue1,
myfield2 = myreturnedvalue2
WHERE myremotetablekeyfield = mycriteria

Make sure your DDFs are up to date for the Pervasive schema.

HTH,
PH

 

[aBill]

Hello everyone, I am haing a similar situation to the messages listed here. Here is the lowdown.

1.)Microsoft SQL Server 2000 sp2
2.)Pervasive SQL 2000 on seperate machine, running Timberline.

I need to create a linked server so that the MSSQL server can run scheduled jobs to import data from the Pervasive DB. The job and packages are already setup in MSSQL and work fine when manually triggered, but fail when sceduled. It is my understanding that this is because the system process that runs the job does not have access to the Mapped Network drive needed for the Pervasive System DSN.

I am also told that a linked server will allow me to do this. So.... Can anyone be of assistance in instructiong me how to create the linked server? The connection string is giving me a lot of issues.

Thanks.

 

[philhege]

aBill,

Make sure the SQL Server Agent service is running under a user account that has rights to the network resource. Likewise, make sure the job is running under the same account.

HTH,
PH

 

[aBill]

The SQL Agent is running under an Domain Administrator account for testing. The job is owned by the same account.

Since this is a scheduled job and is run by the system as the specified user, does it have access to the mapped network drive? The source database is on a mapped network drive specified in the DSN.