MySQL To MS SQL 2005 - ODBC?
Jul 18, 2006
Hi,
Posted this at the tail end of another thread but that one appears to have died. Thought I'd try again since my question was a little distinct from that one anyway, but apologies if I've committed forum sacrilege.
I am having a heck of a time trying to migrate a fairly simple MySQL DB to SQL Server I am using SQL Server 2005 Standard Edition SP1.
I tried following MS' instructions in this article:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx
hoping that what worked for 2000 would work for 2005, since the principles seem sound. No luck. I can create the ODBC link, but SQL Management Studio, the '.NET provider for ODBC' option doesn't give me the choice to copy tables - only to write a query.
I tried making sense of the on-line help and was able to create the ODBC connection in the 'Business Intelligence Development Studio' but I have no idea what I am looking at with respect to creating control & data flow in an SSIS package. All I know is that I could do what I am trying to do in SQL Server 2000 quite easily - the MySQL database in question is quite simple (but not small). I am pretty amateurish when it comes to SQL administration, but this seems to me unnecessarily difficult.
I tried the MySQL OLE connector from sourceforge and was able to properly set up a test connection and get the 'copy tables' option, but then when I hit 'next' I get:
MYSqlProv 3.9 failed with no error message available, result code E_ABORT(0x80004004) (System.Data)
Am I missing some stupendously easy way around this, or did I torpedo this client when I promoted moving them from MySQL to MS SQL Server on account of my not being a full-time SQL Server DBA who knows how to use the Business Intelligence Development Studio?
View 5 Replies
ADVERTISEMENT
Mar 6, 2007
The source for this problem can be found http://www.wellytop.com/SQLProblem.zip
This test creates two threads each with a database connection and uses transactions to insert values into the same table.
The objective of this test is to check that a thread cannot read the results from a pending transaction on a different thread.
In effect this checks dirty reads do not happen and transaction locking.
The test runs correctly and displays "PASSED" with MySQL indicating the transaction and threading worked.
When running with MSSQL Express 2005 it reports a deadlock error during a transaction.
It's not really possible to re-run the transaction and I would like MS SQL to operate similar to MySQL, i.e. MySQL waits for the other transaction to finish before the next transaction can operate on those table rows. I'd like to use MSSQL but I am wondering why this error doesn't happen with MySQL and so have, for the moment, chosen to use it as my preferred database solution.
I have experimented with transaction isolation levels and this doesn't seem to solve the problem.
I've tested this with a fresh install of Windows XP SP2 and no firewall turned on.
To run this test with MSSQL Express2005 use the ODBC Data Source Administrator (odbcad32.exe) to create a data source named MyExpressTest and attach this to an empty database that has been created with the default values. Enable the #define MSSQL in the coude otherwise it tests with MySQL.
To run this test with MySQL (to show how this test should work) use the ODBC Data Source Administrator (odbcad32.exe) to create a data source named mySQLNewTest and attach this to an empty database that has been created with the default values. Comment out the #define MSSQL to switch to MySQL mode.
View 6 Replies
View Related
Mar 2, 2006
Hi, we are writting a SQL Server Integration Services package to import data from a MySql database to a Sql Database.
We are using ODBC 3.51 Drivers to connect to the MySql Database in SSIS. The package runs perfectly in design mode. When we schedule the package to run, the package seems to hang about 1/3 of the times.
What can this be. We used to the package and ran it from a Sql Server 2005 to a Sql Server 2005 database. This setup works perfectly. When i'm doing MySql to Sql Server 2005, 1/3 of the times, it does not work.
Can anyone help me with this?
Giovan Gentile
Ayuda Media Systems
View 5 Replies
View Related
Nov 26, 2007
I am testing an application that uses mysql that works 100% fine with Microsoft Windows XP on Windows Vista Premium Home Edition.
Using Vista's ODBC Data Source Administrator I have set up and successfuly tested the DSN Connection to the Database.
However, when I call the set up DSN with the application I get [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified.
I am using the mysql-connector-odbc-3.51.12-win32.
I have tried editing the registry DSN files - the problem still persists.
Does anybody know a way to solve this problem
View 1 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 26, 2007
I have a SQL Server 2005 and am trying to create an OPENDATASOURCE with MYSQL.
I downloaded the MYSQL 3.51 driver and installed it on the SQL Server, but am unable to connect. I keep getting
the OLE DB provider "MySQLProv" has not been registered.
Any help would be greatly appreciated.
Thanks
Susan
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
Nov 5, 2007
Hi!
How can i convert this code to work with MsSQL 2005?
/Tomas
Partial Class skaalb
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim strConnectionString As String
Dim strQuery As String
Dim MyConnection As OdbcConnection
Dim myCommand As OdbcCommand
Dim path As String = Server.MapPath("~/album") & "/"
Dim albName As String = Trim(Replace(txtAlbum.Text, "'", "''"))
Dim folderName As String = Trim(Replace(txtAlbum.Text, "'", "''"))
folderName = Replace(folderName, " ", "_")
Try
If Not My.Computer.FileSystem.DirectoryExists(path & folderName) Then
My.Computer.FileSystem.CreateDirectory(path & folderName)
labelStatus.Text = "Folder <b>" & folderName & "</b> created!"
Dim Beskrivning As String = Trim(Replace(txtBeskrivning.Text, "'", "''"))
strConnectionString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=xxxxxxxx; DATABASE=xxxxxxx; UID=xxxxxxxx; PASSWORD=xxxxxxxxx; OPTION=3"
MyConnection = New OdbcConnection(strConnectionString)
MyConnection.Open()
strQuery = "INSERT INTO tbl_albumet(alb_Namn, alb_Beskrivning, alb_Mapp) VALUES (?, ?, ?)"
myCommand = New OdbcCommand(strQuery, MyConnection)
myCommand.Parameters.AddWithValue("?", albName)
myCommand.Parameters.AddWithValue("?", Beskrivning)
myCommand.Parameters.AddWithValue("?", folderName)
myCommand.ExecuteNonQuery()
MyConnection.Close()
Else
labelStatus.Text = "Folder excist, pick another name!"
End If
Catch ex As Exception
labelStatus.Text = "Unable to create folder!"
End Try
End Sub
End Class
View 1 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
May 19, 2008
Hello
Hope this is an easy one..
Can i install and run SQL Express 2005 sp2 without interrupt the mysql thingy i got on my webserver???
Its on a Win XP sp 2
//Cheers
Jim
View 4 Replies
View Related
Sep 14, 2006
Hi;
I have sql 2005 server in my local area and mysql server on internet..
How can replicate Ms SQL 2005 to MYSQL ? is that possible ? or any possible version this server ?
I try sql 2000 server for replication; publishing starting but then ı take "invalid cursor state " message. ı delete tables msrep7 in mysql then replication starts but if replication stop for any reason , ı must delete table again again...
is this bug in sql 2000 ? how can fix that ?
Thanks...
View 1 Replies
View Related
Dec 12, 2005
Hello! I'm installing the MSSQL 2005.
View 24 Replies
View Related
Feb 27, 2007
Can both be run on the same service I have a asp.net application that need to use the MS sql 2005 and I have another application PHP that requires MySql. Can bot be run ont hte same server at the same time???
View 1 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
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
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
Jun 15, 2007
Please help me, i am having trouble in Adding data in MySQL Database, and even in Getting or retrieving Data as well.
I use the connection string of Visual Basic 6.0 (ODBC 3.51). Nothing goes wrong with the connection but in updating and adding new data in the MySQL Database then...that's it, nothings go Right!
Please help me in this matter, or can somebody to please give me some simple codes like Address Book using the Visual Basic 2005...
pls pls pls pls pls.... thanx a lot!!!
polarmont@yahoo.com
View 1 Replies
View Related
Nov 7, 2007
I am trying to migrate from MySQL 4.1 to MS SQL Express 2005
1. do i need to create an empty db under Express
2. can this be done from within Express by pulling from MySQL into Express or do I need to work from command lines
3. what would be the cleanest way to approach this
I have a MySQL dump and did look at the result from within Express, but it was not an import, I would like to find a way to import if possible that data. Table by table would be fine.
View 1 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
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
Jun 30, 2006
I'm trying to upsize an Access database to SQL 2005 Express. When I follow the wizard and I've tried all ways of connecting to the database I get the following error
Connection failed
SQL State '01000'
SQL Server Error 53
[Microsoft] [ODBC SQL Server Driver][DBNETLIB] ConnectionOpen()).
Connection failed
SQL State '08001'
SQL Server Error 17
[Microsoft] [ODBC SQL Server Driver][DBNETLIB] SQL Server does not exist or access denied
I've tried using local host and typing the exact server connection string from the SQL Express Management tool and nothing is working
How come I can't do this? Everyone says this is easy and straight forward -- not for me.
View 1 Replies
View Related
Oct 18, 2007
hello,
so as you can see i want to migrate from My SQL 5.0 to MS SQL Express. Is there any free tool provided by microsoft or something else i could to use to migrate my databases. I need this cause old web page was developed on PHP and now it's redeveloped on ASP.NET and i have very strange issues with ASP.NET and MySQL Lots of incompatibility and performace degradation, so i would like to try out MS SQL i think it must be better.
please guys help me out.
View 1 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
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 "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
Jan 19, 2007
I am looking for a simple example or guidance on how to move the results of a query from a mysql database to sql server 2005 using SSIS.
It seems rather difficult to do this or at least I am stuggling to figure it out. It was easy to do in DTS, how do you do it in SSIS?
View 3 Replies
View Related
Aug 20, 2007
i've got a database stored on my computer using mysql server 2005, and I've just registered for hosting which uses the Plesk control panel.
In plesk I've setup a database and relevant user. What the easiest way of exporting the whole database on my computer and getting it onto my hosting account?
View 5 Replies
View Related
Mar 18, 2006
Normally I'd go and write a bunch of code for doing it manually... But instead of that, maybe there is a smarter way?
What I know is that you can get some kind of dump out of MySQL but I would be very surprised if that could be directly read into SQL Express ??
So if you have a few step guide to migrate from MySQL to SQL Express, I am all ears (and eyes)!
Thanks!
(Yes I am aware there is some DTS thingy for this but could not find it in the Express folders)
View 6 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
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
View Related
