| Author |
Topic |
|
zeronasium
Starting Member
19 Posts |
 Posted - 2005-03-20 : 23:15:34
|
| insert error, column 7 ('column_name')(dbtype_str), status 6, data overflowWhy am i getting this error? what does it mean? i am migrating data from sql 2000 into mysql using myodbc inside dts. running myodbc v 3.51 & mysql 4.0.18i have exp with both systems. |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-03-21 : 01:53:25
|
| I found that this is a bug in mysql. Check the other thread you started for the details.I recommend you take this up with the mysql folks to see if they can get a fix. they have classified this bug as being non-critical, so it doesn't seem like it is going to get worked on any time soon.http://bugs.mysql.com/bug.php?id=6452-ec |
 |
|
|
zeronasium
Starting Member
19 Posts |
Posted - 2005-03-21 : 18:58:14
|
| i have been thinking about ways to acomplish the task at hand, since this is the last step in my project and i need this done asap. i don't have time to wait for the mysql team to push a release. basicly, i need to get the data out of sql an into mysql. it looks as if i can execute a query in mysql, so i thought about generating insert statements for the data. if i an create a stored proc that creates insert statements, i can run the SP in dts and write the inserts to a flat file. i should be able to execute the inserts through myodbc. thoughts?i am stuck really on what to do here, any help is greatly appreciated.jorge |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-21 : 19:04:04
|
| Here's a stored procedure from Vyas that generates the INSERT statements for your SQL Server data. If the syntax isn't correct for mysql, you'll need to tweak his code.http://vyaskn.tripod.com/code.htm#insertsTara |
|
|
|
zeronasium
Starting Member
19 Posts |
Posted - 2005-03-21 : 19:09:22
|
| i have looked into that script, but when i execute the sp in EM, i get an error saying EM can't parse query text in the query definitions differ screen. when i try to exec the sp in DTS, i can a warning saying the flat file can't be opened.thoughts? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-21 : 19:13:21
|
| Use Query Analyzer to create and execute the stored procedure.Tara |
|
|
|
zeronasium
Starting Member
19 Posts |
Posted - 2005-03-21 : 19:19:57
|
| granted, but i need to execute the SP in dts on a scheduled basis so i can push the data to a product server. so executing it once does nothing for me. i need to exec the sp, store the results, then run the inserts. thoughts? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-21 : 19:24:12
|
| I thought this was a one-time transfer, my bad. You'll have to modify his stored procedure so that it executes the result set. You can do this with xp_execresultset, which is undocumented but you should be able to find examples by googling it and searching these forums.It looks like you'd just modify his sproc here: EXEC (@Actual_Values)Tara |
|
|
|
zeronasium
Starting Member
19 Posts |
Posted - 2005-03-21 : 19:38:32
|
| ok, thanks. i will look into it, but i have reached my full spectrum of sql knowledge so this is becoming difficult. |
|
|
|
zeronasium
Starting Member
19 Posts |
Posted - 2005-03-21 : 19:40:48
|
| also, the sp is not in the master db. where else could it be? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-03-21 : 19:45:28
|
| Well it was created in whatever database you were pointing to when you ran it.Tara |
|
|
|
zeronasium
Starting Member
19 Posts |
Posted - 2005-03-21 : 20:02:56
|
| nono, xp_execresultset |
|
|
|
zeronasium
Starting Member
19 Posts |
Posted - 2005-03-21 : 20:06:30
|
| i can't even find xp_execresultset in any of the dbs. |
|
|
|
zeronasium
Starting Member
19 Posts |
Posted - 2005-03-21 : 20:08:20
|
| do i need to be logged in as a top level user to see xp_execresultset? |
|
|
|
zeronasium
Starting Member
19 Posts |
Posted - 2005-03-21 : 20:16:15
|
| after reading your post, i realized something. i don't want to execute the insert statements, i want to write the statements to them into a file where i can port the data to mysql. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-21 : 21:19:25
|
| You can export your SQL Server data to a text file using DTS or bcp, then use whatever facilities mySQL has to import text files. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-21 : 21:25:38
|
quote:
they have classified this bug as being non-critical
Amazingly, they managed to finally mark this one as critical:http://bugs.mysql.com/bug.php?id=5670From the original comments you'd think accidentally dropping a table was an everyday occurrence, and shit you shoulda made a backup fella.  |
|
|
|
zeronasium
Starting Member
19 Posts |
Posted - 2005-03-21 : 22:15:46
|
quote:
Originally posted by robvolk
You can export your SQL Server data to a text file using DTS or bcp, then use whatever facilities mySQL has to import text files.
That was my last resort. but what i am stuck thinking is how i can export/ftp that data to another machine on a schedule. i would guess a SP could be executed in the dts package to ftp the text files out. No? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-21 : 22:32:37
|
| You need neither a stored procedure nor a DTS package. Create a scheduled job with two Operating System Command steps. The first step runs bcp to export the data. The 2nd step uses the Windows ftp command line utility to ftp the file over to your other server. You could also create a batch file to run both steps, it's up to you. Books Online will explain the details on bcp, and the Windows help file describes the ftp utility. Once the job is set up you can schedule it as you like. |
|
|
|
zeronasium
Starting Member
19 Posts |
Posted - 2005-03-21 : 23:01:23
|
| thanks i will look into it. for now, i will be exporting the data to text files, then ftp them to the mysql box and run a routine on cron.thanks |
|
|
|
|
DTS error, status 6