School Item Crashes with SqlException

[Ghislain Hachey]

When I go to Schools -> List and filter to see some school I can see schools in a table. School ID have a clickable link changing to state site.schools.list.item but this fails and throws the error shown in the image on the backend.

[Brian Lewis]

Nasty.

This occurs in circumstances where

• the collation sequence of the database differs from the default collation of the server; and

• a stored procedure uses a temporary table, and tries to join it to a database table on a string field.

The temp table and database table will have different collation sequences, giving rise to this error.

SQl server unfortunately has used different values for the default collation over the years, but I'm surprised you ended up with

SQL_Latin1_General_CP1_CI_AS in this day and age.

Anyway, I can pretty quickly see where this has come from. For the record, the usual fix is to replace the temporary table ( which gets created in the temp db, and therefore has the collation of the temp db = server collation) with a table variable.

I'll email the fix.

[Ghislain Hachey]

On 4/12/16 10:30, Brian Lewis wrote:

Well, this was a default installation of MS SQL Server Express.

Should we document the SQL fix in this ticket? If it happened to me on a fresh basic install, it is bound to happen again :)

[Ghislain Hachey]

I confirm the SQL fix you sent works. Should we include it here

[Brian Lewis]

It seems from a quick look around that the default collation sequence may depend on the regional settings of the target machine... In any case , for a brand new installation we should go with Latin1_General_CP1_CI_AS

However, there will be circumstances where you can;t control the server collation so we need to fix any potential references to temp tables in stored procs as indicated above. I did at one stage spend some time doing this - so it probably won't appear often.

Need to review any stored proc containing a CREATE TABLE statement...

[Brian Lewis]

Propogating changes to the database is a bigger issue, and the approach we are using is to store in the git \database folder a schema file created by SQL DELTA. This represents the schema of all tables, views, stored procs etc... any database artefact. Sql Delta can use this snapshot to generate the necessary sql script to upgrade the target database to match that snapshot.

[Brian Lewis]

Should we mark this as resolved (by the updated Stored Procedure)?

[Ghislain Hachey]

Well, does the stored procedure fix the problem or one must run the Stored Procedure when the problem arises? If the later then I think it is not resolved. But minor and low priority since this does not necessarily happen often or at all. Maybe we document the steps to take and attach the Stored Procedure and call it CLOSED as oppose to RESOLVED.

[Brian Lewis]

the script below redefines the stored procedure - ie changes the code - which fixes the error.

USE [SIEMIS]
GO

