SQL query written in 2008 does not work in 2005

Question

I wrote a trigger in the development environment which runs SQL Server 2008. I then tried to execute it in production, which runs SQL Server 2005.

I'm getting the following error:

Msg 102, Level 15, State 1, Procedure trg_issues_update, Line 97
Incorrect syntax near ','.

The query looks like this:

INSERT INTO users (username, firstname, surname, pwd, emailaddress, resetpwd, comment,  apikey, active)
VALUES (@username, @first_name, @last_name, @password, @email, NULL, 'Comment: ' + CAST(@issueid AS VARCHAR), 'None', '1');

UPDATE: This is the part of the trigger where most of the variables are declared/set:

IF (SELECT projectid FROM inserted) = 55 AND (SELECT issuestatusid FROM inserted) = 28 BEGIN
    DECLARE @full_name nvarchar (255)
    SET @full_name = (SELECT fielddata FROM customfielddata WHERE issueid = @issueid AND customfieldid = 84)

    DECLARE @first_name nvarchar (255)
    SET @first_name = SUBSTRING(@full_name, 1, CHARINDEX(' ', @full_name) - 1)

    DECLARE @last_name nvarchar (255)
    SET @last_name = SUBSTRING(@full_name, CHARINDEX(' ', @full_name) + 1, LEN(@full_name) + 1)

    DECLARE @email nvarchar (255)
    SET @email = (SELECT fielddata FROM customfielddata WHERE issueid = @issueid AND customfieldid = 85)

    DECLARE @location nvarchar (255)
    DECLARE @domain nvarchar (255)
    DECLARE @username nvarchar (255)
    SET @location = (SELECT fielddata FROM customfielddata WHERE issueid = @issueid AND customfieldid = 86)
    SET @domain = (SELECT fielddata FROM customfielddata WHERE issueid = @issueid AND customfieldid = 87)
    IF @location = 'Canada' BEGIN SET @username = 'ORG1\' + UPPER(@domain) END
    ELSE SET @username = 'ORG2\' + UPPER(@domain)

    DECLARE @password binary (16)
    SET @password = CONVERT(BINARY(16), HashBytes('MD5', ''), 2)

    DECLARE @comment nvarchar (500)
    SET @comment = 'APPR-' + CAST(@issueid AS VARCHAR)

    INSERT INTO users (username, firstname, surname, pwd, emailaddress, resetpwd, comment, apikey, active)
    VALUES (@username, @first_name, @last_name, @password, @email, NULL, @comment, 'None', '1');

    DECLARE @userid NUMERIC(10,0)
    SET @userid = (SELECT userid FROM users WHERE username = @username)

    INSERT INTO globalgroupmembership (globalgroupid, userid, created)
    VALUES (1, @userid, CURRENT_TIMESTAMP), (3, @userid, CURRENT_TIMESTAMP)

END

I assume it's because of the 2005 vs 2008 differences, but can't figure out what exactly is wrong.

Ya - we'll need to see the trigger to help you, since that's where the error is — n8wrl
– n8wrl, Commented Nov 18, 2013 at 21:00
The issue is with this line VALUES (1, @userid, CURRENT_TIMESTAMP), (3, @userid, CURRENT_TIMESTAMP) 2005 doesn't support the VALUES(etc), (etc) For that line, use either multiple INSERT statements or SELECT with UNION ALL — Taryn
– Taryn, Commented Nov 18, 2013 at 21:14
Thanks @bluefeet - I re-wrote that part and the query executed successfully. Feel free to post an answer, I will accept it. Line 97 from the error message was that INSERT query I posted originally, oh well. — ulmas
– ulmas, Commented Nov 18, 2013 at 21:17

Taryn · Accepted Answer · 2013-11-18 21:19:52Z

The issue is with the line:

INSERT INTO globalgroupmembership (globalgroupid, userid, created)
VALUES (1, @userid, CURRENT_TIMESTAMP), (3, @userid, CURRENT_TIMESTAMP)

From MSDN:

SQL Server 2008 introduces the Transact-SQL row constructor (also called a table value constructor) to specify multiple rows in a single INSERT statement. The row constructor consists of a single VALUES clause with multiple value lists enclosed in parentheses and separated by a comma.

In order to fix this so it will work in both SQL 2008 and SQL Server 2005, you will want to rewrite the query to:

INSERT INTO globalgroupmembership (globalgroupid, userid, created)
VALUES (1, @userid, CURRENT_TIMESTAMP);

INSERT INTO globalgroupmembership (globalgroupid, userid, created)
VALUES (3, @userid, CURRENT_TIMESTAMP)

Or you can use INSERT INTO... SELECT...UNION ALL:

INSERT INTO globalgroupmembership (globalgroupid, userid, created)
SELECT 1, @userid, CURRENT_TIMESTAMP
UNION ALL
SELECT 3, @userid, CURRENT_TIMESTAMP;

aweis · Accepted Answer · 2013-11-18 20:58:29Z

-2

try to concatinate the comment before the value list:

declare @comment varchar
set @comment = 'Comment: ' + cast(@issueid as varchar)

INSERT INTO users (username, firstname, surname, pwd, emailaddress, resetpwd, comment,  apikey, active)
select @username, @first_name, @last_name, @password, @email, NULL, @comment, 'None', '1'

answered Nov 18, 2013 at 20:58

aweis

5,6564 gold badges38 silver badges54 bronze badges

2 Comments

Laurence Over a year ago

It wasn't me, but I guess it's because 2005 supports expressions in the values part of an insert.

ulmas Over a year ago

I tried this approach, but it didn't work unfortunately (it wasn't me who downvoted).

Collectives™ on Stack Overflow

SQL query written in 2008 does not work in 2005

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related