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