SQL Error (529): Explicit conversion from data type timestamp to nvarchar is not allowed.

python_dev posted 4 years ago in General
I have multiple tables with timestamps, so the end result of this error is I cannot access the data tab of any of them connected to MSSQL database. I have to manually change the query to VARCHAR instead of NVARCHAR in order to see data in the tables and run as a query..
Is there any way to turn this off????
ansgar posted 4 years ago
The MSSQL TIMESTAMP data type is currently sorted into the binary type category. This is wrong I guess, and it's the cause for why HeidiSQL applies a LEFT(CAST()) to it in the data tab.

So, I will need to move that TIMESTAMP data type into the right category. But I'm having problems in understanding Microsoft's intention for timestamp columns. It's not a simple number, and it's not a readable string, end even not some date/time format. If I insert some row in a table with a timestamp, that timestamp value is auto-filled with some binary character. How does HeidiSQL need to display such values? I guess I must do some internal conversion?
jfalch posted 4 years ago
ahem; thus, most probably cast to int.
sspnet posted 4 years ago
I am having the same problems with MsSQL tables with timestamp fields.

Did you get any further finding the solution?
ansgar posted 4 years ago
I'm still not sure if it will run without any CAST and LEFT call, but I'm going to test that. The posts on stackoverflow says it's a "binary representation of a consecutive number". So probably I have to cast to INT or so.
ansgar posted 4 years ago
This gives me some cosecutive numeric value in a TIMESTAMP column:
SELECT CAST("Column 2" AS INT) FROM "anse"."dbo"."foo";

However, attempts to update a TIMESTAMP column seems to be impossible:
update "anse"."dbo"."foo" set "Column 2"=34007 where "Column 2"=34006
>> SQL Error (272): Cannot update a timestamp column.

But I guess it's ok if at least the value can be seen without triggering some error.
ansgar posted 4 years ago
Should work now without errors, in r4673.

Please login to leave a reply, or register at first.