SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sql compile error: reference to non-existent column


sql compile error: reference to non-existent column

Author
Message
junk.mail291276
junk.mail291276
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 78
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?
frederico_fonseca
frederico_fonseca
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1830 Visits: 1905
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)

Group: General Forum Members
Points: 229166 Visits: 46344
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


junk.mail291276
junk.mail291276
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 78
That worked perfectly. Thanks a lot!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search