Incorrect syntax near ')' when using DTS 2

[thorntong]

Hi,
I'm in the process of trying to convert a MySQL DB (ver 3.0 on Linux) over to a SQL 2000 DB (Windows) and I'm using the DTS (Data Transformation Service) built into SQL 2000. When I run the utility, it is successful in copying over 15 of 17 tables. But what I'm getting is a Incorrect syntax near ')' error as well as an invalid object name on the same 2 tables. I believe that the conversion process is having trouble converting some of the table fields, but this is still unclear. Does anyone know of ways that I can troubleshoot this and find out what is causing the errors? Any and all assistance would greatly be appreciated.
Kind Regards,
Glenn

 

[SimonSellick]

Hi Glenn,

I haven't converted from MySQL so treat my comments with the contempt they deserve, but...

- non-standard column names are a likely source of error (anything other than letters, digits and underscores)
- are there constraints on the source tables and if so, does removing them help?
- are there any funny data types that might not map easily, or data types that have a different 'shape' of specification in MySQL from that of the corresponding type in SQL Server?

If none of that helps, can you post the table structures?

Regards,
Simon

 

[jdemmi]

My guess is that at least one of the records has a character which is interferring with the insert statement. Probably ) as specified but it could also be a ' or a ".

Can you confirm that one of the columns in the offending table contains such data?

In terms of troubleshooting you can usually pinpoint the record in question by transferring the table by itself and noting which row it errors on (DTS usually displays what row it's currently processing).



-- Jason
"It's Just Ones and Zeros

 

[thorntong]

Simon and Jason,

You both bring up extremely good points to look at. As I'm not a Linux programmer and the person who setup this database is no longer with our organization and I'm having to do this on my own, as I'm sure you've both been in situations such as this. One of the things I started looking into is what Simon brought up about the table/column names. I looked at both troublesome tables and have found that the person who designed it did not use underscores for spaces between words and I also found characters such as back slash (\) (for example: Phone\Extension) and single quotes (') (for example: current adl's). But then Jason also has a good point in that there could be a right paren ')' sitting in one of the column fields that does not convert nicely because the new DB field is checking the validity of what kind of data is being copied. A check that should have been implemented into the application from the start. Using your suggestions, I'm going to dig into this deeper and then report back in this post as to what I found. Thank you guys for your quick and prompt responses and ideas.

Kind Regards,
Glenn

 

[thorntong]

Hi everyone,
This is what I found to work for my issue. What I did was to use Microsoft Access and use the import table function to copy all the data tables from the MySQL database. I then used the export function within Microsoft Access to move them over to the SQL server. This process was extremely 'simple' and did not cause any data integrity problems. I do want to thank both Jason and Simon for their great suggestions and prompt response.

Kind Regards,
Glenn