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.
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 .
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.
We have recently added a new file group and file on a new drive. We have tested it by moving a small table to the new file group. We would like to relocate a new table to this file group but the table is about (we estimate) 75GB. My question is this: How long can we expect the transfer of data from the current file group to the new one for this table? I understand that depending on our hardware the answer may vary but does anyone have a rough estimate?
The current current (primary) file is located on a DELL SAN and the new secondary group is on a EMC 4700 both are connected via fiber channel.
Also a bonus question would be: Does a "normal" database backup created as a maintenance plan backup the seconary data as well into the BAK file?
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
I have an internal Project Management and Scheduling app that I wrote internally for my company. It was written to use MySQL running on a Debian server, but I am going to move it to SQL Server 2000 and integrate it with our Accounting software. The part I am having trouble with is the user login portion. I previously used this:
PHP Code:
$sql = "SELECT * FROM users WHERE username = "$username" AND user_password = password("$password")";
Apparently the password() function is not available when accessing SQL Server via ODBC. Is there an equivalent function I could use isntead so the passwords arent plaintext in the database? I only have 15 people using the system so a blank pwd reset wouldn't be too much trouble.
im trying to convert this table for a friend 1 CREATE TABLE adminmessage ( 2 adminmessageid INT UNSIGNED NOT NULL AUTO_INCREMENT, 3 varname varchar(250) NOT NULL DEFAULT '', 4 dismissable SMALLINT UNSIGNED NOT NULL DEFAULT '0', 5 script varchar(50) NOT NULL DEFAULT '', 6 action varchar(20) NOT NULL DEFAULT '', 7 execurl mediumtext NOT NULL, 8 method enum('get','post') NOT NULL DEFAULT 'post', 9 dateline INT UNSIGNED NOT NULL DEFAULT '0', 10 status enum('undone','done','dismissed') NOT NULL default 'undone', 11 statususerid INT UNSIGNED NOT NULL DEFAULT '0', 12 PRIMARY KEY (adminmessageid), 13 KEY script_action (script, action), 14 KEY varname (varname) 15 ) and was wondering how to do it when mssql doesn't have enums :(
I just started developing my site locally using SQL Server and am planning to move it to our work's MySQL server. Is there anything that I should be aware of before I continue any furthur? Will I lose any data or is it even an option to move from SQL Server to My SQL???
I am working for a client who needs to move database from MySQL application to MS SQL SERVER 7.0. Is there anybody who has done it before? I need some help about how to load data from one system to another system.
Hi there, I'm hoping someone out there can help me.
At my old job, I developed web applications that were based off of a MySQL server. I used PHPMyAdmin and learned the PHP coding, and although not formally trained in DBA or anything, I became very comfortable with using it and building database applications. I did everything through SQL/PHP script coding or through PHPMyAdmin.
At my new job, I decided to build a database, but only had access to MS Access, which I can use, but don’t necessarily like. One day, in earshot of one of our admins, I grumbled “if I only had access to a SQL server, I’d turn this thing into a very cool web application.� Well, he heard, and gave me access to the SQL server and the web server. Thing is, it’s not MySQL, but MS SQL Server. And I feel like I am at a total loss. I was able to build a basic database in the Enterprise Manager on the SQL server, and create a web site on the web server, but I don’t even know how to begin to connect the two – what is the equivalent of MySQLConnect? I know I would have to learn the VBScript and everything for the advanced stuff, but for right now, I just want to make a simple web page that will do a simple select query from a database and return the rows, and simple forms to input into the database. How do I begin? What do I need to know to begin? I have a feeling the applications already on the server were contracted out, so I don’t actually know if anyone here could help me.
I need to pull data out of SQL Server 2005 and load it into a MySql 5 database, and can't find a way to do this efficiently in SSIS since there's no ODBC destination, and I couldn't find an Oledb driver for MySQL. Any thoughts on the best way to accomplish this, aside from exporting to text, and then loading it into MySql via the mysql client or mysql importer?
Hey there,I'm curious about MySQL. Could anyone quickly summarize thedifferences between MySQL and SQL Server and outline why someone mightpick SQL Server over MySQL?(Please post replies here, email address is fake)Cheers,Geoff GlaveVancouver, Canada
I'm searching for a way to compare informations from one database against another database.
E.g. i want to check if serialnumbers of my first database (eg hardware.xls or hardware.csv ) are already stored in my sql server database.
I know i can use sql querys (Joins) for that task but i don't know how i can access 2 different databases at the same time. Do i need a tool for it? Does Excel is able to compare those data? But how? I'm searching for that thread since 2 days testing with some tools without any success...
I don't know if this is unique to GoDaddy: I found the program for working with a MySQL database very intuitive (to the point of compliments). Today I started work with ASP.NET and SQL, and when I went to work with SQL Server (on GoDaddy), it wasn't intuitive, and there was almost nothing on which to click. By chance might there be a tutorial that would help a person who needs to work with SQL Server on a provider like GoDaddy?
Hello all, I have an application in which the database is currently in MySQL. The app is being re-written(in .net 3.5) and I would like to move the data to SQL Server 2005. What is the best may to get the data from MySQL to SQL Server. Any good tools out there? Idealy, I would be able to manipulate the data during the move, as I have plans for some schema changes, but I suppose I could always make them after migrating. Please suggest any tools or methods, with your thoughts. I will evaluate any suggested tools. Also, SSIS is available for me, is that the best way?
hi ! i am using SQL Server 2000...someone told me the new version of Mysql 5.0 is very powerfull and it is better than MS products?!!! any good resources about the benefit of using SQL server 2000 rather than open source?!! Thanks!
I have created a linked server to a MySQL database using a DSN. Does anyone know how to run a select statement against a table in the MySQL database? The usual 4 part naming convention does not work.
I can run SELECT * FROM OPENQUERY(Top50, 'SELECT * FROM orders') which does return records successfully. (Top50 being the name of the linked server)
My objective is to be able to insert records into the MySQL database and I am not sure how do to that using OPENQUERY. Any thoughts on that would be appreciated as well.
Anyone know how to go about getting data from MySql to MS SQL server? Im a little confussed as to what ODBC driver is needed and all that. Is it possible to this all through enterprise manager? Thanks a lot.
I have used this mysql code in my programm before:
select * from address order by vorname asc limit 0, 10
It works fine. But now I want to use sql server, and it doesn't recognize the term limit. So I have found out that I can use top instead. I have changed my code into this:
select top 0 * from (select top 10* from address order by vorname) order by vorname asc
Hi all, hoping someone can help. I have created an ODBC connection to a remote MySQL database. I can then go into DTS wizard and specify this ODBC connection, it all goes through fine and I can see the tables on the remote server and import data. The problem arises when I try and create a linked server using the same ODBC connection. I get the following error, when I try to view the tables. I am so stuck. Any help would be very much appreciated.
We are trying to do a linked server to MySQL from MS SQL2k. We downloaded MyODBC drivers, setup the system dsn successfully but then SQL errors out using both the GUI and the stored proc to add the linked server to mysql. Does anyone have a good site to reference or any words of advice. An hr or so of google didn't really give up any helpfully information.
I have to port a database from MSSQL to MySQL. I'm really a newbie with MSSQL and I need some hints on how to do this task.
I received a .bak file which my boss told me that this a backup of the database I have to port.
Then I installed MSQL 2000 developer edition on my workstation and then started playing with it.
The problem is that I don't know how to connect to the database... and with what application do some queries. In mysql I use phpMyAdmin which is really useful. Is there something similar for SQL server? Or something like sqlplus under Oracle?
Moreover how can I install the .BAK file they sent me on my MSSQL install in order to recreate the db on my workstation??
I use MS profiler to run traces to diagnose issues/problems with any MS SQL Server database. Is there an equivalent tool when using MySQL database? What about Enterprise Manger and Query Analyzer? Is it fair to assume that the administrative tools available for MySQL will do the job that MS tools do for MS SQL Server with comparable ease?
Public Class DB Dim myDB As New OdbcConnection Dim myCMD As New OdbcCommand Public Sub New() Dim db_server = AppSettings("db_server") Dim db = AppSettings("db") Dim db_user = AppSettings("db_user") Dim db_pwd = AppSettings("db_pwd") Dim DBConnection As String = "DRIVER={MySQL ODBC 3.51 Driver};" & _ "SERVER=" & db_server & ";" & _ "DATABASE=" & db & ";" & _ "UID=" & db_user & ";" & _ "PASSWORD=" & db_pwd & ";" & _ "OPTION=3;" myDB.ConnectionString = DBConnection myCMD.Connection = myDB End Sub
Public Function q(ByVal mySTR As String) As OdbcDataReader myCMD.CommandText = mySTR Try myDB.Open() q = myCMD.ExecuteReader(Data.CommandBehavior.CloseConn ection) Catch ex As Exception Err(ex.ToString) End Try
End Function
Public Sub c(ByVal mySTR As String) Try myCMD.Connection.Open() myCMD.CommandText = mySTR myCMD.ExecuteNonQuery() myCMD.Connection.Close() Catch ex As Exception Err(ex.ToString) End Try
End Sub
Private Sub Err(ByVal strError As String) Current.Response.Write("<h1>An error has occurred.</h1><br>" & vbCrLf) Current.Response.Write("Please report to us the error message<br>" & vbCrLf) Current.Response.Write(strError) Current.Response.Flush() Current.Response.End() 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
I want to send table values from MS SQL Server to MySQL. For example if any row is inserted or deleted or updated in MS SQL Server database then the same has to be reflected in a similar table in MySQL so that the both the database table are in synch with each other. it would be of gr8 help if anyone could suggest something regarding this. Thanks in advance....
We recently started to use SQL server 2005. Now I need someway to sync some tables between the SQL server and our existing MySql db. Any recommendations on tools who will allow me to do this?
I have a problem , I created a linked server to mysql with following parameters:
New linked server: ServerName
Server Type: Other data Source: Microsoft OLE DB Provider for ODBC Drivers.
In the Provider String:Driver={mySQL ODBC 3.51 driver};Server="ip";Port=3306;Option=17923;Stmt=;Database="database";Uid=root;Pwd="pwd"
And I created a ODBC with mysql, and connection worked sucessfully!
The connection worked corretly until I' ve running a transact query in sql query analyzer, thats connects to MySQL and...
when I try to connect in SQL Server 2000 standard Ed. in linked Server, the following message appears to me now, and before this I was able to connect!
Server: Msg 7399, Level 16, State 1, Line 11 OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure. ODBC: Msg 0, Level 19, State 1 SqlDumpExceptionHandler: Process 169 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x8000ffff: The provider reported an unexpected catastrophic failure.
The query just make a select in MySQL, but I think that broke something...
How can I change the MySQL code to SQL Server's like below? I could not find out to change appropriate UNSIGNED keyword and ENUM keyword in SQL Server.
CREATE TABLE myTable ( id INT UNSIGNED NOT NULL, name CHAR(20), role ENUM('ADMIN','USER','GUEST','UNKNOWN') DEFAULT 'ADMIN' PRIMARY KEY(id) );
