Error Upgrading from 3.8 - 4.2

Lee_Smith · 30 August 2016 10:35

Seems there is a database error on upgrade;

Database upgrade logs:
Beginning database upgrade
Fetching list of already executed scripts.
Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0043 - Increase Package Column Widths.sql’
SQL exception has occured in script: 'Octopus.Core.UpgradeScripts.Script0043 - Increase Package Column Widths.sql’
Script block number: 0; Block line 1; Message:
System.Data.SqlClient.SqlException (0x80131904): The object ‘PK_NuGetPackage_Id’ is dependent on column ‘Id’.
ALTER TABLE ALTER COLUMN Id failed because one or more objects access this column.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
ClientConnectionId:80790b02-35b4-4d63-80d3-b56609e654fa
Error Number:5074,State:1,Class:16
Upgrade failed due to an unexpected exception:
System.Data.SqlClient.SqlException (0x80131904): The object ‘PK_NuGetPackage_Id’ is dependent on column ‘Id’.
ALTER TABLE ALTER COLUMN Id failed because one or more objects access this column.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
at DbUp.Engine.UpgradeEngine.PerformUpgrade()
ClientConnectionId:80790b02-35b4-4d63-80d3-b56609e654fa
Error Number:5074,State:1,Class:16
—> System.Data.SqlClient.SqlException: The object ‘PK_NuGetPackage_Id’ is dependent on column ‘Id’.
ALTER TABLE ALTER COLUMN Id failed because one or more objects access this column.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
at DbUp.Engine.UpgradeEngine.PerformUpgrade()
— End of inner exception stack trace —
at Octopus.Core.Initialization.DatabaseUpgrader.RunScripts(IRelationalStore store, IScriptProvider scriptProvider, Boolean runEveryTime) in Y:\Work\refs\tags\3.4.2\source\Octopus.Core\Initialization\DatabaseUpgrader.cs:line 55
at Octopus.Core.Initialization.DatabaseUpgrader.Upgrade(IRelationalStore store) in Y:\Work\refs\tags\3.4.2\source\Octopus.Core\Initialization\DatabaseUpgrader.cs:line 27
at Octopus.Core.Initialization.StoreInitializer.Initialize() in Y:\Work\refs\tags\3.4.2\source\Octopus.Core\Initialization\StoreInitializer.cs:line 22
at Octopus.Server.OctopusServerEngine.Start() in Y:\Work\refs\tags\3.4.2\source\Octopus.Server\OctopusServerEngine.cs:line 59
at Octopus.Server.Commands.RunCommand.Start() in Y:\Work\refs\tags\3.4.2\source\Octopus.Server\Commands\RunCommand.cs:line 39
at Octopus.Shared.Startup.AbstractCommand.Octopus.Shared.Startup.ICommand.Start(String[] commandLineArguments, ICommandRuntime commandRuntime, OptionSet commonOptions) in Y:\Work\refs\tags\3.4.2\source\Octopus.Shared\Startup\AbstractCommand.cs:line 58
at Octopus.Shared.Startup.OctopusProgram.Start(ICommandRuntime commandRuntime) in Y:\Work\refs\tags\3.4.2\source\Octopus.Shared\Startup\OctopusProgram.cs:line 221
at Octopus.Shared.Startup.WindowsServiceHost.<>c__DisplayClass1_0.b__0() in Y:\Work\refs\tags\3.4.2\source\Octopus.Shared\Startup\WindowsServiceHost.cs:line 19
at Octopus.Shared.Startup.WindowsServiceAdapter.RunService() in Y:\Work\refs\tags\3.4.2\source\Octopus.Shared\Startup\WindowsServiceAdapter.cs:line 64
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

The database is hosted in a RDS instance at AWS.

Can you advise?

Michael_Richardson · 31 August 2016 03:30

Hi Lee,

Firstly, I’m very sorry to hear you’re experiencing this issue.

As quick summary, we have created an issue which you can follow, and should be resolved in the next release of Octopus (which will be available either today or tomorrow at the latest). This will allow the installer to run without that error.

In the meantime, since I assume your database is now in an inconsistent state (unless you have a backup), there is a something you can do:

If you execute the following against your Octopus database:

ALTER TABLE [dbo].[NuGetPackage] DROP CONSTRAINT [PK_NuGetPackage_Id]

