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.
I am in the process of convering my code from using MySQL to MS SQL
I have queries of the form:
Code:
INSERT INTO <table> (params) VALUES (values) ON DUPLICATE KEY UPDATE <param1>=<value1>, <param1>=<value1>... <param n>=<value n>
My question is: Is there an equivalent syntax for such query in MS SQL? I am looking for a syntax that does this in a single atomic query. I can always break it down to 2 queries (select + insert/update), but I will do so only after I know there is no equivalent way for it in MS SQL
Also, is there an equivalent syntax for LIMIT X,Y in MS SQL for quries in the form:
Code:
SELECT <params> FROM <tables> WHERE <conditions> LIMIT X, Y
Working on trying to support mutliple backend db's against a custom VB6 app. Right now the db is SqlExpress. It's relatively uncomplicated and I just want to move table structures and data over. Using the MySql Migration tool, I am able to authenticate as SA to a server-based instance of SqlExpress, however, only the MS-supplied databases appear as available databases; my databases don't appear. Can't seem to authenticate at all to any local instance of the database, either. Anyone done any successful migrations to MySql through their migration tool?
For those of you who are able to assist, I'd like to thank you in advance right now. This is a pretty big problem for me.
First let me setup what it is I'm trying to do before I describe the problem in detail. This is part of a semester-long Software Engineering project for my SE class at school. Now, I've got a month for this project, but the database part of it is something I'm trying to get done by this week. Our team is using GoDaddy to host our account and so to simplify our problems with using Visual Studio and MySQL we're switching over to MS SQL, where our code works.
Okay, onto the specifics: I'm used to using a database modeling program called DBDesigner4. Unfortunately, they're support forums have been closed down (fabFORCE.net) and I've spoken with a GoDaddy representative and their servers don't allow me to directly connect with my modeling program and create the schema/tables using the program. However, the program does export to a MySQL table creation script or even an MDB XML file (MSAcess I believe). I'm trying to hand convert the creation script over to MS SQL syntax and I'm having alot of problems (please bear with me, I've never messed with MS SQL before).
For your reference, I'm going to display the actual MySQL script here (just skip this section if you'd like to see the actual problem below):
Code:
CREATE TABLE Addresses ( address_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, address_line1 VARCHAR(30) NOT NULL, address_line2 VARCHAR(30) NULL, address_city VARCHAR(30) NOT NULL, address_state VARCHAR(2) NOT NULL, address_zip MEDIUMINT UNSIGNED NOT NULL, PRIMARY KEY(address_id) ) TYPE=InnoDB;
CREATE TABLE Broadcasts ( broadcast_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, address_id INTEGER UNSIGNED NOT NULL, broadcast_title VARCHAR(30) NOT NULL, broadcast_message VARCHAR(255) NOT NULL, broadcast_image VARCHAR(255) NOT NULL, broadcast_date_posted DATETIME NOT NULL, broadcast_date_expires DATETIME NOT NULL, broadcast_date_archived DATETIME NULL, broadcast_clicks BIGINT NOT NULL DEFAULT 0, broadcast_type ENUM('promo', 'announcement', 'other') NOT NULL DEFAULT 'promo', broadcast_link VARCHAR(255) NULL, PRIMARY KEY(broadcast_id), INDEX Broadcasts_FKIndex1(address_id) ) TYPE=InnoDB;
CREATE TABLE Classes ( user_id INTEGER UNSIGNED NOT NULL, subject_id INTEGER UNSIGNED NOT NULL, class_grade INTEGER UNSIGNED NOT NULL DEFAULT 100, class_tardies INTEGER UNSIGNED NOT NULL DEFAULT 0, class_absences INTEGER UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY(user_id, subject_id), INDEX Users_has_Sections_FKIndex1(user_id), INDEX Classes_FKIndex2(subject_id) );
CREATE TABLE Classes_have_Grades ( subject_id INTEGER UNSIGNED NOT NULL, user_id INTEGER UNSIGNED NOT NULL, grade_id INTEGER UNSIGNED NOT NULL, PRIMARY KEY(subject_id, user_id, grade_id), INDEX Grades_has_Classes_FKIndex1(grade_id), INDEX Grades_has_Classes_FKIndex2(user_id, subject_id) );
CREATE TABLE Grades ( grade_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, grade INTEGER UNSIGNED NOT NULL, grade_type ENUM('h', 'q', 't') NOT NULL, grade_desc VARCHAR(50) NOT NULL, PRIMARY KEY(grade_id) );
CREATE TABLE Locations ( loc_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, address_id INTEGER UNSIGNED NOT NULL, location_id VARCHAR(20) NOT NULL, location_name VARCHAR(25) NOT NULL, PRIMARY KEY(loc_id), INDEX Locations_FKIndex1(address_id) ) TYPE=InnoDB;
CREATE TABLE Passports ( passport_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, user_id INTEGER UNSIGNED NOT NULL, location_id INTEGER UNSIGNED NOT NULL, passport_user VARCHAR(7) NOT NULL, passport_code VARCHAR(32) NOT NULL, passport_access ENUM('student', 'faculty', 'admin') NOT NULL DEFAULT 'student', passport_tries TINYINT UNSIGNED NOT NULL DEFAULT 0, passport_locked BOOL NOT NULL DEFAULT 'false', passport_lastaccess DATETIME NULL, PRIMARY KEY(passport_id), INDEX Passports_FKIndex1(location_id), INDEX Passports_FKIndex2(user_id) ) TYPE=InnoDB;
CREATE TABLE Subjects ( subject_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, subject_name VARCHAR(50) NOT NULL, subject_offered BOOL NOT NULL, subject_grade ENUM('1', '2', '3', '4', '5', '6') NOT NULL, PRIMARY KEY(subject_id) ) TYPE=InnoDB;
CREATE TABLE Users ( user_id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, address_id INTEGER UNSIGNED NOT NULL, user_fname VARCHAR(20) NOT NULL, user_mletter VARCHAR(1) NULL, user_lname VARCHAR(20) NOT NULL, user_grade FLOAT NOT NULL, user_type ENUM('student', 'faculty') NOT NULL DEFAULT 'student', user_balance FLOAT NOT NULL, user_phone CHAR(15) NOT NULL, user_email VARCHAR(50) NOT NULL, PRIMARY KEY(user_id), INDEX Users_FKIndex1(address_id) ) TYPE=InnoDB;
That script runs just fine on MySQL and generates my application's tables with the correct relationships intact, indexes, etc.
However, as I said, I need this setup in MS SQL with a few additional checks and I've hand converted most of this, not knowing how to properly maintain primary keys or foreign keys:
Code:
CREATE TABLE Addresses ( address_id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
address_line1 VARCHAR(30) NOT NULL,
address_line2 VARCHAR(30) NULL,
address_city VARCHAR(30) NOT NULL,
address_state VARCHAR(2) NOT NULL,
address_zip ZIPCODE CONSTRAINT CK_address_zip CHECK (address_zip LIKE '[0-9][0-9][0-9][0-9][0-9] ') )
CREATE TABLE Broadcasts ( broadcast_id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
address_id INT NOT NULL,
broadcast_title VARCHAR(30) NOT NULL,
broadcast_message VARCHAR(255) NOT NULL,
broadcast_image VARCHAR(255) NOT NULL,
broadcast_date_posted DATETIME NOT NULL,
broadcast_date_expires DATETIME NOT NULL,
broadcast_date_archived DATETIME NULL,
broadcast_clicks INT NOT NULL DEFAULT 0,
broadcast_type CHAR(1) NOT NULL DEFAULT(‘a’),
broadcast_link VARCHAR(255) NULL )
CREATE TABLE Classes ( user_id INT PRIMARY KEY NOT NULL,
subject_id INT NOT NULL,
class_grade INT NOT NULL DEFAULT 100,
class_tardies INT NOT NULL DEFAULT 0,
class_absences INT NOT NULL DEFAULT 0 )
CREATE TABLE Classes_have_Grades ( subject_id INT NOT NULL,
user_id INT PRIMARY KEY NOT NULL,
grade_id INT NOT NULL )
CREATE TABLE Grades ( grade_id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
Grade INT NOT NULL,
grade_type CHAR(1) NOT NULL DEFAULT(‘h’),
grade_desc VARCHAR(50) NOT NULL )
CREATE TABLE Locations ( loc_id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
address_id INT NOT NULL,
location_id VARCHAR(20) NOT NULL,
location_name VARCHAR(25) NOT NULL )
CREATE TABLE Passports ( passport_id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
user_id USERID NOT NULL CONSTRAINT CK_user_id CHECK (user_id LIKE '[A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9]'),
location_id INT NOT NULL,
passport_user VARCHAR(7) NOT NULL,
passport_code VARCHAR(32) NOT NULL,
passport_access CHAR(1) NOT NULL DEFAULT(‘s’),
passport_tries TINYINT NOT NULL DEFAULT(0),
passport_locked BOOL NOT NULL DEFAULT(false),
passport_lastaccess DATETIME NULL )
CREATE TABLE Subjects ( subject_id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
subject_name VARCHAR(50) NOT NULL,
subject_offered BOOL NOT NULL,
subject_grade SMALLINT NOT NULL DEFAULT 6 )
CREATE TABLE Users ( user_id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
address_id INT NOT NULL,
user_fname VARCHAR(20) NOT NULL,
user_mletter CHAR(1) NULL,
user_lname VARCHAR(20) NOT NULL,
user_grade FLOAT NOT NULL,
user_type CHAR(1) NOT NULL DEFAULT(‘s’),
user_balance FLOAT NOT NULL,
user_phone CHAR(15) NOT NULL,
user_email VARCHAR(50) NOT NULL )
Unfortunately, Query Analyzer seems to have a problem and says I have an error in my syntax (on line 74) near "'".
I've googled around and found out a couple of things: TSQL doesn't support enumerations (that I'm aware of - therefore I converted my enum fields to CHAR(1)s or INTs) and that the single quotes surrounding default values is the proper way to do DEFAULT values. I can't figure out for the life of me what's wrong with my syntax and I've been going at this for about 4 hours now.
If you can help me out by explaining how I should properly do the PKs/FKs (I believe there's a keyword REFERENCE(field1, field2) for FKs, but I'm not sure where to place it, etc.) I'll go through the SQL script and try to implement it, but I have a feeling this is going to be a long thread if I do get someone willing to help.
Thanks to everyone who took the time to read this, Ahad L. Amdani
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
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 .
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..?
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
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
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.
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?
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 .
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.
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.
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 ')
Can anybody tell how to import a table with the text column from SQL Server 2000 to MySQL 4.0.17? I tried this using ODBC connection but got an error message saying, "Query-based Insertion or updating of BLOB values is not supported".
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. :-(
Hello Everyone, thank you for taking the time to read my post. I'm creating a view in SQL Server 2005 to base a report on Crystal Reports XI. I've been trying to figure out how I can convert a date field in the format YYYYMMDD to MM/DD/YYYY. I'm not quite sure about the steps I need to take to accomplish this since I'm pretty new to this. The date is stored as an Int on the database, and I've tried converting it directly to the DATETIME data type like this: CONVERT(DATETIME, datefield,101) but I'm getting an error saying illegal data type conversion. Thanks a lot for your help, I really appreciate it.
I am migrating some DTS2000 packages to SSIS via the migration wizard. However there is an activex script in each DTS that connects to an AS400 to retrieve record count for integrity checking. Under SQL server 2000 this worked fine but now it errors with "The OLE DB provider "MSADSQL" has not been registered" how do i get this to work i suspect its because this is a 32 bit dll and my new system is a 64bit sql server on windows 2003. The odbc driver for the as400 has been installed also. The script is as follows:
dim cn 'sql connection dim rs 'sql recordset for the insertion of new as400 records dim insertstr 'sql insert string dim rs1 'sql recordset for the insertion of new as400 records dim insertstr1 'sql insert string
set cn = createobject("ADODB.Connection") set rs = createobject("ADODB.Recordset")
Function Main() 'set up connection for sql cn.provider="sqloledb" cn.commandtimeout = 3600 'timeout in seconds cn.open %sqlserver05%, %user%, %password%
'populate temporary table in sql with recordset from as400 odbc insertstr1 = "Insert into db.dbo.%table% select 'table name',a.* from openrowset ('MSDASQL','DRIVER={Client Access ODBC Driver (32-bit)};SYSTEM=%system name%;UID=%username%;PWD=%password%','SELECT count(*) from %table%') as a"
set rs1 = cn.execute (insertstr1)
'close all objects cn.close Main = DTSTaskExecResult_Success End Function
Does anyone know how i change this to work under sql server 2005. i need to resolve this quickly in order to deliver a project on time. Many thanks
hello friends, i am facing a problem in SQL Server 2005 Express Edition. The database created in the SQL Server 2005 Express is when opened in SQL Server 2000, it changes the data type of all the columns of all the table to text type. can anybody help me?
We downloaded and installed the trial software of SQL Server 2005 Enterprise edition a month ago. We have purchased a fully licensed version of SQL Server 2005 Standard Edition and would like to apply the licensed version to our workstation clients since their SQL software will eventually expire. Our SQL Server is not an issue since we purchased a new Server and installed the new licensed version of SQL on it.
Is there an easy way to accomplish this or is an uninstall & reinstall of SQL on every workstation required?
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.
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
I Have developed a windows application with an SQL server 2005 database. Now I want to publish that application BUT I want to make that database an SQL CE one. In other words, I want to make the users able to download the application with the database without having to download SQL EXPRESS from the net or so....But instead have the Database embedded with the instllation package together with the SQL CE. 1. What is the best way i can transfere the data (Tables and their data) from ordinary SQL server 2005 .mdf files to some place where it can work with SQL CE ? 2. How to embedd the data base and the SQL CE within the installtion package ?
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
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:
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?
