Connector reply error on Incremental Load

[#1]

Hi!

I'm completely new to Qlik Sense and I've been learning the ropes for a few weeks now. I ran into a problem that doesn't seem to have been addressed or at least I couldn't find any material to this specific error (I could be wrong though!).

I'm trying to get incremental load with insert, update and delete to work with my Qlik Sense app. I've gained access to our database (SQL Server) using ODBC and have already managed to read our "Work Orders" table successfully. The problem is that I cannot get the incremental load script to work, as I run into the error:

          Connector reply error: ***

I literally have no clue as to what this entails. The connection to the database should be working fine as I've already managed to read the table before. I used this example script provided on Qlik's website:

Let ThisExecTime = Now( );

QV_Table:

SQL SELECT PrimaryKey, X, Y FROM DB_TABLE

WHERE ModificationTime >= #$(LastExecTime)#

AND ModificationTime < #$(ThisExecTime)#;

Concatenate LOAD PrimaryKey, X, Y FROM File.QVD

WHERE NOT EXISTS(PrimaryKey);

Inner Join SQL SELECT PrimaryKey FROM DB_TABLE;

If ScriptErrorCount = 0 then

STORE QV_Table INTO File.QVD;

Let LastExecTime = ThisExecTime;

End If

Only that I modified it like so (I changed the names of some fields for confidentiality reasons):

LIB CONNECT TO 'DBNAME';

Let ThisExecTime = Now( );

WORDERS:

SQL SELECT Id

FROM DBNAME.WORDERS

WHERE Modified >= #$(LastExecTime)#

AND Modified < #$(ThisExecTime)#;

Concatenate LOAD Id FROM [lib://DBNAME_QVDS/WORDERS.QVD] (qvd)

WHERE NOT EXISTS(Id);

Inner Join SQL SELECT Id FROM DBNAME.WORDERS;

If ScriptErrorCount = 0 then

  STORE WORDERS INTO [lib://DBNAME_QVDS/WORDERS.QVD] (qvd);

  Let LastExecTime = ThisExecTime;

End If

drop table WORDERS;

exit script;

At first, I suspected that the error may have occurred due to the variable "LastExecTime" being empty at first, but initializing it to 0 and running the script gave the same error. Also, I'm curious as to what the X and Y are in the example script, as I merely assumed they were for cases where there was no surrogate primary key (i.e. an ID) and the key consists of multiple fields (please correct me if I'm wrong here!). The X and Y might also be worth addressing in the documentation for incremental load to avoid this type of confusion, in case any Qlik employees read this

Any help in guiding me in the right direction would be greatly appreciated!

[rittermd]

Is the connector working for the QVD?

[#3]

The connector works for the QVD. I believe that I have (at least partially) solved the problem though.

The problem was in the the WHERE-clause of the first SQL query. I found this almost by accident when I commented out the WHERE-clause and the script ran without any problems. I did not realize that the '#' symbols around the two timestamp variables need to be changed to single quotes.:

WORDERS:

SQL SELECT Id

FROM DBNAME.WORDERS

WHERE Modified >= '$(LastExecTime)'

AND Modified <  '$(ThisExecTime)';

After this change, the script worked when I initialized the LastExecTime variable to an earlier date (e.g. '2010-01-01 09:00:00'). However, I do still get the connector error if the LastExecTime and ThisExecTime are essentially the same (or a few seconds apart). It looks like an erroneous SQL query seems to lead to the connector error. Perhaps the ODBC is throwing some sort of exception or error?