Heidisql: MSSQL Heidi crashes when there is a field with propery datetimeoffset, see's it as bit

[mkeus]

bugreport.txt

Steps to reproduce this issue

1. have datatable with a column with type datetimeoffset(7) (in msssql management studio)
2. select * from table; or click on TAB-Data
3. application crashes

If you select all columns except the datetimeoffset column it works fine
9.5.0.5317 works fine, no crashes on table

strange thing.
If you do a view of the table columns (TAB: table) it shows the datetimeoffset(7) column as a BIT
9.5.0.5317 also shows BIT

Current behavior

See steps to reproduce, application crashes.

Expected behavior

show all data from table including the datetimeoffset column

Possible solution

Environment

• HeidiSQL version:
  10.0.0.5460

• Database system and version:
  MS SQL (EXPRESS) 14.0.2002
  MS SQL (Azure) 12.0.200.8

• Operating system:
  WINDOWS 10

[ansgarbecker]

Relevant part of the crash callstack:

exception number   : 1
exception class    : EOleException
exception message  : Multiple-step operation generated errors. Check each status value.

main thread ($af80):
00aaa142 heidisql32.exe Data.Win.ADODB            TCustomADODataSet.GetFieldData
00a58c80 heidisql32.exe Data.DB                   TField.GetData
00a59120 heidisql32.exe Data.DB                   TField.GetIsNull
00b07457 heidisql32.exe dbconnection    6472   +7 TAdoDBQuery.IsNull
00db8d22 heidisql32.exe Main            8663  +10 TMainForm.AnyGridGetText
00949f46 heidisql32.exe VirtualTrees   33939   +8 TCustomVirtualStringTree.DoGetText
00948833 heidisql32.exe VirtualTrees   33413   +4 TCustomVirtualStringTree.GetText
00949e63 heidisql32.exe VirtualTrees   33923  +27 TCustomVirtualStringTree.DoGetNodeWidth
00939b28 heidisql32.exe VirtualTrees   26979  +85 TBaseVirtualTree.GetDisplayRect
00dbbb89 heidisql32.exe Main            9245  +28 TMainForm.AutoCalcColWidth
00da4b97 heidisql32.exe Main            5089 +175 TMainForm.DataGridBeforePaint

[ansgarbecker]

I "silenced" that error for the next builds, so you should not get it any more, in any situation. Not sure if that's a good idea, you should definitely check that twice and report back.

[mkeus]

bugreport-10.1.0-5479.txt
I still see the error when I select the DATA tab.
If I only select the field with type datetimeoffset I see a resultset with 'node' as value.

More info: when is select the datetimeoffset as last item in the query it returns fieldcount-1 so the datetimeoffset field is never selected. When there is a field behind the datetimeoffset field, it crashes.

[ansgarbecker]

The error is now not in .IsNull but in .GetValue, which renders my solution useless, I should even revert it.

I have no clue what this "Multiple-step operation generated errors" means. Can you shed some light on that?

[mkeus]

It does not matter if I query 1 row with a null value for the field or an actual value. MS SQL management studio (MSMS) shows it as a datetime value.

I think the problem lies in the size of the field,
(https://stackoverflow.com/questions/17659830/delphi-error-multiple-step-operation-generater-error-check-each-status-value)
http://www.adopenstatic.com/faq/80040e21.asp --scenario 6

According to MSMS, The datetimeoffset field has the following properties:

Length = 10
precision = 34
numeric scale = 7

[bruce965]

I noticed that in the "Table" tab, HeidiSQL does not correctly detect DATETIMEOFFSET fields.

The detected datatype is the same as the first previous non-DATETIMEOFFSET field.
I tested various tables and HeidiSQL versions, this behaviour is consistent.

I think the problem described by this bugreport is that:

• In the "Data" tab, HeidiSQL tried to render the DATETIMEOFFSET field as the previous non-DATETIMEOFFSET field
• This field happens to be a BIT (or maybe there is no previous non-DATETIMEOFFSET field at all)
• DATETIMEOFFSET is not convertible to BIT

HeidiSQL never crashed on me when erroneously detecting DATETIMEOFFSET fields as NVARCHAR.
Only when detecting them as other values like BIT.

[ansgarbecker]

Heidi should now detect DATETIMEOFFSET columns correctly, at least in the table editor and in the data tab. Check out the next nightly build.

[mkeus]

@ansgarbecker Thanks for the update,

The datatype is shown as DateTimeOffset in the Table-Tab, although the selection of the Data-Tab still crashes with the same error:
version : 10.1.0.5545
exception number : 1
exception class : EOleException
exception message : Multiple-step operation generated errors. Check each status value

When I only select the datetimeoffset field, it shows a list of 'Node' maybe that is the problem?

[timgblack]

I'm seeing the same issue but with TIME columns. Converting them (CONVERT(VARCHAR, "TimeColumn")) works, but it can't be done for the Data view or when exporting.

DATETIMEOFFSET and DATETIME2 columns are working fine in the latest build.

[mkeus]

I see different behaviour bij selecting another library.
For mssql I have the SQLOLEDB (this does not have the datetimeoffset problem, but only has TLS 1.0) I also have a MSOLEDBSQL library (this has the datetimeoffset problems)

You can change the library in the settings of the sessionmanager.