Hello,
We have sql 2000 and I need to retreive data from a Prevasive database. Can I cross tables from the two different databases and how it is possible ?
Thanks a lot.
1) Create a data source on the server that points to the Pervasive database (Pervasive ODBC drivers included on the CD's, or download from Pervasive, I think).
2) Create a linked server using the new data source.
3) Query data from the linked server using the openquery command. Joins are valid if like fields are linked.
Regular example: SELECT * FROM OPENQUERY(LINKEDDB,'SELECT Field1, Field2, Fields3 FROM PervasiveTable')
Results show Field1, Field2 and Field3 from the Pervasive table
Join Example: SELECT A.Field1, B.Field2, B.Field3 FROM LocalTable AS A INNER JOIN OPENQUERY(LINKEDDB,'SELECT Field1, Field2, Field3 FROM PervasiveTable') AS B ON A.Field1 = B.Field1
Results show Field1 from the local SQL table, Field2 and Field3 from the Pervasive table.
INSERTS, UPDATES and DELETES are valid in the linked server, but be VERY careful using these guys. Pervasive is very picky about how it recieves a field...and it probably isn't a good idea to delete records unless you have a thorough knowledge of the Pervasive data structure. Even then, use extreme care.
Hopefully, that will give you a start. Keep in mind that the Pervasive structure is file-oriented (each table is a file) and can be a bit slow at times.
Bill
Linguinut
------------------
Bill Cunnien
ConBrio Consulting
Mimi, from bitter experience, you have to install the Pervasive ODBC Client on the SQL Server machine to succeed at this. Pervasive uses >1 dlls for its ODBC communication, so it's not just a simple ODBC connection string.
If you don't see the Pervasive interface pop up when you create a new DSN, you need to install the client.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.