June 28, 2017 at 9:37 am
Well not so much interesting as completely annoying! (Also rather long apologies)
We had an application running on SQL 2005, and eventually we have managed to get the users to agree that they had to move to a supported DBMS. The application supplier confirmed that they do support SQL 2016, so far all good. I built a new box and installed SQL 2016 DBE (nothing else required). A backup of the database was taken on the old 2005 server and restored on 2016, again with no problems. Users were migrated between the two servers. A new application server was built pointing at the new database, and the users commenced testing. After 4 weeks or so of not hearing anything, and the deadline of getting off Windows 2003 rapidly approching, we got the users to confirm that we can go live. A new backup of the old system was taken and restored on the new server, and the database upgraded to match the new version of the software - no issues were detected. After going live the users started reporting erros related to converting a character string into a date format. . . . That old chesnut . . . somewhere there is a conflict on 'Language' settings. We then advised the supplier and waited.
The Old server was US English at the server level and the Logins had US English as the default (No Database Default as it wasn't available in 2005). The new Server has British English as the Default, adn the Users are US English, but the Datbase is British English, so it's pretty obvious that the database Setting needs changing Simple Alter Database Set Default_Language = 1033 . . . Job done!
If only it were that simple, we now have contained databases, and the containment type is None, on order to change the database Language you have to set the containment type to Partial (at least), and that is where we run into issues. This is the script we are running.
USE [master]
EXEC sp_configure 'contained database authentication' , 1
RECONFIGURE
GO
ALTER DATABASE [TempTrain] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO
ALTER DATABASE [TempTrain] SET DEFAULT_LANGUAGE = 1033 WITH NO_WAIT
GO
Simple enough except that we get errors reported.
Configuration option 'contained database authentication' changed from 0 to 1. Run the RECONFIGURE statement to install.
Msg 468, Level 16, State 9, Procedure apspuRackUpNextUsed, Line 19 [Batch Start Line 4]
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "Latin1_General_100_CI_AS_KS_WS_SC" in the equal to operation.
Msg 12813, Level 16, State 2, Line 5
Errors were encountered in the procedure 'dbo.apspuRackUpNextUsed' during compilation of the object. Either the containment option of the database 'TempTrain' was changed, or this object was present in model db and the user tried to create a new contained database.
Msg 12836, Level 16, State 1, Line 5ALTER DATABASE statement failed. The containment option of the database 'TempTrain' could not be altered because compilation errors were encountered during validation of SQL modules. See previous errors.
Msg 5069, Level 16, State 1, Line 5ALTER DATABASE statement failed.
Msg 12807, Level 16, State 1, Line 7
The option 'default_language' cannot be set on non-contained database.
Msg 5069, Level 16, State 1, Line 7
ALTER DATABASE statement failed.
Now I'm really stumped. The procedure dbo.apspuRackUpNextUsed appears to be generated on the fly as part of the change containment type statement, but everything in the database and the server has the collation Latin1_General_CI_AS.
The only way round this that I can see, is to create a new database with the default language US English, migrate the structure, and then load the data from the problem database. it's quite a small DB at 4Gb, but lots of tables indexes and foreign keys etc, so sequencing is going to be tricky. I'm thinking of taking another look at Apex SQL Admin as I'm sure thats got me out of trouble like this in the past, but anyone else got any other ideas??
Thanks for reading this far 😀
June 28, 2017 at 1:29 pm
As a thought, did you change the compatability of the database after upgrading?
I tried looking up that SP on bing hoping to find something related to it (which I am guessing you did too) but came up empty.
When changing to a partially contained database, are you sure your database meets all of the requirements to migrate to a contained database? Some features are not supported and will cause that to fail. You cannot have replication, change data capture, or change tracking turned on for example.
In SQL versions prior to 2012 (I believe), you couldn't pick a language at the database level, it was set at the instance level. Since you say the instance is British, but users want things US, any reason you can't change it at the instance level?
But those are just my thoughts on it... I could be wrong. I've never needed to change the language at the database level before.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 29, 2017 at 1:57 am
Thanks for the reply.
The compatibility is set to 100 (sql 2008) which is what it was set to following the restore. The default language at the DB level is picked up from the server settings at the point of restore, which I change to British English upon installation of SQL Server, unless the supplier gives me a warning not to . . . two weeks after the fact, and indeed 6 weeks after the initial load for user 'testing' is just too late.
Not using any of the features that prevent changing the containment level, and it's not going to be set permanently, once the default language is set I'll swap it back.
Last time I had to struggle with this was changing Collation Sequences on a database moving from 2000 to 2005!
The worse part of it will be that most of the App runs fine, where I suspect the developers use an explict character date format that cannot be misinterpreted at the SQL level but one or two point the developer has been sloppy/lazy and uses an implicit conversion. Of course the suppliers just say that none of their other customers have this problem!
June 29, 2017 at 2:02 am
Changing the compatibility level to 130, made no difference, still get exactly the same errors.
June 29, 2017 at 2:52 am
As a thought, probably a long shot, but I wonder if a column in your database has a different collation to the actual database. Does this yield any results?USE TempTrain; --Guessing this is the name of your database name from your error message.
GO
SELECT o.name, c.name, c.collation_name
FROM sys.objects o
JOIN sys.columns c on o.object_id = c.object_id
WHERE o.type_desc = 'USER_TABLE'
AND collation_name <> 'Latin1_General_CI_AS';
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 29, 2017 at 4:32 am
Thanks Thom,
Interestingly it did find two columnsname name collation_name
tblAlarmHistory AlarmMessage SQL_Latin1_General_CP1_CI_AS
tblAlarmHistory Note SQL_Latin1_General_CP1_CI_AS
As I Said earlier the stored procedure that errorsos is created 'on the fly'as part of the changing containment level process. The good thing is that either as a result of the error or not bothering the stored procedure is left behind. so I though just for fits and giggles lets have a look at what it contains
CREATE PROCEDURE apspuRackUpNextUsed
AS
BEGIN
declare @tablecnt integer
declare @tablename char(30)
declare @keyfields char(128)
declare @nexttablestr nvarchar(4000)
declare @tablecode int
declare tnames_cursor cursor for
select
rt.tablename, rt.keyfields, rt.tablecode
from
dbo.tblRosterTable rt,
sysobjects o,
syscolumns c
where
upper(o.Name) = upper(rt.TableName) and
upper(c.Name) = upper(rt.KeyFields) and
o.id = c.id and
c.type <> 61 and
o.id = object_id(o.name)
open tnames_cursor
set @TableCnt = 0
fetch next from tnames_cursor into @tablename, @keyfields, @tablecode
while (@@fetch_status <> -1) and @tablecnt < 1000
begin
if (@@fetch_status <> -2)
begin
set @tablecnt = @Tablecnt + 1
set @nexttablestr = 'if ((select max(' + rtrim(@KeyFields) + ') from ' + rtrim(@tablename) + ') > (select LastKey from tblRosterTable where TableCode = ' + CAST(@tablecode AS varchar(12)) + '))'
set @nexttablestr = @nexttablestr + N' update dbo.tblRosterTable set LastKey = (10 + (select max(' + rtrim(@KeyFields) + ') from ' + rtrim(@tablename) + ')) where TableCode = ' + CAST(@tablecode AS varchar(12))
execute sp_executesql @nexttablestr
end
fetch next from tnames_cursor into @tablename, @keyfields, @tablecode
end
deallocate tnames_cursor
END
Not sure if it helps though as neither of the two tables with the alternative collation are in the procedure, in fact it doesn't help as executing the procedure as it exists doesn't cause any errors, so I think it's when it is trying to create procedure for the next object??
June 29, 2017 at 12:36 pm
2 thoughts:
1 - are you on the latest SP/CU?
2 - I think this error might have a bit more to it:
Errors were encountered in the procedure 'dbo.apspuRackUpNextUsed' during compilation of the object. Either the containment option of the database 'TempTrain' was changed, or this object was present in model db and the user tried to create a new contained database.
My thought on this is actually related to your last post and the above message. What I THINK it is saying (and could be wrong) is that the object dbo.apspuRackUpNextUsed exists AND you tried to create a new contained database.
My thought on fixing this would be to drop (or more likely RENAME as I dislike dropping anything from system databases) apspuRackUpNextUsed and then try again?
Mind you I've not run into this problem, I'm just going based on the error message.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply