Solved

Migrating date from MySQL to MSSQL run time error issue

Posted on 2007-04-03
6
448 Views
Last Modified: 2008-01-09
Hi,
I am trying to migrate data from a table in MySQL to MSSQL.
I have been using SSIS Packages in Microsoft Visual Studio to help me do this but i have come across a problem. When pulling the informaion from the MySQL database i get a run time error.
This is the code i have been using:

TRUNCATE TABLE [DataView].[dbo].[CUSTOMER_HISTORY_TEMP];

GO

BEGIN

DECLARE @max_time VARCHAR(255), @remote_query VARCHAR(255)

SELECT @max_time = CONVERT(varchar,ISNULL(MAX(dw_timestamp),CAST('1990-01-01' AS DATETIME)),120)
FROM [DataWarehouse].[dbo].[CUSTOMER_HISTORY];

SET @remote_query = 'SELECT * FROM CUSTOMER_HISTORY WHERE dw_timestamp>="' + @max_time + '" ORDER BY history_id limit '

DECLARE @record_count INT, @while_counter INT, @limit_start INT, @limit_amount INT

SET @limit_start=0
SET @limit_amount=1000
SET @while_counter=-1

SELECT @record_count=count(*) FROM [DataView].[dbo].[CUSTOMER_HISTORY_TEMP]

WHILE @while_counter<@record_count
            BEGIN
                        DECLARE @final_query VARCHAR(255)
                        SET @final_query = @remote_query + CAST(@limit_start AS VARCHAR) + ',' + CAST(@limit_amount AS VARCHAR)
                        INSERT INTO [DataView].[dbo].[CUSTOMER_HISTORY_TEMP]
                        EXEC('SELECT * FROM OPENQUERY(IBBINTRAISP_LOCAL,''' + @final_query + ''') AS subq');
                        SET @while_counter=@record_count
                        SELECT @record_count=count(*) FROM [DataView].[dbo].[CUSTOMER_HISTORY_TEMP]
                        SET @limit_start= @limit_start + @limit_amount
            END
END

--This is the Error i am recieveing when i run this:
OLE DB provider "MSDASQL" for linked server "IBBINTRAISP_LOCAL" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].history_text" from OLE DB provider "MSDASQL" for linked server "IBBINTRAISP_LOCAL".

Any thought would be appriciated,
Thank you.



0
Comment
Question by:MickyNin
  • 4
  • 2
6 Comments
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 500 total points
Comment Utility
Hi,

This looks like you have a data casting problem. Certain field datatypes will need special handling. See this page for more info:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=304025&SiteID=1

Regards,

Lee
0
 

Author Comment

by:MickyNin
Comment Utility
Thank you Lee,
I will have a look and let you know how it goes.
Regards,
MickyNin
0
 

Author Comment

by:MickyNin
Comment Utility
Hi, I have had a look at the forum as suggested by Isavidge.
I have tried to both using index's and checking the data types are the same between MySQL and MSSQL. I am still getting back the Error:

OLE DB provider "MSDASQL" for linked server "IBBINTRAISP_LOCAL" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].history_text" from OLE DB provider "MSDASQL" for linked server "IBBINTRAISP_LOCAL".

I have tried to break down the code to bring back the rows that seem to be throwing me back the problem but there seems to be nothing unusal about these rows and the stragest thing of all is that i could load this package previous to today and never had a proble, and these rows would have been loaded at that time?
Any ideas on what the problme would be greatly appreciated?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 500 total points
Comment Utility
Hi,

It isn't a row that is the problem. The problem is a particular column. In this case the column history_text column. What is the datatype in this column? Which database is it in? Is it in the MS SQL Server one or the other one? Have you tried using DTS on the SQL Server installation to pull the data from the other database rather than try and do it using a stored procedure?

Regards,

Lee
0
 

Author Comment

by:MickyNin
Comment Utility
Hi,

I have checked the data types.In both MySQL  and MSSQL it is  a Text datatype with not null values.
I seen that this had posed a problem if the text datatype allowed nulls so i have changed this?
The reason for using the t-sql procedure was to use a while loop when importing the data into the temp table in MSSQL from MySQL, this avoided the issue of run time errors. I put this SQL into the Control Flow 'Execute SQL Task' Item and then in the data flow I use the Temp table as a source, i do a look up to split updates and inserts and this information flows into the destination table.

I have used this on other bigger tables and it seems to work?
I think your right that it has something to do with the text datatype on history_id, would and option be to change the data type to something esle on both MySQL and MSSQL?

Regards,
MickyNin
0
 

Author Comment

by:MickyNin
Comment Utility
Hi
Do you know is there a difference in the size of the datatype 'TEXT' beetween MySQL and MSSQL could this be a problem?

Thank you,
MickyNin
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now