Moving existing 2sxc installations from Azure SQL 11 to Azure SQL 12

4/18/2016

If you had installed 2sxc on Azure using Azure SQL 11 and now want to Migrate to SQL 12 you'll run into a strange issue. You'll see a message like

System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'ContextInfo', table 'dnna1ct2b.dbo.ToSIC_EAV_ContextInfo'; column does not allow nulls. INSERT fails. The statement has been terminated.

Here's why, and how to fix it.

The core Issue: Azure SQL 11 Limitations

Azure SQL is technically fairly different than a normal SQL Server, and it had many limitations which are now gone. One of these limitations was a ContextInfo-object, which we needed for various SQL operations in the data-timeline.

In Azure SQL 12 this limitation was fixed. This is a good thing, but now the SQL had code saying "On Azure, do it differently" which now caused trouble.

Fixing this on New Installations

2sxc 8.4 and higher have additional version checking so it only applies the fix on older SQL Azures. This should take care of everything if you are installing a NEW system.

Fixing this by First Upgrading to 2sxc 8.4

If you can upgrade to 2sxc 8.4 before changing SQLs, you should be all set, as this modifies the relevant code to check for the different versions.

Fixing this on Already Migrated Installations

The fix is just a short SQL. The problem is that we cannot include it in the normal upgrade-path, because we don't know which version you are upgrading from, and this code would have to be the very first which would have to run. So if

if you had installed 2sxc before version 8.4
...on a normal SQL Server on premise OR on Azure SQL 11...
...and if you have migrated / upgraded to Azure SQL 12
...and are now experiencing the bug

then you should run the following code in the Host > SQL section, which fixes the relevant stored procedure:

Update Stored Procedure to Support Azure SQL 12

/****** Object:  StoredProcedure [dbo].[ToSIC_EAV_ChangeLogSet]    Script Date: 26.10.2015 09:35:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ToSIC_EAV_ChangeLogSet]
  @ChangeID int
AS

SET NOCOUNT ON

-- Remove all context items older than an 5 minutes ago
DELETE FROM [dbo].[ToSIC_EAV_ContextInfo] WHERE [UpdatedAt] < DATEADD(mi, -5, GETUTCDATE())

IF SERVERPROPERTY('edition') <> 'SQL Azure' OR CAST(SERVERPROPERTY('ProductVersion') AS CHAR(2)) >= '12'
BEGIN
    DECLARE @b varbinary(128)
    SET @b = CONVERT(varbinary(128),newid())
    EXEC sp_executesql @statement=N'SET CONTEXT_INFO @b',@params=N'@b varbinary(128)',@b=@b
	print @b
END

DECLARE @ContextInfo varbinary(128)
SELECT @ContextInfo = CONTEXT_INFO()

IF EXISTS (SELECT * FROM [dbo].[ToSIC_EAV_ContextInfo] WHERE [ContextInfo] = @ContextInfo)
	UPDATE [dbo].[ToSIC_EAV_ContextInfo]
	SET
		[ChangeID] = @ChangeID,
		[UpdatedAt] = GETUTCDATE()
	WHERE
		ContextInfo = @ContextInfo
ELSE
	INSERT INTO [dbo].[ToSIC_EAV_ContextInfo] ([ContextInfo], [ChangeID], [UpdatedAt]) VALUES (@ContextInfo, @ChangeID, GETUTCDATE());

That's it folks :)

Happy coding, and please leave your feedback...

Love from Switzerland,
the iJungleboy

Sql azure 2sxc v8 DNN / DotNetNuke

Daniel Mettler grew up in the jungles of Indonesia and is founder and CEO of 2sic internet solutions in Switzerland and Liechtenstein, an 20-head web specialist with over 800 DNN projects since 1999. He is also chief architect of 2sxc (see github), an open source module for creating attractive content and DNN Apps.