I learnt today that you cannot perform an ALTER DATABASE operation within an 'implicit' or 'explicit' TRANSACTION.
I have a bunch of databases that i want to change the COLLATION of...ideally i would like them all to not have one set and just inherit from the server collation setting...but it appears you cannot do:
ALTER DATABASE MyDb COLLATE ''
As such...so the idea was to explicitly set the collation to the same value as the Server.
But when i tried to do them all in a TRANSACTION:
ALTER DATABASE TMP_CHCR_DEV COLLATE SQL_Latin1_General_CP1_CI_AS
ALTER DATABASE database1 collate Latin1_General_CI_AS;
ALTER DATABASE databaseN collate Latin1_General_CI_AS;
WAITFOR DELAY '00:00:30'
-- TEST the TRANSACTION
SELECT name, collation_name FROM sys.databases
where collation_name != 'Latin1_General_CI_AS'
-- This should return no results if the above has worked.
I got this error:
"ALTER DATABASE statement not allowed within multi-statement transaction."
Which is because ALTER DATABASE statements "must run in Autocommit" mode...
See this article for more info on Autocommit mode
The solution is to run each of the statements one by one...