1

I have seen tons of post on this, but I am not very familiar with the process and nothing has worked.

My basic problem is I am trying to get data from a Microsoft SQL database (Using Microsoft Management Studio 2008) to convert and use in a MySQL database (using MySQL Workbench)

I have tried to dump the .sql file but when I try to import into MySQL Workbench I get errors about it not being in the UTF-8 format. I tried several ways to get it to be that encoding (such as notepadd++ converting I saw suggested everywhere), but nothing seemed to work.

If I run it, I get this error: 

ERROR: ASCII '\0' appeared in the statement, but this is not 
allowed unless option --binary-mode is enabled and mysql is 
run in non-interactive mode.

Again I looked up fixed for this, but could not get it.

I have tried a few options, just no success, so looking for some ideas or guidance with this area I do not have much experience in.

UPDATE: The problem now is that the sql file I export from Management Studio is not accepted as a valid query in Workbench.

For example the brackets are not accepted and such.

blapaz
  • 334
  • 2
  • 11
  • How did you "dump the `.sql` file" ? If you want to get the schema and data scripted using SSMS 2012, follow this article: https://sqlserver-help.com/2013/12/13/did-you-know-you-can-generate-insert-statement-with-data-using-management-studio/ - this should be a good basis for the transfer (some data types etc. might need adjustment though). – Lucero Nov 03 '16 at 14:28
  • Dialect of Sql Server and MySql are different. ASCII is not defined in MySql, but you can find in the Sql Server script as directive – Joe Taras Nov 03 '16 at 14:28
  • I did these steps. Right Click on database -> Tasks -> Generate Script. That leads to creating a .sql file. I am confused though because there is no actual data in it. – blapaz Nov 03 '16 at 14:30
  • @blapaz Yes this only created the schema. See my edited comment above: https://sqlserver-help.com/2013/12/13/did-you-know-you-can-generate-insert-statement-with-data-using-management-studio/ – Lucero Nov 03 '16 at 14:32
  • So that will work perfectly to get the data in there, thank you for that! I just need to get that error to go away also. – blapaz Nov 03 '16 at 14:38
  • MySQL doesn't really understand Unicode, either in script or for its data. SQL Server on the other hand uses Unicode (UTF-16). MySQL didn' recognize the Unicode file and thought that `\0` is an ASCII character instead of the first byte of a Unicode character. MySQL treats everything as ASCII and considers UTF8 just another codepage. – Panagiotis Kanavos Nov 03 '16 at 14:43
  • So when I import into Workbench, I need to select that the data is UTF-16 basically? – blapaz Nov 03 '16 at 14:44
  • Yes. Or save your script as UTF8 - that's just an option in SSMS. What you did was just an action in SSMS, you didn't dump anything. You got a file that you can save any way you want – Panagiotis Kanavos Nov 03 '16 at 14:45
  • PS wait until you try to find how to perform a differential backup in MySQL – Panagiotis Kanavos Nov 03 '16 at 14:46
  • I tried to save the script as UTF-8 and I had issues. Any suggestions on an efficient way to do this? – blapaz Nov 03 '16 at 14:47

1 Answers1

0

I bet your encoding is not supported in MySQL WorkBench.

You can change the encoding when saving a .sql file.

  1. Select File|Save.sql As to invoke the save as dialog.
  2. Notice that the Save button on the lower right hand side has a drop down icon to indicate options.
  3. Select the drop down icon and choose the "Save with Encoding" context memu item.
  4. Select an encoding that works in MySQL Workbench.
Ross Bush
  • 14,648
  • 2
  • 32
  • 55
  • I was trying to do this in Notepad++ but just doing it in Notepad allowed this to show up, thank you. However the import still does not work. – blapaz Nov 03 '16 at 16:17