Moving Data From Mysql To Sql Server 2005. Does Not Work!!
Nov 9, 2006
Problem: Moving data from mysql to sql server 2005
I am trying to pull data over from mysql to sql server. First the import wizard greys out so I have to put in 1 query at a time which is pain. and second it does not even work! it takes me through the end of the wizard for me to click finish and then says oops it does not work. there was an error!
Anyway i tried going through the ssis route cuz its going to be a nightly job. i used the ado.net odbc connection. It worked but the performance is really not acceptable. it took 5 mins to import 24000 rows where as dts was taking 1 sec to do this. i wish i could use the native mysql odbc 3.51 connector and import. can some one give me step by step instructions on how to do that ?
I hear someone mentioned of using excute sql task which can use mysql odbc 3.51 driver. but since i am new how do i get it to work. say for example in the excute sql task i run a statement like select * from addr. then what?
cuz eventually i want the result to be saved in a sql server table called addr. How can i get the result from that excute sql task and put it inside of an addr table in sql server. should i save the result to a variable of type object. but then how do i get the data from object and tell sql server in the designer that the result contains these columns and it needs to map to these columns in the addr table of sql server.
Very confused. i wish the first option would have given me results which an enterprise ETL gives. but apparently it is too slow that it wont be acceptable in a production envrioment. when i will have millions of rows coming in .
Please anyone can help me in this.
Thanks for the help!
View 1 Replies
ADVERTISEMENT
Apr 9, 2008
Hi gang,
This is a cross-post from the MSDN Managed Newsgroups, which I never seem to get a response on... hoping someone over here can help me out!
I am having a problem and could also use some help understanding something...
We have a DB in MySQL (5.0.51) that I am transferring to SQL Server 2K5
using DTS. I am going through the MySQL ODBC v3.51 driver, and bringing data
over via query.
My query is just SELECT * FROM <table>, and it take approximately 1 minute
to run on the MySQL server.
First, the problem... the source table is 5 columns (2-ints, 1-float,
2-varchar(255)s) and 22M rows. Looks to me like about 600MB of data.
When I go through the DTS wizard it runs for about an hour and then I get a
"MySQL client out of memory" error. After a bit of research, I found a
switch in the ODBC driver to use forward-only cursors, which seems to have
alleviated that problem, however... and this is where I need help in
understanding the process, when I get to the page in the wizard where I enter
the query, I click next, and the machine gets busy for an hour. Looking at
the processes on the MySQL server, I can see the entire result set is
streaming across the wire to my SQL Server box. Then the page comes back,
and I specify the table to import into, click Edit Mappings... and wait for
another 30+ minutes for the mapping screen to come up. Once I enter the
mappings, I can complete the wizard, but the Performing Operation page takes
about an hour again to complete.
The duration of all of this seems ridiculous to me... especially in moving
such a (relatively) trivial amount of data (MySQL server has 100s of gigs of
hard drive space available, plus 8GB or RAM, and my SQL Server box has 3GBs
of RAM and plenty of drive space... plus we are running gigabit ethernet).
It appears that the query page is retrieving all of the rows just to take me
to the page where I map the columns. That, assumedly, is the case because on
that page I can Preview the data. Is there any way to supress this?
Is there anything anyone can suggest to speed this whole ordeal up?
I am wondering (haven't tried this yet), if I save the DTS Package and run
that if it will execute more rapidly because much of the work is already done
by that point.
Any info would be greatly appreciated!
Cheers,
Chris
View 3 Replies
View Related
Jun 28, 2007
Hello,
Finally making the move to ASP.NET! I've been advised that to begin with it is probably best to start using SQL Server 2005 with ASP.NET. All my sites are currently using MySQL. Can anyone advise a way for me to import all the data from a MySQL database to a SQL Server 2005 database. Apologies if this isn't directly related to ASP.NET but any help would be greatly appreciated.
Thanks
View 2 Replies
View Related
Aug 1, 2007
hey all,
i want to know Equivalent of Load Data Infile of MySQL in SQL Server 2005?
in mysql we can direclty load file with Load Data infile query.
my question is how to do this in sql server 2005?
thanks.
View 6 Replies
View Related
Jan 22, 2008
HI All,
I want to pull data from mysql dbms to sql servr 2005, i have wrote the following codes but it takes more than an hour and half which is not visible. Thus is there any consideration to consider and reduced time it takes. For your information i am going to use SSIS packags, there is no any tranformation, it is direct dump.
Here is the code i am using,
SELECT *
FROM
OPENQUERY (Server_1,'
SELECT
t3.Column11 as Column1,
Column12 as Column2,
Column13 as Column3,
Column14 as Column4,
Column15 as Column5,
Column16 as Column6,
Column17 as Column7,
Column18/1000 as Column8
FROM
table1 t1
INNER JOIN
table2 t2
ON t1.ColumnId = t2.columnID
INNER JOIN
Table3 t3
ON t2.columnId = t3.columnID
WHERE t1.Column4 > Sometime ')
View 11 Replies
View Related
Nov 9, 2007
I made a page for a baseball league a couple of years ago using PHP and MySQL, and decided this winter to switch it to ASP.Net and SQL Server Express that comes with VWD 2005 Express Edition.
Anyways, I ran into a problem when trying to convert the query I made with MySQL into SQL Server.
My old code was something like this:
SELECT homeScore, visitorScore, ( homeScore > visitorScore ) AS homeWins, ( visitorScore > homeScore ) AS visitorWins FROM Schedule
This worked fine in MySQL, and returned the home team's score, visiting team's score, and then returned a 1 in homeWins if the home team won. Now, when I try running this query in SQL Server Express, I get an error saying "Invalid column name 'visitorScore > homeScore' ".
Can anyone please help me with what I should be doing differently so this works in SQL Server Express?! Thanks in advance!!!
View 17 Replies
View Related
Oct 27, 2007
I'm trying to use linked server to import big amount of data(around 9 million rows) from mysql to a sql 2005 table.
I plan to create a stored procedure and use job agent to run it at off-peak time everyday. The SP is like:
.....
Truncate table sqltblA;
Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB')
.....
But when I tried to CREATE the above SP from management studio, seems the sql server was trying to get all data from table mytblB and ended up with failure after several minutes hang. The error message is sth. like "running out memeory". BTW, during that several minutes hang, I can't query mytblB using mysql's tool as well. it seems to me that table got locked.
However if i try to change the returned result to 100,000 rows by changing script to
Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB Limit 100000')
The SP could be created without any problem and running the SP could get the data in table sqltblA.
But that's not what I want, I need all rows instead of a certain amount of rows.
Does anyone know what's going on there when I try to CREATE that SP and any solution to it?
Plus I tried to use SSIS as well. When I'm configuring the DataReader source, which uses ADO.NET's ODBC option to connect to mysql, if its sqlcommand is "select * from mytblB" without the Limit key word, then the configuration page will hang there as well and table mytblB is not accessible by using mysql tool. I'm just sick of wasting time on that and that's why I chose to use SP istead of SSIS. But same result. :-(
View 1 Replies
View Related
Oct 9, 2006
Hi
I am wishing to
* take a table of data into a C# CLR procedure and store it in an array
* take a second table of data into this procedure row by row, and return a row (into a third table) for each row (and this returned row is based on the data in the array and the data in this row).
I am new to CLR programming, and pulling my hair out at the moment. I€™m sure that what I€™m trying to do is simple, but I am not making any progress with the on-line help and error messages L. I have now described what I am trying to do in more detail, firstly in English, and then in a T-SQL implementation that works (for this simple example). I€™m looking for the C# CLR code to be returned €“ containing preferably two parts:
* the C# code and
* the CLR code to €˜make it live€™
Since I am not sure where my coding is going wrong. (I think it should be possible to read in the one table, and then loop through thte second table, calculating and returning the necessary output as you do the calculations...
Problem in English
Consider a situation where there are three tables: DATATABLE, PARAMETERTABLE and RESULTSTABLE.
For each row in PARAMETERTABLE, I will calculate a row for RESULTSTABLE based on calculations involving all the entries form DATATABLE.
I am wishing to do this in a C# CLR for performance reasons, and because the functions I will be using will be significantly more complex, and recursively built up.
Consider the following table structures
DATATABLE €“ [i int, datavalue real]
[1,12.5]
[2.10]
[3.14]
[4,17.5]
(where numberofrows is 4)
PARAMETERTABLE [parametera real, paramaterb real]
[10,2]
[11.7,1.1]
RESULTSTABLE [resultvalue real]
[20.5]
[18.26]
The values of the results table are calculated as the
sum (for i = 1 to numberofrows)
of
(Parameter1+i*parameterb-datavalue)^2
Which leads to the values shown.
T-SQL Implementation
-- Set up database and tables to use in example
USE master
GO
CREATE DATABASE QuestionDatabase
GO
sp_configure 'clr enabled', 1
GO
USE QuestionDatabase
GO
RECONFIGURE
GO
CREATE TABLE DataTable (i INT NOT NULL, DataValue REAL NOT NULL)
GO
CREATE TABLE ParameterTable (ParameterNumber INT NOT NULL, ParameterA REAL NOT NULL, ParameterB REAL NOT NULL)
GO
CREATE TABLE ResultsTable (ParameterNumber INT NOT NULL, ResultsValue REAL NOT NULL)
GO
--Initialise the Tables
INSERT INTO DataTable (i, DataValue) VALUES (1,12.5)
INSERT INTO DataTable (i, DataValue) VALUES (2,10)
INSERT INTO DataTable (i, DataValue) VALUES (3,14)
INSERT INTO DataTable (i, DataValue) VALUES (4,17.5)
INSERT INTO ParameterTable (ParameterNumber, ParameterA, ParameterB) VALUES (1, 10, 2)
INSERT INTO ParameterTable (ParameterNumber, ParameterA, ParameterB) VALUES (2, 11.7, 1.1)
-- The TSQL to be rewritten in C#, to produce the Output as hoped
INSERT INTO ResultsTable (ParameterNumber, ResultsValue)
SELECT ParameterNumber, SUM((parametera+i*parameterb-datavalue)*(parametera+i*parameterb-datavalue)) AS b
FROM DataTable
CROSS JOIN
ParameterTable
GROUP BY ParameterNumber
-- Output as hoped
SELECT * FROM DataTable
SELECT * FROM ParameterTable
SELECT * FROM ResultsTable
-- which produced
1 12.5
2 10
3 14
4 17.5
1 10 2
2 11.7 1.1
1 20.5
2 18.26
-- but I hope to do the same with something like:
CREATE ASSEMBLY *** FROM 'C:***.dll'
CREATE PROCEDURE GenerateResultsInCLR(@i int, @r1 real, @r2 real)
RETURNS TABLE (i int, r real)
EXTERNAL NAME ***.***.***
EXEC GenerateResultsInCLR
This is a simple example, that can be easily written in T-SQL. I am looking to develop things that are recursive in nature, which makes them unsuited to T-SQL, unless one is using cursors, but this becomes very slow when the parameter table has 1m records, and the data table 100k records. This is why the datatable must be read in once, and manipulated many times, and the manipulation will need to be in the form of a loop.
Thanks very much for your help.
Go well
Greg
View 8 Replies
View Related
Feb 9, 2007
Hi All,
We have a Progress DB in our Company. We are trying to move all the data to SQL Server 2005. When I try to run the Import Data task from SQL Server 2005 the radio button called "Copy data from one or more tables or views" is getting disabled and it is asking me to write a SQL script to copy all data from 120 tables.
I am using a .NET Framework Data Provider for ODBC when I run the Import Data task from SQL Server 2005.
I am using the DataDirect 4.1 32-Bit Progress SQL92 v9.1D ODBC Driver to connect to Progress DB. I am getting a connection but the copy feature is getting disabled.
Can anyone please help me to resolve this issue, or even if you provide me with some pointers that will be really helpful.
Thanks
Suresh
View 1 Replies
View Related
Oct 17, 2006
I am trying to load data from an Excel spreadsheet file into SQL Server 2005 Express. I understand that DTS is the best tool for doing this but from my research it appears that DTS is not available with the Express edition and the import wizard that does come with Express is not well suited for this type of conversion.
Does anyone have any suggestions for how to achieve this objective? Thanks for any help you can provide.
View 7 Replies
View Related
Jun 7, 2006
Hi,
I'm a newbie on SSIS and am trying to grasp my way through this.
I am trying to copy data from a Sql Server 2000 database to a simplified table in Sql Server 2005 database.
What I want is to move the data to a staging table, then drop the main
table and rename the staging table to the main table, to minimize the
down-time of the data. I can't get the workflow to work, because the
staging table has to exist when I run the package. I thought I could
use an "Execute SQL" task to generate the table before I would run the
task, but that doesn't work. Am I going about this the wrong way? Is
there an optimal solution to this problem so my data can be accessible
as much as possible.
Regards,
Atli
View 5 Replies
View Related
Feb 9, 2007
hi, does anyone know how I can make a connection to a 2000 db thru ssis?
View 8 Replies
View Related
Mar 19, 2004
Hello,
I recently moved from MySQL (after 1 year of experience) to MS SQL Server as my company moved from MySQL to MS SQL Server so I am in the learning stage.
I just wanted to know how you start up logging in SQL Server. I mean I want to log all the queries that are being executed against the server. I dont mean the binary logs but Actual query logging. Since I will be the admin I want to queries executed against all the databases.
Is it possible?
Regards
Karam
View 4 Replies
View Related
Oct 27, 2007
I'm trying to use linked server to import big amount of data(around 9 million rows) from mysql to a sql 2005 table.
I plan to create a stored procedure and use job agent to run it at off-peak time everyday. The SP is like:
.....
Truncate table sqltblA;
Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB')
.....
But when I tried to CREATE the above SP from management studio, seems the sql server was trying to get all data from table mytblB and ended up with failure after several minutes hang. The error message is sth. like "running out memeory". BTW, during that several minutes hang, I can't query mytblB using mysql's tool as well. it seems to me that table got locked.
However if i try to change the returned result to 100,000 rows by changing script to
Insert Into sqltblA(col1, col2, col3)
Select col1, col2, col3
From OpenQuery('mysql', 'Select col1, col2, col3
From mytblB Limit 100000')
The SP could be created without any problem and running the SP could get the data in table sqltblA.
But that's not what I want, I need all rows instead of a certain amount of rows.
Does anyone know what's going on there when I try to CREATE that SP and any solution to it?
Plus I tried to use SSIS as well. When I'm configuring the DataReader source, which uses ADO.NET's ODBC option to connect to mysql, if its sqlcommand is "select * from mytblB" without the Limit key word, then the configuration page will hang there as well and table mytblB is not accessible by using mysql tool. I'm just sick of wasting time on that and that's why I chose to use SP istead of SSIS. But same result. :-(
View 11 Replies
View Related
Feb 13, 2008
Please help - I am dealing with data transfer from multiple tables to multiple tables - what is the best practice to get this done? So far I have my connection to MySql figured out and I am using a DataReader source to get the data. What is the approriate Data Flow Transformation to pump data into three different tables in MS Sql 2005 database from three different tables in MySlq database? So far I was able to figure out how to do this for one table only...
Thanks,
Galina
View 12 Replies
View Related
Apr 18, 2007
Hi experts,
I 'm trying to get countNumber from a select statement in MySQL source. Something likes this:
select @countNumber = count(*) from tableName where condition_1 = xxx
I use ExecuteSQLTask to get the result set with result name @countNumber along with ADO.NET connection type.
I 've installed mysql-connector-net-5.0.5 cause the source is MySQL 5x.
And I got a task failed error which is described below:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "new_process": "Result binding by name "@new_process" is not supported for this connection type. ".
Please help me to fix this error, thanks alot
Best reguards,
View 8 Replies
View Related
Apr 26, 2007
Hi guys,
I'm a newbie DBA and i'm trying to create a package that would extract data from MySQL and inserts them to a SQL 2005 Server. I'm quite new to this SSIS and would like to ask help from you to help me go through with this.
I hope you guys can help me with this.
Hoping to hear from you soon.
Thank you so much.
Kind regards,
Neil
View 7 Replies
View Related
Apr 26, 2007
I've been asked to migrate a db in MySQL to SQL Server 2005. Since I'm only familiar with sql server 2000 and before, I dont' know where to begin looking for the information I need.
Can someone point me to what I would search for in SQL Server 05 for this (is there any new features here I should be aware of that might make this easier?)
If anyone has done this, what are the gotcha's or things I should be aware of.
Thanks for any input
View 1 Replies
View Related
Feb 21, 2007
We need to upgrade our HR system from SQL 2000 to SQL 2005. Support for SQL 2000 ends 12/31/2007.
We have a third party vender that do not currently support SQL 2005. Employee's data is transfered from our HR database to the third party database for the purpose of yearly performance evaluation on a daily basis.
Both databases are currenlty on a SQL 2000 server.
Can data be transferred from a SQL 2005 database to a SQL 2000 database?
View 4 Replies
View Related
Jan 19, 2007
Hello. I have the following problem. I have a database in mysql and i want to migrate in ms sql server 2005. How can i do that? I have downloaded and installed MySQL Connector/ODBC .
View 8 Replies
View Related
Feb 20, 2007
Hi guys,
im trying to develop a web application that uses a backend database, however im not quite sure what db backend to use. What are the diffrence between sql server express and mysql.. which is better to use in terms of small enterprises.? what are their limits..?
thanks you very much
View 4 Replies
View Related
Aug 29, 2007
I am having trouble connecting to a MySQL database in SQL Server 2005. How can this be done in the easiest fashion?
View 7 Replies
View Related
Jul 27, 2007
Can anyone recommend me how I can convert MySQL database to MS SQL server 2005 database? I am new to the SQL server 2005. Is there any ODBC I can use? Thanks in advance
View 3 Replies
View Related
May 30, 2006
How to move some tables with data & procedures etc from 1 database to another in sql server 2005 express edition.
i did by scripting but i transfer tables and procedures and not data
data is the problem.
tnx
View 1 Replies
View Related
Feb 12, 2007
Good Morning
Has anyone successfully used cherry's oledb provider for MYSQL to create a linked server from MS SQLserver 2005 to a Linux red hat platform running MYSQL.
I can not get it to work.
I've created a UDL which tests fine. it looks like this
[oledb]
; Everything after this line is an OLE DB initstring
Provider=OleMySql.MySqlSource.1;Persist Security Info=False;User ID=testuser;
Data Source=databridge;Location="";Mode=Read;Trace="""""""""""""""""""""""""""""";
Initial Catalog=riverford_rhdx_20060822
Can any on help me convert this to corrrect syntax for sql stored procedure
sp_addlinkedserver
I've tried this below but it does not work I just get an error saying it can not create an instance of OleMySql.MySqlSource.
I used SQL server management studio to create the linked server then just scripted this out below.
I seem to be missing the user ID, but don't know where to put it in.
EXEC master.dbo.sp_addlinkedserver @server = N'DATABRIDGE_OLEDB', @srvproduct=N'mysql', @provider=N'OleMySql.MySqlSource', @datasrc=N'databridge', @catalog=N'riverford_rhdx_20060822'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'DATABRIDGE_OLEDB', @optname=N'use remote collation', @optvalue=N'false'
Many Thanks
David Hills
View 7 Replies
View Related
Apr 5, 2007
Hi all,I am developing a web application and the back end is MySQL database.
Now I want to shift all the data to SQL Server 2005.
Is there any method to do it?
Thanks
Tomy
View 1 Replies
View Related
Aug 1, 2007
hey all,
i want to know Equivalent of "LIMIT" of MySQL in SQL Server 2005?
in mysql we can direclty get data using LIMIT clause.
my question is how to do this in sql server 2005?
thanks.
View 6 Replies
View Related
Mar 20, 2007
Does anyone know of a reference site where I can find a reference table to get a better idea of data type conversions that I should be using?
I have a MySQL 5.0 database which had a lot of tables (mostly empty) that I already have gotten transferred to SQL Server 2005. However, I am suspicious of some of the data type conversions that SQL Server did.
I would really like a good web site to bookmark to help me with this if there is such a reference. Can anyone help?
If not, the most specific example I have right now is a MySQL column that is expecting to accept currency and the MySQL data type is "Double". SQL Server 2005 translated this as a "float" data type. I normally use a "decimal" data type.
- - - -
- Will -
- - - -
http://www.strohlsitedesign.com
http://www.servicerank.com/
View 2 Replies
View Related
Oct 3, 2007
I have a database that is in MySQL and would like to import it into SQL Server 2005 Express.
Can this be done? Any tutorials anywhere?
Thanks.
View 7 Replies
View Related
Jul 25, 2007
my package contains
Two connections ==> one is using OLEDB connecting to SQL server 2005 and the other using ADO.NET's ODBC option to connect to mysql;
Two "Execute SQL Task" ==> one gets maximum ID(bigint) from a SQL server table and the other gets the maximum ID(unsigned) from a mysql's table and bind them to two variables ID1(string) and ID2 (int 64). http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1902297&SiteID=17&mode=1 ;
And one DataFlow whose data source is DataReader Source which queries mysql "select * from table where ID > cast(@ID1 as signed) and ID <= cast(@ID2 as signed) ". To debug, I put a DataViewer between Data Flow Source and Data Flow Destination.
Run the package, I found no data coming into DataViewer and no error message as well. However if I remove the "<=" sign and change the DataReader Source query to "select * from table where ID > cast(@ID1 as signed)", It works perfectly. Initially I thought it's variable @ID2's issue. But if I replace "<=" sign with ">=" sign in the query ==> "select * from table where ID >= (cast(@ID2 as signed) -100)", there is no problem. After tried many times, I found that query just doesn't work with "<" sign or "between". Plus the result of running package tells it's successful but just no records got inserted into Data Flow Destination.
Anyone could help? Thanks!
View 3 Replies
View Related
Sep 4, 2007
Hi All,
We will replace our website which currently build in php and mysql into .net and sql server 2005. What is the best way to migrate all the user account from current database in mysql into sql server, also considering that there is different database table and fields?
Thanks in advance.
View 2 Replies
View Related
Mar 28, 2008
problem is I don't have unfettered access to the MS SQL Server(7), so I need to send instructions on how to do it to a client.
After reading up a little, I figured "select view into ##tmp_table", and then dump the table, which, if I'm right, means I'll have an SQL dump that I can quite easily import into MySQL.
Problem,: What's the syntax to dump that temp table, or is there a better way to import the data into MySQL
TIA
View 5 Replies
View Related
Sep 18, 2006
Hi All
I'm trying to import data from mySQL database to SQL server 2005. I installed mySQL odbc driver (3.51) and configured system DSN. However when I start SQL server import/export wizard mysql is not an option in data source dropdown.
What do I miss?
Thanks,
Vlad.
View 2 Replies
View Related
