I know virtually nothing about PSQL other than that which I have read here and googled...so I am asking if someone here could perhaps give me some guidance on code structure to accomplish the following:

A record is inserted into TSQL (2008 64bit)
after insert, query the PSQL (2011) database table using a TSQL column data
retrieve row data from PSQL based on the TSQL column data
insert (update the row) the retrieved data back into the TSQL record

Any help would be greatly appreciated. Thank you.

I think the code structure is pretty close to what you describe but the bigger question is what language do you want to do this in?

I'm curious, why are you doing this sequence? What is your end goal?

Thank you for your reply. I probably would want it to reside on the TSQL server in the form of a trigger.

The end goal would be that when a record is indexed (inserted) into a TSQL table, in this case purchase_order, using the TSQL column po_number, query the PSQL purchase_order table by the po_number value that was just inserted into the TSQL column, grab other PSQL column values so those values could be then updated in the TSQL purchase_order table.

I'm probably not explaining very well, but basically a database lookup to update values in a purchase order based on a key field. The idea is to only have a person have to manually enter in the purchase order number in an application with a TSQL 2008 database and have the rest of that data, which resides in a PSQL 2011 database, to auto populate. It is a canned application with no programming interface.

Again, thank you.

That clears up a lot.
Within your trigger on the MS SQL side, you may (don't know, haven't tried) be able to use a Linked Server. If you can access the Linked Server, you can create a Linked Server to the PSQL database using the PSQL ODBC driver (I wouldn't use the OLEBD driver). Once the Linked Server is set up, you can then query the PSQL database and modify the MS SQL database.

I contacted Pervasive today today to see if there was a download of the ODBC driver, but their tech support said no and we never have released one. According to them you need a complete installation of PSQL. I cant have PSQL installed on this server. Any ideas which files, reg keys or whatever to enable the ODBC driver?

What version of PSQL do you have? All versions since PSQL 2000 (PSQL 2000, v8, v9, v10, and v11) have included the ODBC driver in both the client and engine (server or workgroup) installs. You automatically get the driver if you run the PSQL install.

With later versions of PSQL (v9 and later), you can disable parts of the install to make it smaller but you still need all of the client level components to get either the ODBC or OLEDB driver. The ODBC driver was released as a separate install back in the Btrieve 6.15 days (1996-ish) when it was first introduced. It hasn't been separate since though. Why can't PSQL be installed?

I appreciate your reply. The version is: Pervasive.SQL 10.20.0020.000. I have it installed on a development server here, but won't be allowed to be installed on the production server. That's why I'm trying to figure out how to enable ODBC here somehow. I've looked at the dsn for the demodata database and it uses w3odbcci.dll, but I can't register that as it has dependencies. I've tried locating 3rd party PSQL ODBC drivers, but can't seem to find any. So I'm looking for some suggestions or advice.

[quote]I have it installed on a development server here, but won't be allowed to be installed on the production server.[quote]
Why won't you be allowed to installed it on the production server? And if you can't install PSQL, why would you be able to install third party drivers (which will still need the client components to communicate to the engine).

You cannot just copy the W3ODBCCI.DLL and get it working. There are a number of other files and registry entries required. The best (and only supported) way is to run the install.