Mysql query to Sqlserver 2000 query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Mysql query to Sqlserver 2000 query

Author

Topic

shahzebkn
Starting Member

4 Posts

Posted - 2007-04-10 : 18:47:25

Hi all

My client database was in mysql. Now I am converting its in sql server 2000. And all queries was written for mysql. for example this query.

This Query for MySql

-----------------------------------------------------------------------------------------------------------------

CREATE TABLE `acc` (
`acc_id` int(7) NOT NULL auto_increment,
`cat_id` int(7) NOT NULL default '0',
`brand_id` int(11) NOT NULL default '0',
`item_code` varchar(100) NOT NULL default '',
`acc_name` varchar(255) NOT NULL default '',
`acc_desc` longtext NOT NULL,
`acc_spec` longtext NOT NULL,
`acc_techspec` longtext NOT NULL,
`acc_warranty` varchar(5) NOT NULL default '0',
`acc_partno` longtext NOT NULL,
`acc_serialno` longtext NOT NULL,
`acc_size` longtext NOT NULL,
`acc_weight` longtext NOT NULL,
`acc_price` int(11) NOT NULL default '0',
`acc_dprice` int(11) NOT NULL default '0',
`logos` varchar(100) NOT NULL default '',
`condition` varchar(255) NOT NULL default '',
`status` enum('available','unavailable','backorder') NOT NULL default 'available',
`approved` enum('approved','unapproved') NOT NULL default 'approved',
`addedby` varchar(10) NOT NULL default '',
`acc_date_added` varchar(50) NOT NULL default '0000-00-00',
`acc_lprice` int(11) NOT NULL default '0',
`supplier` text NOT NULL,
PRIMARY KEY (`acc_id`)
) TYPE=MyISAM;

-----------------------------------------------------------------------------------------------------------------

I made it for sql srever 2000 like this

-----------------------------------------------------------------------------------------------------------------

CREATE TABLE acc (
acc_id int(7) NOT NULL IDENTITY,
cat_id int(7) NOT NULL default '0',
brand_id int(11) NOT NULL default '0',
item_code varchar(100) NOT NULL default '',
acc_name varchar(255) NOT NULL default '',
acc_desc longtext NOT NULL,
acc_spec longtext NOT NULL,
acc_techspec longtext NOT NULL,
acc_warranty varchar(5) NOT NULL default '0',
acc_partno longtext NOT NULL,
acc_serialno longtext NOT NULL,
acc_size longtext NOT NULL,
acc_weight longtext NOT NULL,
acc_price int(11) NOT NULL default '0',
acc_dprice int(11) NOT NULL default '0',
logos varchar(100) NOT NULL default '',
condition varchar(255) NOT NULL default '',
--status enum('available','unavailable','backorder') NOT NULL default 'available',
status varchar(10) Not Null default 'available'
constraint chk_valid$entries$for$status
check ( status in ('available','unavailable','backorder')),
approved varchar(10) not null default 'approved'
constraint chk_valid$entries$for$approved
check ( approved in ('approved','unapproved')),
--approved enum('approved','unapproved') NOT NULL default 'approved',
addedby varchar(10) NOT NULL default '',
acc_date_added varchar(50) NOT NULL default '0000-00-00',
acc_lprice int(11) NOT NULL default '0',
supplier text NOT NULL,
PRIMARY KEY (acc_id)
) TYPE=MyISAM;

-----------------------------------------------------------------------------------------------------------------

Only last line give me error. here is the error anyone could plz help me.

Server: Msg 170, Level 15, State 1, Line 32
Line 32: Incorrect syntax near '='.

Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-10 : 18:50:13

Just remove "TYPE=MyISAM;" part.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-10 : 18:50:57

On second looks, there are few other changes you need to make..let me check it out again..

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-10 : 18:54:23

(1) Remove the size part for the INT's. You cannot specify the length.
acc_id int(7) NOT NULL IDENTITY,
would be

acc_id int NOT NULL IDENTITY,

(2) There is no 'longtext' in SQL. Try using Varchar(500) to see if its sufficient for your data.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

shahzebkn
Starting Member

4 Posts

Posted - 2007-04-10 : 19:30:58

Thanks

I did all changes and its work. If i use text instead of longtext how is it?
And another point is that if i run all mysql script in mysql database then using any converting tool convert mysql database to sql server 2000 database. Would it be possible.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-04-10 : 19:32:55

text datatype is a different beast alltogether. Read up documentation about the pros an cons before using it. See if you really need it because it can slow down queries and performance if not implemented properly.

Validate the script your tool generates before you run it.

************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-04-10 : 21:30:17

also, the mysqldump command will help you dump the contents of each table to a SQL file that you can run against your SQL Server version.

Make sure you use the --compatible=mssql argument if you go this route. Now that I think about it, this command might be MySQL 5 only. Hopefully you are using a recent version of MySQL and not 3.2x. If you are running an old version you may have to manually edit the mysqldump output to make it compatible with SQL Server.

You can also use SQL Server DTS to extract the data (as well as create the tables). You just need to install the MySQL ODBC driver on your system so DTS can use it to connect to MySQL.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources