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:
- Migrate from Azure to local
- Create new Azure SQL Db with "Latin1_General_CI_AS" collation
- Run Data Migration Assistant to push Local SQL Server DB to new Azure SQL Db
- 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!