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?

[NadiaB]

Hi @ManuelRühl 

By any chance could it be possible to confirm the data type for that column in the table? the field is called timestamp but wanted to double check what is the actual type was set when the table was created, could it be possible to share the details?

Kind Regards. 

[ManuelRühl]

The sqlcolumns; command returns this:

SQL Configuration:

Google translate:

Microsoft SQL Server ODBC Driver Version 10.00.17763

Data source name: ***
Data source description:
Server: ***
Database: **
Language: (Default)
Convert characters: Yes
Log long-running queries: No
Protocol Driver Statistics: No
Use country settings: No
Prepared statements option: Delete temporary procedures on disconnect
Use failover server: No
Use ANSI Quotation Marks: Yes
Use ANSI nulls, spaces, and warnings: Yes
Data encryption: no

Hope this helps, tank you! 👍

[Albert_Candelario]

Hello @ManuelRühl,

If you quote the field with the square brackets, does it work?

Please test it and let us know.

Cheers,

Albert

[ManuelRühl]

@Albert_Candelario 

Like this?

SELECT [timestamp] FROM *****.dbo."*****$Item";  -> Does not work, same error.

SELECT [No_] FROM *****.dbo."*****$Item"; -> Works

But I want to do this:

SELECT * FROM *****.dbo."*****$Item";

[Albert_Candelario]

Hello @ManuelRühl,

yes, like I we saw in the picture:

SELECT [timestamp],

No_,

and so one.

Can you also confirm you get the issue with that script?

Cheers,

Albert

[ManuelRühl]

@Albert_Candelario 

Yes:

Der folgende Fehler ist aufgetreten:

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

Der Fehler ist hier aufgetreten:

SELECT [timestamp], "No_" FROM *****.dbo."*****$Item"

[ManuelRühl]

@Albert_Candelario 

Any Update on my problem? 😮

[marcus_sommer]

I suggest to try to exclude potential causes, for example just to rename the column, maybe to _Zeitstempel_ to exclude that timestamp is a reserved term and/or also a variable/routine/table-name and/or treated as function-call. A direct renaming within the query or in the table might be not sufficient/sensible but the column or the table might be duplicated.

Another way may be to create a new table just with such timestamp column (with this name and also another one) and a few definitely valid values. The same checks like above mentioned would be possible and also that further properties on the column and/or any invalid values breaks the query by returning a misleading error-message.    

[Albert_Candelario]

Thanks @marcus_sommer  for sharing your insight.

Cheers,

Albert