+5hi,
As an ISV we install our end-user databases on our customer’s servers. That means that we cannot always set the default collation of the server. How can we set the collation for a new Thinkwise database generated with the Thinkwise Deployment Center?
Or could it be set in the model?
Best answer by Mark Jongeling
I'm not aware of any native possibility to preset the collation of a new database. You can opt to uitlize the Manual code file for that. In there you can verify the collation and alter it when it doesn't match the collation you want the database to be.
Dynamic SQL example; replace [COLLATION] with the desired collation:
if (select collation_name
from sys.databases
where name = db_name()) <> '[COLLATION]'
begin
declare @alter_database_collation nvarchar(max) =
'ALTER DATABASE ' + quotename(db_name()) + ' COLLATE [COLLATION]';
exec sp_executesql @alter_database_collation;
print 'Database collation has been changed to [COLLATION]';
end
go
+23Hi Tejo,
By default, any new installation (or deployment / creation of a new database) will use the Server collation unless otherwise specified in the DB code file script.
… If <the collation is> not specified, the database is assigned the default collation of the instance of SQL Server.
Source: CREATE DATABASE (Transact-SQL) - SQL Server | Microsoft Learn
To always ensure compatibility between the database and the tepdb collation, you can set the collation of alphanumeric fields when creating temporary tables to "Database_default”. This is what the Software Factory also does as not all of our users use the same collation for the Software Factory as for their TempDB.
+5Thanks
+23I'm not aware of any native possibility to preset the collation of a new database. You can opt to uitlize the Manual code file for that. In there you can verify the collation and alter it when it doesn't match the collation you want the database to be.
Dynamic SQL example; replace [COLLATION] with the desired collation:
if (select collation_name
from sys.databases
where name = db_name()) <> '[COLLATION]'
begin
declare @alter_database_collation nvarchar(max) =
'ALTER DATABASE ' + quotename(db_name()) + ' COLLATE [COLLATION]';
exec sp_executesql @alter_database_collation;
print 'Database collation has been changed to [COLLATION]';
end
go
+8One common issue that may occur is character misinterpretation: If the new collation uses a different character encoding than the old one, data containing special characters or symbols might be misinterpreted, leading to corruption. Which leads to data loss: In some cases, characters unsupported by the new collation might be dropped or replaced with substitute characters, resulting in data loss.
I’d strongly advice to set the collation as soon as possible, preferably when creating the database.
Just my 2 cents.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