Then re-start the Octopus server, either via the Octopus Manager application, or in Service Manager.

And the finally execute:

ALTER TABLE [dbo].[NuGetPackage] ADD  CONSTRAINT [PK_NuGetPackage_Id] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)

I believe that should work.

Some technical information:

The script that is giving you the error is trying to increase the width of the Id column in the NuGetPackage table. It is seemingly failing because that column is part of the Primary Key.

It’s interesting that you are the first person to report this problem, and the 3.4 upgrade has been run on >700 instances at this point. In my testing (on a SQL Server 2012 instance), the width of columns referenced by a Primary Key can be safely increased, and only throw the error you are seeing if they are decreased. This leads me to assume that your version of SQL Server implements this differently (assuming you haven’t increased the width of that column yourselves).

We sincerely apologize for any inconvenience caused. If there’s anything further we can do to help, please don’t hesitate to ask.

Regards,
Michael

Lee_Smith · 31 August 2016 12:39

Michael,

Thanks for the reply.

We are using Sql Express RDS Instance at AWS, so that may be the reason…

Thanks for the feedback (i had restored the database back to the backup pre upgrade)

Lee

Michael_Richardson · 31 August 2016 23:25

You’re welcome Lee.

Version 3.4.3 is now available, and should resolve the issue.

Apologies again,
Michael

Lee_Smith · 1 September 2016 07:25

Michael,

Next issue (excuse the dump of the logs, i can post on a GIST if you wish?):

2016-09-01 07:18:35.1572 7 INFO Beginning database upgrade
2016-09-01 07:18:35.1572 7 INFO Fetching list of already executed scripts.
2016-09-01 07:18:35.2312 7 INFO Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0000 - Fix wrong schema.sql’
2016-09-01 07:18:35.2612 7 INFO Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0040RemoveTemplateScriptStepPropertiesThatDoNotApplyToTheScriptSource.cs’
2016-09-01 07:18:35.5062 7 INFO Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0041FixOrDeleteChannelReferencesForVariablesAndReleaseCreationOptions.cs’
2016-09-01 07:19:09.7535 7 INFO Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0042 - Dashboard view potential slowness fix.sql’
2016-09-01 07:19:09.7675 7 INFO Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0043 - Increase Package Column Widths.sql’
2016-09-01 07:19:09.7985 7 INFO Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0043 - Index in EventRelatedDocument.sql’
2016-09-01 07:19:09.9105 7 INFO Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0044 - FixMutexIdLength.sql’
2016-09-01 07:19:10.0515 7 INFO Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0044 - Octopus 3.4 Schema.sql’
2016-09-01 07:19:10.1425 7 INFO SQL exception has occured in script: 'Octopus.Core.UpgradeScripts.Script0044 - Octopus 3.4 Schema.sql’
2016-09-01 07:19:10.1425 7 ERROR Script block number: 20; Block line 9; Message: TenantProject
2016-09-01 07:19:10.1645 7 ERROR System.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the like operation.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
ClientConnectionId:7514927b-47f7-4d14-8d41-0fb53abd5b86
Error Number:468,State:9,Class:16
2016-09-01 07:19:10.1675 7 ERROR Upgrade failed due to an unexpected exception:
System.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the like operation.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
at DbUp.Engine.UpgradeEngine.PerformUpgrade()
ClientConnectionId:7514927b-47f7-4d14-8d41-0fb53abd5b86
Error Number:468,State:9,Class:16
2016-09-01 07:19:10.1675 7 FATAL Database upgrade failed: Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the like operation.
Database upgrade logs:
Beginning database upgrade
Fetching list of already executed scripts.
Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0000 - Fix wrong schema.sql’
Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0040RemoveTemplateScriptStepPropertiesThatDoNotApplyToTheScriptSource.cs’
Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0041FixOrDeleteChannelReferencesForVariablesAndReleaseCreationOptions.cs’
Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0042 - Dashboard view potential slowness fix.sql’
Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0043 - Increase Package Column Widths.sql’
Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0043 - Index in EventRelatedDocument.sql’
Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0044 - FixMutexIdLength.sql’
Executing SQL Server script 'Octopus.Core.UpgradeScripts.Script0044 - Octopus 3.4 Schema.sql’
SQL exception has occured in script: 'Octopus.Core.UpgradeScripts.Script0044 - Octopus 3.4 Schema.sql’
Script block number: 20; Block line 9; Message: TenantProject
System.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the like operation.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
ClientConnectionId:7514927b-47f7-4d14-8d41-0fb53abd5b86
Error Number:468,State:9,Class:16
Upgrade failed due to an unexpected exception:
System.Data.SqlClient.SqlException (0x80131904): Cannot resolve the collation conflict between “Latin1_General_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the like operation.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at DbUp.Support.SqlServer.SqlScriptExecutor.<>c__DisplayClass7.b__4(Func1 dbCommandFactory) at DbUp.Support.SqlServer.SqlScriptExecutor.Execute(SqlScript script, IDictionary2 variables)
at DbUp.Engine.UpgradeEngine.PerformUpgrade()
ClientConnectionId:7514927b-47f7-4d14-8d41-0fb53abd5b86
Error Number:468,State:9,Class:16

