In my installer I have to make a minor change in the schema:
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[UserProfiles]') AND name = 'AllCheckboxesChecked')
BEGIN
ALTER TABLE [dbo].[UserProfiles] ADD [AllCheckboxesChecked] [bit] CONSTRAINT [DF_UserProfiles_AllCheckboxesChecked] DEFAULT 0 NOT NULL
UPDATE [dbo].[UserProfiles] SET [AllCheckboxesChecked]=1 WHERE [CheckedBoxes] LIKE '%#ALL#%'
END
GO
In SSMS this works, but not in Advanced Installer, where it fails with the error message that the column AllCheckboxesChecked does not exist. So I tried:
IF NOT EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'[dbo].[UserProfiles]') AND name = 'AllCheckboxesChecked')
BEGIN
ALTER TABLE [dbo].[UserProfiles] ADD [AllCheckboxesChecked] [bit] CONSTRAINT [DF_UserProfiles_AllCheckboxesChecked] DEFAULT 0 NOT NULL
GO
UPDATE [dbo].[UserProfiles] SET [AllCheckboxesChecked]=1 WHERE [CheckedBoxes] LIKE '%#ALL#%'
END
GO
but this throws syntax errors as well (not in SSMS, only in AdvInst), so I guess that GO
is not allowed inside the BEGIN...END block. The connection is configured as follows:
Connection type: Microsoft SQL Server / MSDE
Connection mode: ODBC Driver
ODBC Driver: SQL Server
Use 64-bit ODBC resource: No
What steps can I take to get the column created and populated with correct values iff the installer runs on a DB where the column does not yet exist?
The column doesn't exist
error is due to validation that occurs on existing objects. Since the table already exists, the parser / compiler will verify that the table contains all of the referenced columns.
In order to get around such timing issues with object verification, you can enclose the statement in an EXEC
which will not be verified until run-time:
BEGIN
ALTER TABLE [dbo].[UserProfiles]
ADD [AllCheckboxesChecked] [bit]
CONSTRAINT [DF_UserProfiles_AllCheckboxesChecked] DEFAULT 0
NOT NULL;
EXEC(N'UPDATE [dbo].[UserProfiles]
SET [AllCheckboxesChecked]=1
WHERE [CheckedBoxes] LIKE ''%#ALL#%''');
END;