September 12, 2019 at 4:29 pm
Hi,
We recently built a server using a prebuilt AWS AMI and the default collation isn't what the application vendor asked for.
We ran the sqlservr -m -T4022 -T3659 -s"SQL2017" -q"somecollation"
command which seems to have changed the DB collations but the default server collation seems to still be the same
SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation'
SELECT name, collation_name FROM sys.databases --WHERE name = 'master';
Returns
Server Collation
SQL_Latin1_General_CP1_CI_AS
name collation_name
master Latin1_General_CI_AS
tempdb Latin1_General_CI_AS
model Latin1_General_CI_AS
msdb Latin1_General_CI_AS
Boohoo UAT Live2 Latin1_General_CI_AS
Sage200Configuration Latin1_General_CI_AS
Draycir.SDC Latin1_General_CI_AS
KinspeedWebExtra SQL_Latin1_General_CP1_CI_AS
Sage200_DemoData Latin1_General_CI_AS
But when you open SQL server and try and open a user up it displays a warning
TITLE: Microsoft SQL Server Management Studio
------------------------------
Cannot show requested dialog.
------------------------------
ADDITIONAL INFORMATION:
Cannot show requested dialog. (SqlMgmt)
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation. (Microsoft SQL Server, Error: 468)
-
This is for a new UAT server and I'm just about to build the Prod one so keep to resolve and avoid on the new one!
September 13, 2019 at 4:04 pm
you will get this forever - especially if you use temp tables.
my advice - scrap it and rebuild, you will spend less time doing that than the hours of bug chasing because of collation issues
MVDBA
September 17, 2019 at 8:57 pm
I've had this happen to me too. The quickest way I've found to resolve this is to uninstall SQL server then reinstall again using the correct code page / collation settings required for the hosted database.
September 19, 2019 at 11:00 pm
Don't use pre-built AWS SQL AMIs. They cost more (unnecessarily). Just stand up an EC2 instance and you get to configure it exactly as needed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply