STORED PROCEDURE working in my local test machine cannot be created in production environment.
- by Marcos Buarque
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
