Question summarized:
How do I get Pervasive SQL 15.2 as a Linked Server into SQL Server 2022 like we did with 9.7 into 2000?
Background and details:
We have an ERP system that's using a Btrieve database and we have extensive extensions written in SQL Server. These queries (including insert and update) work fine on our old production system, but all insert and update queries accessing a NOTE column through the linked server from SQL Server to PSQL fail.
Technical Setup
| Old | New | |
|---|---|---|
| OS | Windows Server 2003R2 | Windows Server 2019 |
| Arch | 32bit | 64bit |
| SQL | Microsoft SQL Server 2000 | Microsoft SQL Server 2022 |
| Btrieve | Pervasive 9.5 | Zen 15.2 |
| ODBC | Client Engine Interface | Client Interface |
How databases were set up and transferred (boring to most)
The SQL-database was "moved" by generating a CREATE DATABASE script on the old system, changing dboption to ALTER TABLE and running it on the new system. The data itself is transferred through a linked server on the new system to read the latest data from SQL (preparation for the final migration) The Pervasive data was copied from the old system and marked as database by the Zen Control Center.
Why we need the note-column (not that it matters to the problem)
The ERP uses NOTE-columns on many tables to encode additional (user defined) pseudo columns. This means we write in a text file what we want to store like "'GDPR signed' as bool" and the ERP shows this as a Checkbox while the NOTE would contain "{GDPR signed:Y}"
Due to the grown constructs between our ERP and our extensions based on the SQL Server we need to be able to modify the NOTE-column of the tables.
On the old system we would run
UPDATE OPENQUERY(MYLINK, 'SELECT "ID", "Data", "MoreData", Notes FROM MyT')
SET Notes = 'Hallo Welt'
WHERE ID = 'XXX'
and it would just update.
On the new system the same query fails with
[Zen][ODBC Client Interface][LNA][Zen][SQL Engine]Function sequence error
What we tried to narrow the problem down
- We tried to execute the "real" query
UPDATE MyT SET Notes='Hello World' WHERE ID='XXX'using ZCC and 'QTODBC 7.0' - both work just fine - We tried to use ODBCtrace to find what is happening, but we couldn't get a trace of the SSMS. It just didn't log anything (RDP on the system itself)
- We modified the linked server using different settings, but the only change was broken data in SELECT if changing the wrong parameter (UTF vs ANSI...)
What we couldn't try
- Change the ODBC to Engine Interface - that's 32bit only and "should not be used as it will soon be dropped"
- Test with a 32bit SQL - if that would work the project would still be failed - no benefit in trying
We are out of ideas on what to check and try.
We are also quite challenged by the changes and renames that happened between SQL Server 2000 and SQL Server 2022 (it took me nearly a day to verify that our old database was setup mostly with the 2022 default settings because I had to translate the dboptions to ALTER TABLE ('read only' = false vs. READ_WRITE ON))
Update 1: Question added at top as it was dropped during formatting/rewriting of the information.