When we are using to upgrade the database we might encounter the following issue:
| Expand | ||
|---|---|---|
| ||
| Code Block | ||
7/14/2020 12:26:42 PM [INFO] Starting Run upgrade script \5.2.4.7to5.2.4.8.sql...
7/14/2020 12:26:42 PM [INFO] Command output:
Changed database context to 'AccessPortal'.
Start run schema changes of Sfb
Msg 515, Level 16, State 2, Server SQLIIS, Line 19
Cannot insert the value NULL into column 'EwsAgatToken', table 'AccessPortal.dbo.SCA_CELL_LOGIN'; column does not allow nulls. UPDATE fails. |
This error is a result of having rows in the table SCA_CELL_LOGIN where EwsAgatToken or CommonCookieNonce is equal to NULL.
How can I fix it?
In order to ‘fix’ it, we need to edit the .SQL script to not throw an exception when it finds NULL values in one of those columns.
Open the script, the default location is:
C:\Agat\SkypeShield.Setup\Payload\SqlScripts\Upgrade\5.2.4.7to5.2.4.8.sql
Edit the following lines:
| Code Block | ||
|---|---|---|
| ||
IF LOWER(@Product) = N'sfb'
BEGIN
-- only SfB schema changes will be here
print(N'Start run schema changes of Sfb')
ALTER TABLE [SCA_CELL_LOGIN] ALTER COLUMN [EwsAgatToken] nvarchar(60) NOT NULL
ALTER TABLE [SCA_CELL_LOGIN] ALTER COLUMN [CommonCookieNonce] nvarchar(345) NOT NULL
CREATE NONCLUSTERED INDEX IX_SCA_CELL_LOGIN_EwsAgatToken ON dbo.SCA_CELL_LOGIN
(EwsAgatToken) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END |
to look like so:
| Code Block | ||
|---|---|---|
| ||
IF LOWER(@Product) = N'sfb'
BEGIN
-- only SfB schema changes will be here
print(N'Start run schema changes of Sfb')
ALTER TABLE [SCA_CELL_LOGIN] ALTER COLUMN [EwsAgatToken] nvarchar(60) NULL
ALTER TABLE [SCA_CELL_LOGIN] ALTER COLUMN [CommonCookieNonce] nvarchar(345) NULL
CREATE NONCLUSTERED INDEX IX_SCA_CELL_LOGIN_EwsAgatToken ON dbo.SCA_CELL_LOGIN
(EwsAgatToken) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END |
| Info |
|---|
The important thing here is to remove the NOT keyword from lines 7, 8 where the script modifies the columns. |