[MSSQL] Export - CREATE TABLE option with errors

Hi everybody!

Base: HeidiSQL 9.1.0.4928

When exporting data from a Microsoft SQL Server (2012) I got the following errors:

- The fieldnames are enclosed with " an not `

- "NOT NULL" fields always get an DEFAULT NULL
e.g. "adrid" VARCHAR(36) NOT NULL DEFAULT NULL

I think that all DEFAULTS are transfered as is when read from the sql results:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME='mytable';

I can see that COLUMN_DEFAULT is set NULL and IS_NULLABLE to "false". But the DEFAULT NULL is set anyway...

So the fix has to be something like this(pseudo-delphi-code)

//intialize default string for export line
strDefault := '';

//default is null?
if field('COLUMN_DEFAULT').value <> nil then
begin
//Get quote character from data type
strQuote := GetFieldQuotes(field('DATA_TYPE'));
//set default with/without necessary quotes
strDefault := 'DEFAULT ' + strQuote + field('COLUMN_DEFAULT').value + strQuote;
end
else begin
//set default to NULL only if it is defined as "nullable"
if field('IS_NULLABLE').value then
strDefault := 'DEFAULT NULL';
end;

Sorry for the pseudo-code when the source code is available:

"dbconnections.pas" starting at line 2600:

Original

if Cols.Col('IS_NULLABLE') = 'NO' then
Result := Result + ' NOT';
Result := Result + ' NULL';
if Cols.IsNull('COLUMN_DEFAULT') then
Result := Result + ' DEFAULT NULL'
else begin
Result := Result + ' DEFAULT ' + Cols.Col('COLUMN_DEFAULT');
end;

should be corrected to

if Cols.Col('IS_NULLABLE') = 'NO' then
Result := Result + ' NOT';
Result := Result + ' NULL';
if Cols.IsNull('COLUMN_DEFAULT') then
begin
if Cols.Col('IS_NULLABLE') = 'YES' then
Result := Result + ' DEFAULT NULL'
end
else begin
Result := Result + ' DEFAULT ' + Cols.Col('COLUMN_DEFAULT');
end;

Default value / Null handling fixed in r4931.

For the quoting issue: The double quote is the correct quoting char on a MSSQL server. The backtick: ` is MySQL, not MSSQL syntax.

For the quoting issue: The double quote is the correct quoting char on a MSSQL server. The backtick: ` is MySQL, not MSSQL syntax.

Thanks Ansgar for your quick response and fix :)

I know that the quotes are correct for MSSQL. The problem is that I want to transfer a MSSQL database to MYSQL by selecting the MSSQL as source and the MYSQL as destination.

In that case the quotes should be set to MYSQL quotes. But I don't know if this is one of the expected scenarios...

you will most probably have little luck when trying to import a MSSQL export directly into MySQL; their SQL syntax is not really compatible, there are diffent datatypes, create options etc etc. you could use this converter.

you will most probably have little luck when trying to import a MSSQL export directly into MySQL; their SQL syntax is not really compatible, there are diffent datatypes, create options etc etc. you could use this converter.

You're right... and thanks for the link - seems to be interesting ;)

A data-only export could succeed in HeidiSQL, but only when you export directly to another stored HeidiSQL session. (select "Output:" "Server xyz")