A Lump Of Madness

Oh, the pain, the agony. All this cross-database works are making me nuts!

I've got a "duuhh" moment yesterday upon realizing that the SQL Server I'm using on my machine is MSDE (free version of SQL Server), which have a size limit of 2GB per single database. Dumping the 1.7 million row Informix table into it just wouldn't work, it throws this error: --- The task reported failure on execution. Could not allocate space for object 'mySqlTable' in database 'mySqlDb' because the 'PRIMARY' filegroup is full. ---

My initial plan to get all the data, and then do the filtering using T-SQL, is doomed. I have to do the filtering there in Informix, and insert only the data I need in SQL Server. After trying to work it out using the DTS, I decided that the time-constraint won't allow me to learn to use it effectively. This finally force me to retreat into my comfort zone: use C# (VB.NET is equally comforting) to get the job done!

Ok, on to Plan B, use ADO.NET to read the data from Informix, dan write it into SQL Server. I've finished the script, and running it right now. I did got some error. I am eager to tell you about it, but I think this blog entry is already long, so I'll save the actions for tomorrow. To be continued :)