Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SQL 2000 and Prevasive 2
- Thread starter mimi2
- Start date
-
1
- #2
Good morning!
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
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
-
1
- #3
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.
Good luck,
PH
If you don't see the Pervasive interface pop up when you create a new DSN, you need to install the client.
Good luck,
PH
- Thread starter
- #5
- Status
Similar threads
- Question
