CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2000
majidarif
majidarif

Reputation: 20045

Incorrect syntax near SET in SQL Server 2000 stored procedure

I generated a SQL script from SQL Server 2000 because I can't just use the .bak file to restore into version 2014. When I tried to execute the generated SQL script on the new SQL Server 2014, I get an error

Incorrect syntax near the keyword 'SET'

Checking the blamed line:

IF ISNULL(@pi_strMChargeNo,'') = '' BEGIN
    INSERT INTO TPURCHASEGROUP(MCHARGENO, CASHPRICE, COUPONPRICE, EVENTPRICE, TOTALPRICE, PITEMCNT, REGDATE, UPDDATE)
    VALUES(@v_strMChargeNo, 0, 0, 0, 0, 1, GETDATE(), GETDATE())

    IF @@ERROR <> 0 
    BEGIN
        SET @po_strErrMsg = 'TPURCHASEGROUP INSERT FAIL'
        SET @po_intRetVal = 2266
        SET RETURN -- blamed
    END
END

Might be changes to the syntax from 2000 to 2014, but what? Or possibly just delete the line?

Upvotes: 0

Views: 427

Answers (1)

Mark Rotteveel
Mark Rotteveel

Reputation: 109077

Without further context, it looks like you should replace the line SET RETURN by RETURN.

Regarding your problem with restoring a backup. You might want to try restoring on SQL Server 2008, increase the compatibility level of the database to 100, make a new backup and restore that under SQL Server 2014.

Upvotes: 4

Related Questions