Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

MSSQL: Bug: Export grid row as INSERT statements generates wrong statement for bit columns

igitur's profile image igitur posted 4 years ago in General Permalink

If I have a column defined as BIT and try to 'Export grid rows' and select' SQL INSERTs', the statements generate as follows:

INSERT INTO "LeadInReturn" ("Scheme_Id", "ReturnDate", "ReturnValue", "IsNettOfFees", "CompoundFrequency") VALUES (7, '2007-03-01 00:00:00.000', 0.079042207339953, b'1', 0);

However, a b`1` isn't a valid MSSQL conversion. It should be a pure 1.

igitur's profile image igitur posted 4 years ago Permalink

I meant:

However, a b'1' isn't a valid MSSQL conversion. It should be a pure 1.

igitur's profile image igitur posted 4 years ago Permalink

Should be simple fix?

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
ansgar's profile image ansgar posted 4 years ago Permalink

Yes, I'll check that. Thanks for the hint.

igitur's profile image igitur posted 4 years ago Permalink

Please, ansgar ?

igitur's profile image igitur posted 4 years ago Permalink

I hate to do this... but bump. This is surprisingly frustrating.

Code modification/commit from ansgarbecker, 4 years ago, revision 5073
Use b'' format only for BIT columns in MySQL only. See http://www.heidisql.com/forum.php?t=20333
ansgar's profile image ansgar posted 4 years ago Permalink

r5073 should fix that.

igitur's profile image igitur posted 4 years ago Permalink

r5073 should fix that.

Perfect, thanks.

Code modification/commit from ansgarbecker, 4 years ago, revision 5077
Missing file for r5073: BIT column syntax. See http://www.heidisql.com/forum.php?t=20333
Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
Fred56's profile image Fred56 posted 4 years ago Permalink

The error is not corrected for Microsoft SQL in r7078.

igitur's profile image igitur posted 4 years ago Permalink

The error is not corrected for Microsoft SQL in r7078.

I assume you mean r5078 ? Anyway, it works fine for me in r5078. What is the SQL that is generated for you?

Fred56's profile image Fred56 posted 4 years ago Permalink

I am using SQL Express 2012 HeidiSQL 9.3.0.5073. If in a table of data type BIT is, is set in the SET command = b'0 '.

1 attachment(s):
  • hilfe
Fred56's profile image Fred56 posted 4 years ago Permalink

I have updated to 9.3.0.5078 HeidiSQL, the same procedure

igitur's profile image igitur posted 4 years ago Permalink

I am using SQL Express 2012 HeidiSQL 9.3.0.5073. If in a table of data type BIT is, is set in the SET command = b'0 '.

Huh? the SET keyword isn't valid in an INSERT command. What exactly are you doing to generate the script?

Fred56's profile image Fred56 posted 4 years ago Permalink

I do not generating. I want to change in a table a value. Then I get the error message 102 ... HeidiSQL then generates the SQL code UPDATE ... SET ... WHERE ... "Column 2" 0b'0'... I can not change data of the table.

Fred56's profile image Fred56 posted 4 years ago Permalink

Soory

I do not generating. I want to change in a table a value. Then I get the error message 102 ... HeidiSQL then generates the SQL code UPDATE ... SET ... WHERE ... "Column 2" = b'0'... I can not change data of the table.

Fred56's profile image Fred56 posted 4 years ago Permalink

I found the mistake. The SQL database always requires a key. Access makes their own key if none exists. HeidiSQL does not. Only the error message is a little bit irritating. Thanks for the helpful tips. Sorry for my bad English.

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