Pervasive

Is your Pervasive PSQ v11 a 64-bit engine?  Can you try a test using ODBC instead of OLE DB?

Can you run the same basic update statement from the PCC?  Does it return an error? 

Just to be clear,  the PSQL server engine, MS SQL server engine, and PSQL data files are all on the same machine.  Correct? 

Thanks for your prompt reaction!

Indeed, all MS SQL and PSQL files reside on one 64 bit server, called WIN11.

Initially, I have been confused about which ODBC administrator to use: the one in system 32, or the one in SysWOW64. I have defined all ODBC system DSN's on this server, using %windir%\system32\odbcad32.exe with the following parameters:

Data Source Name: sunclasssonnevijver, ...
Description: Pervasive ODBC Interface
Server Name / IP: WIN11
Transport Hint: Try TCP/IP first, then SPX
Database Name: SUNCLASSSONNEVIJVER, ...

I have linked MS SQL Server to these DSN's with the following procedure call:

execute sp_addlinkedserver
@server = 'sunclasssonnevijver',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'sunclasssonnevijver';

In this configuration, I can run the following MS SQL Server query without any problem: 

select *
from [sunclasssonnevijver].[sunclasssonnevijver]..[NUMMERS]
where BENAMING = @benaming

The conflicting MS TSQL statement is:

update [sunclasssonnevijver].[sunclasssonnevijver]..[NUMMERS]
set NUMMER = NUMMER + 1
where BENAMING = @benaming

Amazingly, running a similar, very basic update statement in the PCC

update NUMMERS
set NUMMER = NUMMER + 1
where BENAMING = 'AFLEN'

results in the following error message:

<<<<<<<<<<<<<<<<<<<<<<<<
[LNA][Pervasive][ODBC Engine Interface]Error in expression: NUMMER + 1
>>>>>>>>>>>>>>>>>>>>>>>

Definitely, the same statement has worked fine in the previously existing context (Windows 2003 SBS, SQL Server 2005 Express, Pervasive SQL v10).

This is about all there is to say! Once again, I would be very glad if you could help me out.

 What data type is NUMMER? 

Hello,

Our book keeping application Briljant registers the first free sequence number NUMMER within a named range BENAMING (primary key, in this case 'AFLEN') in a table called NUMMERS. The schema dump of the table NUMMERS explains a lot!

Can you post a screen shot of the actual error?  This forum doesn't allow attachments so you'd need to use another service.  I like imgur.com.

Hello Mirtheil,

I have uploaded the requested image. It is avilable under the following link: http://imgur.com/9fIIR. I certainly hope, that you can make coffee of it!

Thanks so far,

Han

I ran into a similar error unexpectedly: while running an ordinary MS TSQL query on a table in a PSQL database. The error occurs as soon as I reference the LONGVARCHAR column OMSCHRIJVING in the table FKLA2012. When you skip this reference, the query just runs fine. Could ther be something wrong with the 64-bit ODBC driver?

Below, I will copy the query as well as the table schema concerned:

select [BOEK],
[FACTNR],
[CODEFCBD],
[KLANR],
[FACTDAT],
[PERIODE],
[VERVDAT],
[CODEDC],
[TEBET],
[BETREFTBKJR],
[BETREFTBOEK],
[BETREFTFCTNR],
[BETAALD],
[OMSCHRIJVING]
from [sunclasssonnevijver].[sunclasssonnevijver]..[FKLA2012]

CREATE TABLE "FKLA2012"(
 "BOEK" CHAR(2),
 "FACTNR" UINTEGER,
 "LIJNNR" UINTEGER,
 "CODEFCBD" CHAR(1),
 "BTWREGIME" CHAR(1),
 "KLANR" CHAR(8),
 "FACTDAT" DATE,
 "PERIODE" CHAR(2),
 "VERVDAT" DATE,
 "VALUTA" CHAR(4),
 "KOERS" DOUBLE,
 "VERTEGENW" CHAR(8),
 "CODEDC" CHAR(1),
 "TEBET" DOUBLE,
 "KORTCONT" DOUBLE,
 "BEDRAGKC" DOUBLE,
 "BASIS" DOUBLE,
 "BTWAFTR" DOUBLE,
 "BTWNAPRIV" DOUBLE,
 "BTWNABEDR" DOUBLE,
 "BTWTEBET" DOUBLE,
 "TEBETIV" DOUBLE,
 "BEDRAGKCIV" DOUBLE,
 "BASISIV" DOUBLE,
 "BTWAFTRIV" DOUBLE,
 "BTWNAPRIVIV" DOUBLE,
 "BTWNABEDRIV" DOUBLE,
 "BTWTEBETIV" DOUBLE,
 "ORIBKJR" UINTEGER,
 "ORIBOEK" CHAR(2),
 "ORIFCTNR" UINTEGER,
 "BETREFTBKJR" UINTEGER,
 "BETREFTBOEK" CHAR(2),
 "BETREFTFCTNR" UINTEGER,
 "BETAALD" CHAR(1),
 "AFGEPUNT" CHAR(1),
 "NRAFPUNT" UINTEGER,
 "BTWGEWIJZIGD" CHAR(1),
 "STATUS" CHAR(1),
 "STATOPM" CHAR(30),
 "MANINGNIV" CHAR(1),
 "MANINGDAT" DATE,
 "ISSPECREK" CHAR(1),
 "DRIEHOEKS" CHAR(1),
 "DUBIEUS" CHAR(1),
 "BASNTBEL" DOUBLE,
 "BASNTBELIV" DOUBLE,
 "OMSCHRIJVING" LONGVARCHAR);

CREATE UNIQUE NOT MODIFIABLE INDEX "index_0" ON "FKLA2012"("BOEK");

 Once you mentioned the LONGVARCHAR field, I was reminded of this thread.  The solution was to CAST the LONGVARCHAR field. For example, "select cast(LongVarCharField, varchar(8000)) from table".  This is not a problem in the PSQL driver. LONGVARCHAR fields work correctly in PCC, ODBC Test, and other environments.  They only seem to fail when used in a Linked Server query.  My guess is that SQL Server isn't allocating memory properly and the long data from the LONGVARCHAR field is overwriting memory. 

The cast to varchar works and is no longer a problem. Thank you! How about the problem I mentioned earlier? Is there a logical explanation for this behaviour? The only workaround I can think of is writing the insert statements to a batch file for PSQL. This will certainly work, but feels a bit like going back to the past.

Han Schouten