SQL Error on "Pages Selector" control

Michael Legacy asked on April 24, 2019 17:03

Getting the following errors when using a "Pages Selector" control. Haven't altered the CMS project at all, and this is the first time I'm using a Pages Selector.

Any tips on how to fix this error? I don't want to just go altering collations on the DB willy nilly.

Image Text

Correct Answer

Michael Legacy answered on April 24, 2019 19:57

Got this figured out.

For anyone using Azure SQL Databases:

On creation of your Azure SQL DB, make sure you select "Latin1_General_CI_AS", or whatever your default local collation is, as your collation type if you don't want to run into out-of-the-box issues. There were collation errors either on an index or with pre-existing tables. I'm sure a DBA could fix these issues easily, but that's not my forte. Anyone with more experience can chime in if they like.

My process to fix this was:

  1. Migrate from Azure to local
  2. Create new Azure SQL Db with "Latin1_General_CI_AS" collation
  3. Run Data Migration Assistant to push Local SQL Server DB to new Azure SQL Db
  4. You'll need to change the collation of any columns made while the old collation existed. This will be most prominent in your Page Type tables if you've created new Page Types or Fields on those page types.

To find which columns are the incorrect type, use this SQL:

USE YourDatabaseName GO SELECT s.name as 'Schema_Name', t.name as Table_Name,c.name AS Column_Name,c.collation_name AS Collation FROM sys.schemas s INNER JOIN sys.tables tON t.schema_id = s.schema_id INNER JOIN sys.columns cON c.object_id = t.object_id WHERE collation_name = 'SQL_Latin1_General_CP1_CI_AS' ORDER BY Column_Name

Here is the SQL used to update collation on a per-column basis. Not that you may want to automate this if you have many columns that need changed.

USE YourDatabaseName GO ALTER TABLE [dbo].[table_name] ALTER COLUMN ColumnName NVARCHAR(MAX) COLLATE Latin1_General_CI_AS NULL GO

After these steps you should be good to go!

0 votesVote for this answer Unmark Correct answer

Recent Answers

Michael Legacy answered on April 24, 2019 17:10 (last edited on April 24, 2019 17:10)

Plot thickens, this error doesn't happen on my local database, only after we migrate the database up to Azure SQL Db.

Does the Data Migration Assistant alter collations? Anyone have experience migrating Kentico from a local SQL Server to Azure SQL Db? Did you run into this problem?

0 votesVote for this answer Mark as a Correct answer

Dmitry Bastron answered on April 24, 2019 19:26

Hi Mikhael,

I've faced absolutely the same problem with Azure deployment and ended up redeploying the project to Azure. It was working locally but showed absolutely the same error on Azure. There is no easy way to change the collation for existing tables.

There were my steps:

  1. Create an empty Azure SQL database with SQL_Latin1_General_CP1_CI_AS collation
  2. Deploy your Kentico solution to Azure, clean all connection strings and run. As a result it will trigger the database creation dialog like this one where you can point Kentico wizard to a fresh database with the corre3ct collation
  3. Move all the Kentico items (page types, pages, translations, etc.) via Content Staging or Import/Export

Hope this helps!

0 votesVote for this answer Mark as a Correct answer

Michael Legacy answered on April 24, 2019 19:59 (last edited on April 24, 2019 20:00)


Thanks for your answer! I was able to make a new Azure SQL DB and collate it as Latin1_General_CI_AS, which was the current collation of my local SQL DB.

I just had to change the collation of each column in my custom Page Type fields myself, as stated above. Luckily, i'm very early on in development, so I only had to re-collate like 10 columns.

I will remember your process as well, that's very useful!

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.