January 10, 2017 at 3:03 pm
I have the following script that runs when I deploy my database project from Visual Studio 2015:
-- Use @_dbVersion to check DB version:
DECLARE @_dbVersion int
select @_dbVersion = [Version] from [dbo].[DBVersion]
IF @_dbVersion = 1
BEGIN
...
-- Update DB Version:
UPDATE [dbo].[DBVersion]
SET [Version] = 2
SET @_dbVersion = 2
END
IF @_dbVersion = 2
BEGIN
...
-- Update DB Version:
UPDATE [dbo].[DBVersion]
SET [Version] = 3
SET @_dbVersion = 3
END
IF @_dbVersion = 3
BEGIN
...
-- Update DB Version:
UPDATE [dbo].[DBVersion]
SET [Version] = 4
SET @_dbVersion = 4
END
IF @_dbVersion = 4
BEGIN
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='CauseToRiskDataDetails')
BEGIN
...
PRINT('Copying tolerable frequencies from RiskData to BowtieLoop.')
UPDATE [dbo].[BowtieLoop]
SET TolerableFrequency = RD.TolerableFrequency
FROM [dbo].[RiskData] RD
JOIN [dbo].[BowtieLoop] BL ON RD.BowtieLoopId = BL.BowtieLoopId
PRINT('Dropping CauseToRiskDataDetails')
DROP TABLE [dbo].[CauseToRiskDataDetails]
...
END
-- Update DB Version:
UPDATE [dbo].[DBVersion]
SET [Version] = 5
SET @_dbVersion = 5
END
This script essentially performs incremental updates to the database based on a database version. It starts by getting the version from the DBVersion table and assigning it to the variable @_dbVersion. This way, whatever version the database is on, it will skip all incremental changes before that version and perform all incremental changes after that version, incrementing the version for every change.
For versions 1 to 4, I have skipped the script for convenience, leaving the general structure of checking the versions for clarity. It's what happens when the version is 4 that I'm having problems with.
We have recently removed the column TolerableFrequency from our RiskData table. But you can see that the script for version 4 makes reference to TolerableFrequency. This is causing an error when I try to deploy the database from Visual Studio 2015. It tells me that TolerableFrequency is an invalid reference.
This makes sense as we no longer have TolerableFrequency but I'd like to keep the script as the whole purpose of going through these incremental changes is for cases in which we are dealing with older versions of the database, and some of these versions may still have TolerableFrequency on RiskData.
I tried putting a check for TolerableFrequency before running the script above like so:
IF EXISTS(SELECT * FROM sys.columns WHERE Name = N'TolerableFrequency' AND Object_ID = Object_ID(N'RiskData'))
BEGIN
PRINT('Copying tolerable frequencies from RiskData to BowtieLoop.')
UPDATE [dbo].[BowtieLoop]
SET TolerableFrequency = RD.TolerableFrequency
FROM [dbo].[RiskData] RD
JOIN [dbo].[BowtieLoop] BL ON RD.BowtieLoopId = BL.BowtieLoopId
END
But this doesn't seem to help. It seems as though the error is a compile error as there is no way the above code could be run (even without the check for TolerableFrequency on RiskData, it shouldn't run).
Is there a way to tell the SQL compiler only to compile this code if TolerableFrequency exists on RiskData?
January 11, 2017 at 2:38 am
you need to change that bit of code to be dynamic sql - that way you can check for the existence of the column and only execute it if available
January 11, 2017 at 2:50 am
Put the part that has the column that may not exist in dynamic SQL
EXEC ('<sql statement here>')
That error is indeed a parse-time error, the entire batch gets parsed before anything starts executing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 11, 2017 at 12:45 pm
That worked perfectly. Thanks a lot!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply