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'))

my store procedure that call back itself doesn't seems to work.

I have written a store procedure that will delete the record and any children record of it. That store procedure is suppose to call back itself to for every children record, but it only seems to delete the first record (the parent record). Below is my store procedure, pls help.

CREATE PROCEDURE [deleteCMSPage]
(@.PROJ_CMS_PAGE_ID [int])

AS

/* do not delete page id 1 */
if @.PROJ_CMS_PAGE_ID <> 1
begin
-- For storing the list of child activities.
DECLARE @.page_list_cursor CURSOR
DECLARE @.CHILD_ID INTEGER
/* get the children of this page, for every child, call deleteCMSPage */
SET @.page_list_cursor = CURSOR FOR
SELECT PROJ_CMS_PAGE_ID
FROM TBL_PROJ_CMS_PAGE
WHERE PARENT_ID = @.PROJ_CMS_PAGE_ID
ORDER BY PAGE_ORDER

-- Populate the cursor.
OPEN @.page_list_cursor

WHILE (@.@.FETCH_STATUS = 0)
BEGIN
-- Fetch the details for next activity.
FETCH NEXT FROM @.page_list_cursor
INTO @.CHILD_ID
-- Calls itself, i.e
-- as parent to get all child nodes associated with it.
EXECUTE deleteCMSPage @.CHILD_ID
END
-- Close the cursor.
CLOSE @.page_list_cursor
-- Deallocate the cursor.
DEALLOCATE @.page_list_cursor
DELETE TBL_PROJ_CMS_PAGE
WHERE PROJ_CMS_PAGE_ID = @.PROJ_CMS_PAGE_ID
end
GO

The global variable @.@.FETCH_STATUS is set only when you perform a fetch. So the WHILE loop condition is wrong. You should code it like:

while (1=1)

begin

fetch...

if @.@.fetch_status < 0 break

...

end

|||

I have change the code, and right now it only delete the first branch, eg. root, first child, child of first child ..etc, I am not sure why.

CREATE PROCEDURE [deleteCMSPage]
(@.PROJ_CMS_PAGE_ID [int])

AS

/* do not delete page id 1 */
if @.PROJ_CMS_PAGE_ID <> 1
begin
-- For storing the list of child activities.
DECLARE @.page_list_cursor CURSOR
DECLARE @.CHILD_ID INTEGER
/* get the children of this page, for every child, call deleteCMSPage */
SET @.page_list_cursor = CURSOR FOR
SELECT PROJ_CMS_PAGE_ID
FROM TBL_PROJ_CMS_PAGE
WHERE PARENT_ID = @.PROJ_CMS_PAGE_ID
ORDER BY PAGE_ORDER

-- Populate the cursor.
OPEN @.page_list_cursor

while (1=1)
BEGIN

-- Fetch the details for next activity.
FETCH NEXT FROM @.page_list_cursor
INTO @.CHILD_ID
-- Calls itself, i.e
-- as parent to get all child nodes associated with it.
EXECUTE deleteCMSPage @.CHILD_ID
if @.@.fetch_status < 0 break
END
-- Close the cursor.
CLOSE @.page_list_cursor
-- Deallocate the cursor.
DEALLOCATE @.page_list_cursor
DELETE TBL_PROJ_CMS_PAGE
WHERE PROJ_CMS_PAGE_ID = @.PROJ_CMS_PAGE_ID
end
GO

|||

You have the CHECK for FETCH in the wrong place not like what I showed. Here you are checking FETCH_STATUS of some fetch operation that happened within the delete SP not the one before the call to the SP. So you should change below:

-- Fetch the details for next activity.
FETCH NEXT FROM @.page_list_cursor
INTO @.CHILD_ID

-- Calls itself, i.e
-- as parent to get all child nodes associated with it.
EXECUTE deleteCMSPage @.CHILD_ID
if @.@.fetch_status < 0 break

to:

-- Fetch the details for next activity.
FETCH NEXT FROM @.page_list_cursor
INTO @.CHILD_ID

if @.@.fetch_status < 0 break

-- Calls itself, i.e
-- as parent to get all child nodes associated with it.
EXECUTE deleteCMSPage @.CHILD_ID

|||thanks for you help, it's working now. :)