/****** Object:  StoredProcedure [pEnrolmentRead].[SchoolAnnualSummary]    Script Date: 12/04/2016 12:19:36 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:          Brian Lewis
-- Create date: 1 10 2014
-- Description:     summary of key survey statistics by year
-- =============================================
ALTER PROCEDURE [pEnrolmentRead].[SchoolAnnualSummary]
       @SchoolNo nvarchar(50) = null
       , @SurveyYear int = null
WITH EXECUTE AS 'Pineapples'
-- we'll let you read the teacher counts here, even if you do not have teacher read
-- hence the need for the execute as
AS
BEGIN
       -- SET NOCOUNT ON added to prevent extra result sets from
       -- interfering with SELECT statements.
       SET NOCOUNT ON;

-- strategy is temp table with each required field, records are added in sets
-- then summed to bring all the fields together
       DECLARE @tmpAS TABLE
       (
             schNo nvarchar(50)
             , svyYear int
             , minClass nvarchar(10)
             , maxClass nvarchar(10)
             , TeacherSurveyCount int
             , ClassroomCount int
       )

-- to begin make sure we have every life year for the school in the table
       INSERT INTO @TmpAS
       (schNo, svyYear)
       Select schNo, svyYear
       FROM SchoolLifeYears 
       WHERE
             (
                    (@SchoolNo is null or @SchoolNo = SchNo)
                    AND (@SurveyYear is null or @SurveyYEar = svyYEar)
             )

-- add the teacher survey count records        
       INSERT INTO @tmpAS
             (SchNo, svyYear, TeacherSurveyCount)
       SELECT 
             schNo, svyYear, count(tchsID)
       FROM
             SchoolSurvey SS
             INNER JOIN TeacherSurvey TS
             ON Ss.ssID = TS.ssID
       WHERE
             (
                    (@SchoolNo is null or @SchoolNo = SchNo)
                    AND (@SurveyYear is null or @SurveyYEar = svyYEar)
             )
       group by schNo, svyYear


-- add the set of classroom records
       INSERT INTO @tmpAS
             (SchNo, svyYear, ClassroomCount)
       SELECT 
             schNo, svyYear, count(rmID)
       FROM
             SchoolSurvey SS
             INNER JOIN Rooms 
             ON Ss.ssID = Rooms.ssID
       where
             (
                    (@SchoolNo is null or @SchoolNo = SchNo)
                    AND (@SurveyYear is null or @SurveyYEar = svyYEar)
             )
             and rmType = 'CLASS'
       group by schNo, svyYear

-- calculate the minimum and maximum levels for enrol
------
------Select SchNo
------       , SvyYear
------       , MinYear
------       , MaxYear
------       , min(case when L.lvlYear = MinYear then L.codeCode else null end) MinLevelCode
------       , min(case when L.lvlYear = MinYear then L.codeDescription else null end) MinLevel
------       , min(case when L.lvlYear = MaxYear then L.codeCode else null end) MaxLevelCode
------       , min(case when L.lvlYear = MAxYear then L.codeDescription else null end) MaxLevel
------
------
------FROM 
------ Enrollments E
------ 
------ INNER JOIN TRLevels L
------ ON E.enLevel = L.codeCode
------ INNER JOIN
------ (
------       Select S.SchNo
------              , S.svyYEar
------              , S.ssID
------              ,min(lvlYear) MinYear
------              , max(lvlYear) MaxYear
------       from   SchoolSurvey S
------              INNER JOIN   Enrollments E
------              ON S.ssID = E.ssID
------              INNER JOIN lkpLevels L
------              ON E.enLevel = L.codeCode
------       GROUP BY S.SchNo, S.svyYEar, S.ssID
------ ) MiniMax
------ ON MiniMax.ssID = E.ssID
------GROUP BY SchNo, svyYEar, minYEar, maxYear

       Select 
             TMP.schNo
             , TMP.svyYear
             , SS.ssSchType
             , SS.ssAuth
             , SS.ssEnrol
             , SS.ssEnrolM
             , SS.ssEnrolF
             , SS.ssID
             , SS.ssPrinFirstName
             , SS.ssPrinSurname
             , SS.ssNote
             , LMin.CodeDescription minLevel
             , LMax.CodeDescription maxLevel
             , ELS.MaxLevelEnrol
             , LMaxEnrol.codeDescription  maxEnrolLevel
             , TeacherSurveyCount
             , ClassroomCount
             , case when TeacherSurveyCount = 0  then null else cast(SS.ssEnrol as float)/TeacherSurveyCount end PTR

       FROM 
             (
             Select

                    schNo 
                    , svyYear 
                    , min(minClass) minClass
                    , max(maxClass) maxClass
                    , max(TeacherSurveyCount) TeacherSurveyCount
                    , max(ClassroomCount) ClassroomCount
             FROM
                    @tmpAS
             GROUP BY
                    schNo, svyYear
             ) TMP
             LEFT JOIN SchoolSurvey SS
                    ON ss.schNo = Tmp.SchNo
                    AND SS.svyYear = TMP.svyYear
             LEFT JOIN pEnrolmentRead.ssIDEnrolmentLevelSummary ELS
                    ON SS.ssID = ELS.ssID
             LEFT JOIN TRLevels LMin
                    On ELS.LowestLevel = LMin.codeCode
             LEFT JOIN TRLevels LMax
                    ON ELS.HighestLevel = LMAx.CodeCode
             LEFT JOIN TRLevels LMaxEnrol
                    ON ELS.LEvelOfMaxEnrol = LMaxEnrol.CodeCode


END


GO

[Ghislain Hachey]

This is properly documented in case the problem arise. If it arises regularly it should be re-opened and looked into more thoroughly to identify the root cause.