Rolling back…

Regards,

Lee

Shane_Gill · 5 September 2016 02:11

Hi Lee,

We are going to set up a SQL RDS instance as part of our integration testing to avoid this issue in future.

In the meantime, could you please run the following query on your Octopus database so we can try to figure out why the difference in collation?

select object_name(object_id) as tablename, name as columnname,collation_name
from sys.columns
where collation_name is not null
order by object_name(object_id),column_id

Cheers,
Shane

Lee_Smith · 6 September 2016 11:57

Please see attached file with the query output.

Regards,

Lee

octo_db_issue.txt (14 KB)

Shane_Gill · 7 September 2016 04:24

Hi Lee,

Thank you for sending your results.

There are a couple of columns associated with views that have the collation SQL_Latin1_General_CP1_CI_AS and every other Octopus column has Latin1_General_CI_AS.

I think your upgrade scripts are using the collation SQL_Latin1_General_CP1_CI_AS which are causing incompatibilities with the existing tables. Collation is usually inherited from the database so the next step is to figure out what collation your Octopus database is using and correct that if it is different.

You can find out the database collation by running the following query against your Octopus database:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation');

Is should be Latin1_General_CI_AS. If it isn’t, you can set the collation by running the following:

ALTER DATABASE "Octopus"
COLLATE Latin1_General_CI_AS;

Then you should be able to update Octopus.

Cheers,
Shane

Lee_Smith · 14 September 2016 13:19

So i have got round to doing this and get the following error:

Msg 5075, Level 16, State 1, Line 4
The object ‘fnSplitReferenceCollectionAsTable’ is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5072, Level 16, State 1, Line 4
ALTER DATABASE failed. The default collation of database ‘octopus_deploy’ cannot be set to Latin1_General_CI_AS.

This is what i ran:

use master
ALTER DATABASE “octopus_deploy” SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE "octopus_deploy"
COLLATE Latin1_General_CI_AS;

ALTER DATABASE “octopus_deploy” SET MULTI_USER

Can you advise?

Regards,

Lee

Lee_Smith · 19 September 2016 09:32

Is there any update?

Regards,

Lee

Shane_Gill · 20 September 2016 00:06

Hi Lee,

I’m sorry for the delay getting back to you.

I have been trying a bunch of different methods to get you into a position where you can upgrade to 3.4 as easily as possible.

I think our best bet is to continue down the database collation route. I’ve also considered re-creating the database which may be the next step if this doesn’t work.

You should be able to remove the function that is causing the error, change the collation and then create the function again.

From SQL Server Management Studio, script the CREATE of fnSplitReferenceCollectionAsTable to a new query (screenshot attached).

Delete fnSplitReferenceCollectionAsTable.

Run the commands to change the database collation:

ALTER DATABASE "octopus_deploy" SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE "octopus_deploy" 
COLLATE Latin1_General_CI_AS;

ALTER DATABASE "octopus_deploy" SET MULTI_USER

Run the script to create fnSplitReferenceCollectionAsTable.

Hopefully that will allow you to upgrade to 3.4. If not I think we will need to re-create the database which can be a bit complicated and messy.

Cheers,
Shane

Lee_Smith · 20 September 2016 10:21

Shane,

Thats worked this time, thanks for the help, moved to the latest version.

Regards,

Lee