Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Migrating date from MySQL to MSSQL run time error issue

Posted on 2007-04-03
6
Medium Priority
?
458 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 25

Accepted Solution

by:
Lee Savidge earned 1000 total points
ID: 18842252
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
ID: 18842305
Thank you Lee,
I will have a look and let you know how it goes.
Regards,
MickyNin
0
 

Author Comment

by:MickyNin
ID: 18843281
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 25

Assisted Solution

by:Lee Savidge
Lee Savidge earned 1000 total points
ID: 18843298
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
ID: 18843491
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
ID: 18843581
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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
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…

618 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