Data Transfer MS SQL Direct Access - Error loading timestamp

[ManuelRühl]

Hi,

I get the following error:

(Connector error: Status(StatusCode="InvalidArgument", Detail="Input string was not in a correct format."))

I implemented Qlik Direct Access on the on-premise Server. Everything works fine except of the timestamp.
The System is Microsoft Navision with Microsoft SQL.
The format of the timestamp-field is f.e. 000000000265524E.
I can see the field in the load-Manager without any errors:

If I remove the field "timestamp", I can load and store the table without any problems.
Directly on the on-premise-Server I can load the field using Qlikview without any problems.

Any suggestions?

[ManuelRühl]

Thanks @marcus_sommer for replying. The timestamp field exists in nearly every table. I don't need it. If I create new tables within the database I will have to update them as well when something changes. My solution will be to do this within the Qlik script.

But: I had the possibility to check with another Navision-System (Newer version, Business Central).

Same Item-Table, same timestamp field: works perfectly. 🤷‍♂️

[marcus_sommer]

My suggestion wasn't meant as solution else to find the real cause behind the error. That's working with a newer data-base is a good sign and may already exclude one cause - if the identically driver with the identically configuration is used. If it's a different driver you could try for a test to use the other driver with the newer navision-version.

If this worked, too the driver isn't the problem and reverse if not you would have found the cause. If it's the driver you could try to use the newer one also against the elder system and/or an investigation to the differences of both drivers/configurations - newer data-types, 32bit vs. 64bit, ...

If not the driver is the issue you need to investigate the matter within the data-base. Helpful for it may be to reduce the data-set of query to just a single record which has definitely a valid timestamp-value to exclude invalid data as cause. Further if nearly all tables have such timestamp-fields - what happens by loading the timestamp from there? Each time the same error or does some be working? If any timestamp-query is working it exclude the previous mentioned things of reserved terms and/or variable/procedure-names and you have something to investigate the differences between these tables/fields.

The last relates to look on the real stored values in these fields because the data-base UI may not mandatory show the absolute truth else any converted/formatted/decrypted version of it. Also the properties of the tables/fields may have an impact like no-read-rights or encryption/hashing, ... In this regard a test with another user may also helpful.

If it happens only to this table and/or just a few the worst case of a corrupt table might be also considered. Beside all of above I think the solution must be found within data-base/driver because Qlik doesn't execute any SQL else transferred only the query-statement and get back a result data-set.

ps: just take a look if a variable timestamp exists within the Qlik application ...  

[NadiaB]

Hi @ManuelRühl 

The underlaying issue is that timestamp is an unsupported data type:

https://help.qlik.com/en-US/connectors/Subsystems/ODBC_connector_help/Content/Connectors_ODBC/SQLSer...

We have seen different issues where the reload works on prem and not in QCS, same thing will happen if you try to load varbinary , it will wok on-prem and not in QCS where it will error out. 

We have heard from our R&D team that although it might work in on-prem as the data type is not listed in the supported data types is not expected to work. 

It is suggested to file a Feature Request to support timestamp data type. 

Hope it helps !

[ManuelRühl]

@NadiaB 

Thanks for investigating this issue.

But i can access timestamp-fields of another Navision System without any problems.

[ManuelRühl · Accepted solution]

I don't know what happend, but now it works perfectly.
Maybe there was an update of the connector?

[Albert_Candelario]

Thanks for letting us know!