Creation of database with different collation fails

Question

question

BGSisson asked Mar 18, '20

Creation of database with different collation fails

I'm trying to install SSRS 2019 on an instance with existing databases created with the standard collation (SQL_Latin1_General_CP1_CI_AS) and it appears SSRS wants Latin1_General_CI_AS_KS_WS.

There are objects in those databases that use schema binding. When the SSRS installation process failed with error messages about not being able to change the collation because of schema binding I decided to create the SSRS databases manually and encountered the same issues.

Below is the database creation script and one example of the error about the schema bound object.

The instance is a sandbox, but I would still rather not have to screw around with the existing databases. Is there another way around this issue?

Thanks in advance for any suggestions you can offer.

CREATE DATABASE [ReportingServices]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'ReportingServices', FILENAME = N'S:\2019\Data\ReportingServices.mdf' , SIZE = 270336KB , FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'ReportingServices_log', FILENAME = N'S:\2019\Data\ReportingServices_log.ldf' , SIZE = 102400KB , FILEGROWTH = 65536KB )
 COLLATE Latin1_General_CI_AS_KS_WS
GO

Msg 5075, Level 16, State 1, Line 1
The object 'dbo.name_value_pairs.name.c' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 1806, Level 16, State 1, Line 1
CREATE DATABASE failed. The default collation of database 'ReportingServices' cannot be set to 'Latin1_General_CI_AS_KS_WS'.

collation create database

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ · Mar 19, 2020 at 08:12 AM

Does the database creation script work initially?

Once you've created the database, how are you populating the database with SSRS objects?

0 ·

BGSisson Kev Riley ♦♦ · Mar 19, 2020 at 10:23 AM

The script and error message in my original post are all there is. The script fails to create the database outside of the SSRS installation before I can even try to create SSRS objects. I can see the new database files appear on disk, then disappear upon failure. It feels like the database is created, SQL tries to alter the other databases, and when that fails the new database is dropped. That’s just an assumption on my part though. I’ve installed SSRS many times using the same databases on older versions of SQL with no issues at all, so apparently 2019 is doing something different.

0 ·

Answer 1 · 2020-03-19T14:24:47Z

BGSisson answered Mar 19, '20

Mystery solved. I have schema-bound objects in the model database and that's what was breaking the creation. I had forgotten all about that, and once I dropped them things went smoothly. I recently started adding common utility objects to the model database and some of them use schema binding for performance. So I guess I either need to drop the binding in model, add them to each database as I create it, or add them back to model with binding after I'm done with the SSRS installation.

Thanks for assistance.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2020-03-19T10:23:27Z

BGSisson answered Mar 19, '20

,

The database never gets gets successfully created. The script and the error message are from the attempt to create the database manually. I can see the database files on disk initially, but they disappear after the failure. I don't know this, but It feels like the new database is created, then an attempt is made to change the collation of the rest of the databases, and when that fails, the SSRS database is dropped. I never get to the point of creating SSRS objects, so this is a basic SQL issue.

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ · Mar 19, 2020 at 10:34 AM

That's odd as the CREATE DATABASE statement works fine on my machine, and I have other databases with different collations.

Is this a native SSRS, or Sharepoint mode?

0 ·

BGSisson Kev Riley ♦♦ · Mar 19, 2020 at 10:36 AM

Native. But again, this script is being run outside of the SSRS installation. Since it was failing during the installation I was going to create the database manually, then run the installation and point the installation to the manually created databases. Do you have schema-bound objects in the other databases?

0 ·

Kev Riley ♦♦ · Mar 19, 2020 at 10:43 AM

What happens if you try to create another database, not named 'ReportingServices' - but with the Latin1_General_CI_AS_KS_WS collation

0 ·

question