mysql query then sql server 2000 query

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

Remove this from the definition -it is unknown to SQL Server...

TYPE=MyISAM

Change the [ longtext ] datatypes to [ varchar(max) ]

Change the

enum('available','unavailable','backorder')

to a [ CHECK ]CONSTRAINT (and the other enum as well)

I think that you will probably be much happier if you were to change the [ acc_date_added ] datatype from varchar(50) to a datetime datatype with a default of [ 0 ]

Change the Supplier datatype from [ text ] to varchar(LengthAsAppropriate)

|||Here you go...

CREATE TABLE acc (
acc_id int NOT NULL IDENTITY,
cat_id int NOT NULL default '0',
brand_id int NOT NULL default '0',
item_code varchar(100) NOT NULL default '',
acc_name varchar(255) NOT NULL default '',
acc_desc text NOT NULL,
acc_spec text NOT NULL,
acc_techspec text NOT NULL,
acc_warranty varchar(5) NOT NULL default '0',
acc_partno text NOT NULL,
acc_serialno text NOT NULL,
acc_size text NOT NULL,
acc_weight text NOT NULL,
acc_price int NOT NULL default (0),
acc_dprice int NOT NULL default (0),
logos varchar(100) NOT NULL default '',
condition varchar(255) NOT NULL default '',
status varchar(10) Not Null default 'available',
approved varchar(10) not null default 'approved',
addedby varchar(10) NOT NULL default '',
acc_date_added varchar(50) NOT NULL default '0000-00-00',
acc_lprice int NOT NULL default (0),
supplier text NOT NULL,
constraint pk_acc primary key
(acc_id)
)
ALTER TABLE acc WITH CHECK ADD CONSTRAINT [CK_acc_Status]
CHECK (Status in ('available','unavailable','backorder'))
ALTER TABLE acc WITH CHECK ADD CONSTRAINT [CK_acc_approved]
CHECK (approved in ('approved','unapproved'))