SQL Server Collation Setting

[Nina Tulic]

Hi,

When setting up a new database we typically use the default SQL Server collation, however we have an install we need to do where the SQL instance must be set to SQL_Latin1_General_CP1_CI_AS to meet the requirements of another application. We would prefer not to have to setup another instance just for Jiwa. From a Jiwa perspective does it matter what collation the instance is set to?

Thanks
Nina

[Mike.Sheen]

Hi Nina,

As a general rule, it shouldn't matter.

Where there will be a problem is any stored procedures which utilise temp tables or memory tables will have an issue if a subsequent query attempts to join that temp or memory table to a table in the schema - unless the temp table has the columns being joined with the COLLATE database_default attribute defined. This would only affect reports using stored procedures which have temp or memory tables - as we don't use temp or memory tables in the application itself.

We've modified most of our stored procedures to protect against this problem, but there may be some still with issues. If you do find a report which when run reports a collation conflict error, then this is the cause.

The good news is the fix is relatively easy, and can be done yourself.

Inside the stored procedure if you see something like this:

Code: Select all

CREATE TABLE #TempTable
  (
   InvoiceLineID CHAR(20),
...


Then it just needs to be modified like so:

Code: Select all

CREATE TABLE #TempTable
  (
   InvoiceLineID CHAR(20) COLLATE database_default,
...


Any CHAR or VARCHAR columns of temp or memory tables should have the column definition appended with COLLATE database_default and then any collation conflict errors will be resolved.

If you do come across any reports which we ship with that reports a collation conflict error, then please do report it and we'll ammend the stored procedure backing the report in the next release, and we'll also give you a script to fix it in the version you are currently on.

Mike

[Nina Tulic]

Thanks Mike,

This is very helpful. Greatly Appreciated.

Nina

[Nina Tulic]

Hi,

As a follow up question, we noticed that when you set up a Jiwa Demo database or Jiwa Blank database from the connection program the collation is always Latin1_General_CI_AS. Is there any way we can change this so it inherits the default instance collation? We are looking for SQL_Latin1_Genearl_CP1_CI_AS.

Thanks
Nina

[Mike.Sheen]

Hi Nina,

I've had a crack at this and it seems possible - but a bit of effort is required to come up with a series of scripts to do this.

I've spent half an hour on it so far but I think only another 30 minutes to an hour would be required to finish it off - but then there is testing to make sure the resultant database works.

The following DOES NOT work, and I've added comments of where I got to - not much use to you, I know - but I wanted to show some effort and progress has been made - I'm going to leave it where it is and come back to it - but you're more than welcome to continue on with it yourself or if it's critical to you then we can expedite the work through a billable service issue.

Mike



Backup your database first.

Firstly, there is a function which will need to be dropped and then re-created later:

Code: Select all

DROP FUNCTION [fn_split]

Then you need to put the database into single user mode:

In the below replace JiwaDemo with your database name. Make sure you run this on the master database, and no other user can have an open connection to the database - not even SQL Management studio.

Code: Select all

ALTER DATABASE JiwaDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Then you need to change the default collation for the database - run this against the master database and again replace JiwaDemo with your database name :

Code: Select all

ALTER DATABASE JiwaDemo COLLATE SQL_Latin1_General_CP1_CI_AS

Now a script needs to be run to ALTER every column of every table with the new collation - the below script outputs text which you need to run against the database:
**** NOTE: The output when run currently fails due to foreign keys - they will need to be dropped before running and re-created after altering the collation - I suspect indexes as well will need attention

Code: Select all

DECLARE @TableName VARCHAR(500)
DECLARE @CollationName VARCHAR(500)
DECLARE @ColumnName VARCHAR(500)
DECLARE @SQLText VARCHAR(MAX)
DECLARE @DataType VARCHAR(500)
DECLARE @CharacterMaxLen INT
DECLARE @IsNullable VARCHAR(50)

SET @CollationName = 'SQL_Latin1_General_CP1_CI_AS'

DECLARE MyTableCursor CURSOR FOR SELECT name FROM sysobjects
WHERE type = 'U'
ORDER BY name

OPEN MyTableCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE MyColumnCursor Cursor
        FOR
        SELECT COLUMN_NAME,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
            IS_NULLABLE from information_schema.columns
            WHERE table_name = @TableName AND  (Data_Type LIKE '%char%'
            OR Data_Type LIKE '%text%') AND COLLATION_NAME <> @CollationName
            ORDER BY ordinal_position
        Open MyColumnCursor

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType,
              @CharacterMaxLen, @IsNullable
        WHILE @@FETCH_STATUS = 0
            BEGIN
         IF @CharacterMaxLen = -1 OR @CharacterMaxLen > 8000
         BEGIN
            SET @SQLText = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' +
                 'TEXT' +
                 ' COLLATE ' + @CollationName + ' ' +
                 CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END
         END
         ELSE
            SET @SQLText = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN [' + @ColumnName + '] ' +
              @DataType + '(' + CASE WHEN @CharacterMaxLen = -1 THEN 'MAX' WHEN @CharacterMaxLen >= 8000 THEN 'MAX' ELSE CAST(@CharacterMaxLen AS VARCHAR(50)) END +
              ') COLLATE ' + @CollationName + ' ' +
              CASE WHEN @IsNullable = 'NO' THEN 'NOT NULL' ELSE 'NULL' END

            PRINT @SQLText

        FETCH NEXT FROM MyColumnCursor INTO @ColumnName, @DataType,
              @CharacterMaxLen, @IsNullable
        END
        CLOSE MyColumnCursor
        DEALLOCATE MyColumnCursor

FETCH NEXT FROM MyTableCursor INTO @TableName
END
CLOSE MyTableCursor
DEALLOCATE MyTableCursor

Then set the database back to multi-user mode - run this against the master database, and again replace JiwaDemo with your database name.:

Code: Select all

ALTER DATABASE JiwaDemo SET MULTI_USER WITH ROLLBACK IMMEDIATE

The recreate the fn_split function we deleted earlier (make sure you run this against your database you have changed the collation on):

Code: Select all

CREATE FUNCTION [dbo].[fn_split](@sText varchar(8000), @sDelim varchar(20) = '')
   RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
   DECLARE @idx int,
   @value varchar(8000),
   @bcontinue bit,
   @iStrike int,
   @iDelimlength int

   IF @sDelim = 'Space'BEGIN
      SET @sDelim = ' '
   END

   SET @idx = 0
   SET @sText = LTrim(RTrim(@sText))
   SET @iDelimlength = DATALENGTH(@sDelim)
   SET @bcontinue = 1

   IF(Len(@sText) = 0)
   RETURN

   IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty')) BEGIN
      WHILE @bcontinue = 1
      BEGIN
         --If you can find the delimiter in the text, retrieve the first element and
         --insert it with its index into the return table.

         IF CHARINDEX(@sDelim, @sText)>0 BEGIN
            SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
            BEGIN
               INSERT @retArray (idx, value)
               VALUES (@idx, @value)
            END

            --Trim the element and its delimiter from the front of the string.
            --Increment the index and loop.
            SET @iStrike = DATALENGTH(@value) + @iDelimlength
            SET @idx = @idx + 1
            SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))

         END
         ELSE BEGIN
            --If you can't find the delimiter in the text, @sText is the last value in
            --@retArray.
            SET @value = @sText
            BEGIN
               INSERT @retArray (idx, value)
               VALUES (@idx, @value)
            END
            --Exit the WHILE loop.
            SET @bcontinue = 0
         END
      END
   END
   ELSE BEGIN
      WHILE @bcontinue=1 BEGIN
         --If the delimiter is an empty string, check for remaining text
         --instead of a delimiter. Insert the first character into the
         --retArray table. Trim the character from the front of the string.
         --Increment the index and loop.
         IF DATALENGTH(@sText)>1 BEGIN
            SET @value = SUBSTRING(@sText,1,1)
            BEGIN
               INSERT @retArray (idx, value)
               VALUES (@idx, @value)
            END
            SET @idx = @idx+1
            SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)

         END
         ELSE BEGIN
            --One character remains.
            --Insert the character, and exit the WHILE loop.
            INSERT @retArray (idx, value)
            VALUES (@idx, @sText)
            SET @bcontinue = 0
         END
      END

   END

   RETURN
END
GO

Then grant permissions:

Code: Select all

GRANT_ALL_USER_TABLES