distal-attribute
distal-attribute
distal-attribute
distal-attribute

Table Export to Database

User, date Message
Written by everydayfam
2 years ago
Category: Import/Export
6 posts since Thu, 31 Jan 13
I'm trying to export a table (with data) from an existing MS-SQL database to a new MySQL database. I'm selecting the Create checkbox when I perform the export.

The process fails with a syntax error. From what I can tell, this is occurring due to the double-quotes that are being placed around each object reference.

I can export to a SQL file and use find & replace in a text editor to remove all the " in the file and it loads properly into the MySQL database.

I was wondering if there was a better way to accomplish this, to save me those extra steps.

Thanks in advance.
Written by jfalchMoney, Euro
2 years ago
406 posts since Sat, 17 Oct 09
what exactly is an object reference ? can you give an sql example (with dblquotes still in) ?
Written by everydayfam
2 years ago
6 posts since Thu, 31 Jan 13
Hello, my apologies about being vague (and probably using the wrong terminology). Here's what is getting exported :

CREATE TABLE IF NOT EXISTS "checklist_track" (
"checklist_track_id" INT NOT NULL,
"member_id" INT NOT NULL,
"group_id" INT NOT NULL,
"checklists_item_id" INT NOT NULL,
"created" DATETIME NOT NULL,
"updated" DATETIME NOT NULL,
"modified_by" VARCHAR(100) NOT NULL);

That is kicking up a syntax error from MySQL. When I remove all the " it processes successfully.

Thanks for looking at this, let me know if you have any other questions.
Written by everydayfam
2 years ago
6 posts since Thu, 31 Jan 13
My research seems to indicate that setting the SQL mode to ANSI_QUOTES would resolve this. But I'm not sure how to do this, or if it can be done within the Export to a DB process within HeidiSQL. See the below URL for more info:

http://dev.mysql.com/doc/refman/4.1/en/server-sql-mode.html#sqlmode_ansi_quotes
Written by ansgar
2 years ago
5023 posts since Fri, 07 Apr 06
SQL exports in HeidiSQL work fine as long as you do not interchange data between MySQL and MSSQL. It's my goal to make this as compatible as it can be, but currently this is not done 100%. But who knows, probably setting ANSI mode also does it, although I guess you won't be able to import more complex data types, and indexes.

Fire this query on your MySQL server, just before you call the export dialog:

SET sql_mode:='ANSI'

Written by everydayfam
2 years ago
6 posts since Thu, 31 Jan 13
Gotcha, I thought that this was the preferred way to convert from MSSQL to MySQL. Is there a better method I should be using?

If not, I'll use the code you provided. Thanks for the support and for the great product!
 

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