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
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
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
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
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?
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
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...
Hello! I'm installing the MSSQL 2005.
View 24 Replies View RelatedCan 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 RelatedHello. 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 RelatedHi 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
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. :-(
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 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
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.
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
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
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.
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.
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
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
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.
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/
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.
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?
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?
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)
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,
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!
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.
Hi Guys,
well as you can see from my thread SQLServer is new to me i am used to mysql and i c'ant find the equivalent anywhere on the net... Seams easy enough but i have been at it for 6 hrs and i give up... I am just making a quick database view tool. So please tell me.
How do i load the all tables of a database via an sql command?
Since it took me a while to find this i thought migth as well slap ip somewhere so here is the command to load all data bases... In any case worked for me so...
- > select * from master.dbo.sysdatabases;
And where in the ... can you find a reference to all sql server commands? ... Please.
Thanks for your help
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 ')
Hi,
In MySQL,
"GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.1' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;"
This gives the permission to 'root' user from '192.168.1.1'. Another user or another ip couldn't connect to this server.
In SQL Server 2005 Express, can I implement like above query statement?