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: