MySQL Bugs: #16729: MS SQL data transfer fails when server startup option ANSI

Bug #16729	MS SQL data transfer fails when server startup option ANSI_QUOTES is used
Submitted:	23 Jan 2006 16:18	Modified:	29 Jun 2006 16:56
Reporter:	Ivo Lubbers	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S3 (Non-critical)
Version:	1.0.22rc	OS:	Windows (win xp)
Assigned to:	Michael G. Zinner	CPU Architecture:	Any

Description:
Transferring tables with string columns from MS SQL server to Mysql 5 fails
with eror msg
Unknown column '01 DIR.' in 'field list'
when using the migration toolkit with advanced startup variable SQL_MODE=ANSI_QUOTES

How to repeat:
startup var SQL_MODE=ANSI_QUOTES
transfer a table from ms server to mysql containing sting fields.
The inserts.sql shows that all columns are encapsulated with double quotes. When I change that to single quotes, the rows will be inserted without msg Unknown column '01 DIR.' in 'field list'.

Suggested fix:
Use " for encapsulating columns, use ' for encapsulating string-fields

-- ----------------------------------------------------------------------
-- SQL data bulk transfer script generated by the MySQL Migration Toolkit
-- ----------------------------------------------------------------------

-- Disable foreign key checks
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

INSERT INTO "bugdb_dbo"."tb1"("col1", "col2")
VALUES ("first colum", "second column");

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

-- End of script

mysql> INSERT INTO "bugdb_dbo"."tb1"("col1", "col2")
    -> VALUES ("first colum", "second column");
ERROR 1054 (42S22): Unknown column 'first colum' in 'field list'

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html