STORED PROCEDURE working in my local test machine cannot be created in production environment.

Question

Hi, I have an SQL CREATE PROCEDURE statement that runs perfectly in my local SQL Server, but cannot be recreated in production environment. The error message I get in production is Msg 102, Level 15, State 1, Incorrect syntax near '='.

It is a pretty big query and I don't want to annoy StackOverflow users, but I simply can't find a solution. If only you could point me out what settings I could check in the production server in order to enable running the code... I must be using some kind of syntax or something that is conflicting with some setting in production. This PROCEDURE was already registered in production before, but when I ran a DROP - CREATE PROCEDURE today, the server was able to drop the procedure, but not to recreate it.

I will paste the code below. Thank you!

===============

USE [Enorway]
GO

/****** Object:  StoredProcedure [dbo].[Spel_CM_ChartsUsersTotals]    Script Date: 03/17/2010 11:59:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Spel_CM_ChartsUsersTotals]
    @IdGroup int,
    @IdAssessment int,
    @UserId int
AS
SET NOCOUNT ON

DECLARE @RequiredColor varchar(6)
SET @RequiredColor = '3333cc'

DECLARE @ManagersColor varchar(6)
SET @ManagersColor = '993300'

DECLARE @GroupColor varchar(6)
SET @GroupColor = 'ff0000'

DECLARE @SelfColor varchar(6)
SET @SelfColor = '336600'

DECLARE @TeamColor varchar(6)
SET @TeamColor = '993399'

DECLARE @intMyCounter tinyint
DECLARE @intManagersPosition tinyint
DECLARE @intGroupPosition tinyint
DECLARE @intSelfPosition tinyint
DECLARE @intTeamPosition tinyint
SET @intMyCounter = 1


-- Table that will hold the subtotals...
DECLARE @tblTotalsSource table
(
    IdCompetency int,
    CompetencyName nvarchar(200),
    FunctionRequiredLevel float,
    ManagersAverageAssessment float,
    SelfAssessment float,
    GroupAverageAssessment float,
    TeamAverageAssessment float
)

INSERT INTO @tblTotalsSource
(
    IdCompetency,
    CompetencyName,
    FunctionRequiredLevel,
    ManagersAverageAssessment,
    SelfAssessment,
    GroupAverageAssessment,
    TeamAverageAssessment
)

SELECT
    e.[IdCompetency],
    dbo.replaceAccentChar(e.[Name]) AS CompetencyName,
    (i.[LevelNumber]) AS FunctionRequiredLevel,
    (
        SELECT
            ROUND(avg(CAST(ac.[LevelNumber] AS float)),0)
        FROM
            Spel_CM_AssessmentsData aa
            INNER JOIN Spel_CM_CompetenciesLevels ab ON aa.[IdCompetencyLevel] = ab.[IdCompetencyLevel]
            INNER JOIN Spel_CM_Levels ac ON ab.[IdLevel] = ac.[IdLevel]
            INNER JOIN Spel_CM_AssessmentsEvents ad ON aa.[IdAssessmentEvent] = ad.[IdAssessmentEvent]
        WHERE
            aa.[EvaluatedUserId] = @UserId AND
            aa.[AssessmentType] = 't' AND
            aa.[IdGroup] = @IdGroup AND
            ab.[IdCompetency] = e.[IdCompetency] AND
            ad.[IdAssessment] = @IdAssessment
    ) AS ManagersAverageAssessment,
    (
        SELECT 
            bc.[LevelNumber]
        FROM
            Spel_CM_AssessmentsData ba
            INNER JOIN Spel_CM_CompetenciesLevels bb ON ba.[IdCompetencyLevel] = bb.[IdCompetencyLevel]
            INNER JOIN Spel_CM_Levels bc ON bb.[IdLevel] = bc.[IdLevel]
            INNER JOIN Spel_CM_AssessmentsEvents bd ON ba.[IdAssessmentEvent] = bd.[IdAssessmentEvent]
        WHERE
            ba.[EvaluatedUserId] = @UserId AND
            ba.[AssessmentType] = 's' AND
            ba.[IdGroup] = @IdGroup AND
            bb.[IdCompetency] = e.[IdCompetency] AND
            bd.[IdAssessment] = @IdAssessment
    ) AS SelfAssessment,
    (
        SELECT
            ROUND(avg(CAST(cc.[LevelNumber] AS float)),0)
        FROM
            Spel_CM_AssessmentsData ca
            INNER JOIN Spel_CM_CompetenciesLevels cb ON ca.[IdCompetencyLevel] = cb.[IdCompetencyLevel]
            INNER JOIN Spel_CM_Levels cc ON cb.[IdLevel] = cc.[IdLevel]
            INNER JOIN Spel_CM_AssessmentsEvents cd ON ca.[IdAssessmentEvent] = cd.[IdAssessmentEvent]
        WHERE
            ca.[EvaluatedUserId] = @UserId AND
            ca.[AssessmentType] = 'g' AND
            ca.[IdGroup] = @IdGroup AND
            cb.[IdCompetency] = e.[IdCompetency] AND
            cd.[IdAssessment] = @IdAssessment
    ) AS GroupAverageAssessment,
    (
        SELECT
            ROUND(avg(CAST(dc.[LevelNumber] AS float)),0)
        FROM
            Spel_CM_AssessmentsData da
            INNER JOIN Spel_CM_CompetenciesLevels db ON da.[IdCompetencyLevel] = db.[IdCompetencyLevel]
            INNER JOIN Spel_CM_Levels dc ON db.[IdLevel] = dc.[IdLevel]
            INNER JOIN Spel_CM_AssessmentsEvents dd ON da.[IdAssessmentEvent] = dd.[IdAssessmentEvent]
        WHERE
            da.[EvaluatedUserId] = @UserId AND
            da.[AssessmentType] = 'm' AND
            da.[IdGroup] = @IdGroup AND
            db.[IdCompetency] = e.[IdCompetency] AND
            dd.[IdAssessment] = @IdAssessment
    ) AS TeamAverageAssessment
FROM
    Spel_CM_AssessmentsData a
    INNER JOIN Spel_CM_AssessmentsEvents c ON a.[IdAssessmentEvent] = c.[IdAssessmentEvent]
    INNER JOIN Spel_CM_CompetenciesLevels d ON a.[IdCompetencyLevel] = d.[IdCompetencyLevel]
    INNER JOIN Spel_CM_Competencies e ON d.[IdCompetency] = e.[IdCompetency]
    INNER JOIN Spel_CM_Levels f ON d.[IdLevel] = f.[IdLevel]
    -- This will link with user's assigned functions
    INNER JOIN Spel_CM_FunctionsCompetenciesLevels g ON a.[IdFunction] = g.[IdFunction]
    INNER JOIN Spel_CM_CompetenciesLevels h ON g.[IdCompetencyLevel] = h.[IdCompetencyLevel] AND e.[IdCompetency] = h.[IdCompetency]
    INNER JOIN Spel_CM_Levels i ON h.[IdLevel] = i.[IdLevel]
WHERE
    (NOT c.[EndDate] IS NULL) AND
    a.[EvaluatedUserId] = @UserId AND
    c.[IdAssessment] = @IdAssessment AND
    a.[IdGroup] = @IdGroup
GROUP BY
    e.[IdCompetency],
    e.[Name],
    i.[LevelNumber]
ORDER BY
    e.[Name] ASC


-- This will define the position of each element (managers, group, self and team)
SELECT @intManagersPosition = @intMyCounter FROM @tblTotalsSource WHERE NOT ManagersAverageAssessment IS NULL
IF IsNumeric(@intManagersPosition) = 1 BEGIN SELECT @intMyCounter += 1 END

SELECT @intGroupPosition = @intMyCounter FROM @tblTotalsSource WHERE NOT GroupAverageAssessment IS NULL
IF IsNumeric(@intGroupPosition) = 1 BEGIN SELECT @intMyCounter += 1 END

SELECT @intSelfPosition = @intMyCounter FROM @tblTotalsSource WHERE NOT SelfAssessment IS NULL
IF IsNumeric(@intSelfPosition) = 1 BEGIN SELECT @intMyCounter += 1 END

SELECT @intTeamPosition = @intMyCounter FROM @tblTotalsSource WHERE NOT TeamAverageAssessment IS NULL


-- This will render the final table for the end user. The tabe will flatten some of the numbers to allow them to be prepared for Google Graphics.
SELECT
    SUBSTRING( 
        (
            SELECT
                ( '|' + REPLACE(ma.[CompetencyName],' ','+'))
            FROM
                @tblTotalsSource ma
            ORDER BY
                ma.[CompetencyName] DESC
            FOR XML PATH('')
        ), 2, 1000) AS 'CompetenciesNames',
    SUBSTRING(
        (
            SELECT
                ( ',' + REPLACE(ra.[FunctionRequiredLevel]*10,' ','+'))
            FROM
                @tblTotalsSource ra
            FOR XML PATH('')
        ), 2, 1000) AS 'FunctionRequiredLevel',
    SUBSTRING( 
        (
            SELECT
                ( ',' + CAST(na.[ManagersAverageAssessment]*10 AS nvarchar(10)))
            FROM
                @tblTotalsSource na
            FOR XML PATH('')
        ), 2, 1000) AS 'ManagersAverageAssessment',
    SUBSTRING( 
        (
            SELECT
                ( ',' + CAST(oa.[GroupAverageAssessment]*10 AS nvarchar(10)))
            FROM
                @tblTotalsSource oa
            FOR XML PATH('')
        ), 2, 1000) AS 'GroupAverageAssessment',
    SUBSTRING( 
        (
            SELECT
                ( ',' + CAST(pa.[SelfAssessment]*10 AS nvarchar(10)))
            FROM
                @tblTotalsSource pa
            FOR XML PATH('')
        ), 2, 1000) AS 'SelfAssessment',
    SUBSTRING( 
        (
            SELECT
                ( ',' + CAST(qa.[TeamAverageAssessment]*10 AS nvarchar(10)))
            FROM
                @tblTotalsSource qa
            FOR XML PATH('')
        ), 2, 1000) AS 'TeamAverageAssessment',
    SUBSTRING( 
        (
            SELECT
                ( '|t++' + CAST([FunctionRequiredLevel] AS varchar(10)) + ',' + @RequiredColor + ',0,' + CAST(ROW_NUMBER() OVER(ORDER BY CompetencyName) - 1 AS varchar(2)) + ',9')
            FROM
                @tblTotalsSource
            FOR XML PATH('')
        ), 2, 1000) AS 'FunctionRequiredAverageLabel',
    SUBSTRING( 
        (
            SELECT
                ( '|t++' + CAST([ManagersAverageAssessment] AS varchar(10)) + ',' + @ManagersColor + ',' + CAST(@intManagersPosition AS varchar(2)) + ',' + CAST(ROW_NUMBER() OVER(ORDER BY CompetencyName) - 1 AS varchar(2)) + ',9')
            FROM
                @tblTotalsSource
            FOR XML PATH('')
        ), 2, 1000) AS 'ManagersLabel',
    SUBSTRING( 
        (
            SELECT
                ( '|t++' + CAST([GroupAverageAssessment] AS varchar(10)) + ',' + @GroupColor + ',' + CAST(@intGroupPosition AS varchar(2)) + ',' + CAST(ROW_NUMBER() OVER(ORDER BY CompetencyName) - 1 AS varchar(2)) + ',9')
            FROM
                @tblTotalsSource
                FOR XML PATH('')
        ), 2, 1000) AS 'GroupLabel',
    SUBSTRING( 
        (
            SELECT
                ( '|t++' + CAST([SelfAssessment] AS varchar(10)) + ',' + @SelfColor + ',' + CAST(@intSelfPosition AS varchar(2)) + ',' + CAST(ROW_NUMBER() OVER(ORDER BY CompetencyName) - 1 AS varchar(2)) + ',9')
            FROM
                @tblTotalsSource
            FOR XML PATH('')
        ), 2, 1000) AS 'SelfLabel',
    SUBSTRING( 
        (
            SELECT
                ( '|t++' + CAST([TeamAverageAssessment] AS varchar(10)) + ',' + @TeamColor + ',' + CAST(@intTeamPosition AS varchar(2)) + ',' + CAST(ROW_NUMBER() OVER(ORDER BY CompetencyName) - 1 AS varchar(2)) + ',10')
            FROM
                @tblTotalsSource
            FOR XML PATH('')
        ), 2, 1000) AS 'TeamLabel',
        (Count(src.[IdCompetency]) * 30) + 100 AS 'ControlHeight'
FROM 
    @tblTotalsSource src


SET NOCOUNT OFF
GO

Answer 1

don't you miss BEGIN and END in the beginnning and eding of procedure, respectively?

(create procedure blablabla as begin .... end)

Answer 2

If SQL isn't giving you a line number, try commenting out different statements and re-running the command

Answer 3

Search your stored procedure for instances of this string: += (you have a few of them). This combined operator is not supported by T-SQL,

So instead of this:

SELECT @intMyCounter += 1

you will need to do this instead:

SELECT @intMyCounter = @intMyCounter + 1

Answer 4

when you run your create procedure script in sql server management studio and you get an error, double click on the error message and it usually takes you to the line where the error occurs. Sometimes this is just the first line of a long SELECT query, but in this case it is fairly obvious, because it is a one line IF

you can not use += in the line:

IF IsNumeric(@intManagersPosition) = 1 BEGIN SELECT @intMyCounter += 1 END

should be:

IF IsNumeric(@intManagersPosition) = 1 BEGIN SELECT @intMyCounter = @intMyCounter+1 END

you do this three times, fix then and you are good to go.