June 19, 2014 at 8:29 am
I have a SQL Server 2008 R2 database from which I need to export data, to be subsequently read into a MySQL database, by another person, on another machine. From my point of view, everything is working correctly. I use the command:
bcp "Select * from OpenUp.dbo.vwZoologie" queryout "D:\Databaze\Zoologie\Zoologie.csv" -w -t"|" -r"{backslash}n" -S .\SQLEXPRESS2008 -T
in a DOS batch file, called from VBScript routine, triggered every night after hours by a Windows scheduled task. The output from this is subsequently copied to a web server, imported into another instance of SQL Server, which is accessed by a website through a Python script, etc., etc. All this is fully automated and has been working well for several years.
Recently, another person has become involved in the project, and is supposed to get a copy of my data for his MySQL machine. No problem, I have FTP servers all over the place, so I set him up an account from which he can copy this nightly-generated .CSV file and do with it as he pleases. However, the data contains some non-standard ASCII characters, and he has been unable to make the import work properly. I think I am exporting Unicode, and all utilities on all my machines (Notepad, Notepad++, Excel, PsPad, Word, SQL Server bcp...) read the generated file with not the slightest problem. But some of the characters (letters with Czech accent marks) have completely stumped him. He claims to have tried every possible specification of codepages and whatnot, without success, and is trying to tell me there are different flavors of Unicode, and I have to pick the right one for my export.
I don't have much experience with MySQL, so I can't help him much directly, and it sounds to me like his statement is nonsense - from what I know of Unicode, the whole point is that there AREN'T various flavors. There are different collation sequences, to be sure, but those should not have an effect on the actual import of data.
He claims to be out of ideas, and I can't think of what else I might try to help him. Everything works for me, so I have difficulty even experimenting. Some of my experiments have managed to break various parts of my processes, but none have helped him.
Does anyone have any experience with such a situation, or some suggestions on what I might do to shove him in some useful direction?
June 19, 2014 at 9:55 am
He claims to be out of ideas, and I can't think of what else I might try to help him. Everything works for me, so I have difficulty even experimenting. Some of my experiments have managed to break various parts of my processes, but none have helped him.
Are you testing with the same version of python that he's running? Just a thought.
June 19, 2014 at 10:10 am
patrickmcginnis59 10839 (6/19/2014)
He claims to be out of ideas, and I can't think of what else I might try to help him. Everything works for me, so I have difficulty even experimenting. Some of my experiments have managed to break various parts of my processes, but none have helped him.
Are you testing with the same version of python that he's running? Just a thought.
He's not using Python at all - that's only on my web server. He's got some sort of MySQL/PHP setup, with a native import mechanism. Supposedly has a selection of 'all' import formats, but none of them work.
June 19, 2014 at 10:22 am
pdanes (6/19/2014)
patrickmcginnis59 10839 (6/19/2014)
He claims to be out of ideas, and I can't think of what else I might try to help him. Everything works for me, so I have difficulty even experimenting. Some of my experiments have managed to break various parts of my processes, but none have helped him.
Are you testing with the same version of python that he's running? Just a thought.
He's not using Python at all - that's only on my web server. He's got some sort of MySQL/PHP setup, with a native import mechanism. Supposedly has a selection of 'all' import formats, but none of them work.
I see. Just googling around and ran into this http://www.wolflabs.org/2012/08/09/migrating-unicode-data-from-mssql-to-mysql/%5B/url%5D
Doesn't sound like much fun, although the guy says he implemented something, might be worth a look. This page does include this text:
When you specify the -w flag MSSQL will export all data as UTF-16LE (http://en.wikipedia.org/wiki/UTF-16/UCS-2). Unfortunately MySQL’s native support for UTF-16 is limited to UTF-16BE. This inconsistency in the supported UTF-16 encoding by the two database engines renders a direct migration impossible for unicode characters.
So he might be on to something with that "flavors" comment.
June 19, 2014 at 10:34 am
patrickmcginnis59 10839 (6/19/2014)
pdanes (6/19/2014)
patrickmcginnis59 10839 (6/19/2014)
He claims to be out of ideas, and I can't think of what else I might try to help him. Everything works for me, so I have difficulty even experimenting. Some of my experiments have managed to break various parts of my processes, but none have helped him.
Are you testing with the same version of python that he's running? Just a thought.
He's not using Python at all - that's only on my web server. He's got some sort of MySQL/PHP setup, with a native import mechanism. Supposedly has a selection of 'all' import formats, but none of them work.
I see. Just googling around and ran into this http://www.wolflabs.org/2012/08/09/migrating-unicode-data-from-mssql-to-mysql/%5B/url%5D
Doesn't sound like much fun, although the guy says he implemented something, might be worth a look. This page does include this text:
When you specify the -w flag MSSQL will export all data as UTF-16LE (http://en.wikipedia.org/wiki/UTF-16/UCS-2). Unfortunately MySQL’s native support for UTF-16 is limited to UTF-16BE. This inconsistency in the supported UTF-16 encoding by the two database engines renders a direct migration impossible for unicode characters.
So he might be on to something with that "flavors" comment.
Hm, that seems a good starting point. Looks completely gross, but I'll see if we can get it to work. I always thought that Unicode was supposed to be a standard, not just another way to confuse things. Yeah, I know, "Dream on..."
Thanks for the tip.
Pete
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply