August 15, 2016 at 5:32 pm
Can someone please tell me why it returns error for this block? I would expect the BEGIN would not occur if the conditions are not met instead it return error of "Invalid column name 'NotEligible'"
IF (EXISTS (
SELECT *
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(N'[dbo].CS')
AND c.[Name] = 'NotEligible'
)
AND EXISTS (
SELECT *
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(N'[dbo].CS')
AND c.[Name] = 'DerivedNotEligible'
))
BEGIN
UPDATE dbo.CS
SET dbo.CS.DerivedNotEligible = dbo.CS.NotEligible
ALTER TABLE [dbo].[CS]
DROP CONSTRAINT [DF_CS_NotEligible]
ALTER TABLE [dbo].CS
DROP COLUMN [NotEligible]
END
GO
Thanks
LA
August 15, 2016 at 7:09 pm
Hello and welcome to SSC. What error does it throw?
August 15, 2016 at 7:13 pm
The error is : "Invalid column name 'NotEligible'"
However, I have managed to solve it by using SET PARSEONLY OFF/ON around the script to turn off the reference checking at parsing/compile time
Thanks
August 16, 2016 at 7:09 am
festiva_96 (8/15/2016)
The error is : "Invalid column name 'NotEligible'"However, I have managed to solve it by using SET PARSEONLY OFF/ON around the script to turn off the reference checking at parsing/compile time
Thanks
This is a two way street. You should post what you did to resolve this so that others might benefit in the future from your hard work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 16, 2016 at 8:27 pm
As I have mentioned above my solution is to wrap the script around SET PARSEONLY OFF/ON. And here is the implementation:
SET PARSEONLY OFF -- turn off the syntax and name reference check
IF (EXISTS (
SELECT *
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(N'[dbo].CS')
AND c.[Name] = 'NotEligible'
)
AND EXISTS (
SELECT *
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(N'[dbo].CS')
AND c.[Name] = 'DerivedNotEligible'
))
BEGIN
UPDATE dbo.CS
SET dbo.CS.DerivedNotEligible = dbo.CS.NotEligible
ALTER TABLE [dbo].[CS]
DROP CONSTRAINT [DF_CS_NotEligible]
ALTER TABLE [dbo].CS
DROP COLUMN [NotEligible]
END
GO
SET PARSEONLY ON -- turn on the syntax and name reference check
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply