December 20, 2011 at 9:19 am
This seems to be a weird behaviour OR rather I am missing here something..
We have various SQL patch scripts to upgrade our DB's to a newer version. In one of the patches, we transferred the contents of a column into a new column and dropped the column once the task was completed.
However, now the patch script maybe executed more than once in the same DB and due to the nature of SQL which tries to resolve column references during compile ( Since table exists and column does not when it is run the second time) it throws an error even though the text is wrapped in IF EXISTS statement.
Is the server behaving erratically or I am missing something; The same script run on a different server executes the whole patch script flawless; and on one server gives error saying Invalid column name "StoreInfo"
--STEP:1
IF EXISTS ( SELECT *
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME = 'Stores'
AND COLUMN_NAME = 'StoreInfo'
)
BEGIN
UPDATE Stores
SET StoreInformation = CASE WHEN StoreInfo = SOmedata THEN Somedata2 END
WHERE StoreID = Something
END
GO
--STEP 2:
StoreInfo Column Dropped
I got suggestions saying wrap the text in Dynamic SQL; But i am sure there must be some other solution for this behavior.
December 20, 2011 at 9:56 am
You probably have two tables named "stores" under different schemata. Adding a condition for Table_Schema should fix the problem.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 20, 2011 at 10:12 am
I am pretty sure that we have only one Stores table; Also adding the Table_Schema did not help. The SQL analyzer is trying to look up for the table structure of Stores and does not find the column and throws an error during "Bind" phase for Query execution.
I need to make sure that the patch script could be run, 'n' number of times on any given DB. Did anyone come across this issue ?
December 20, 2011 at 11:49 am
Have you verified that though? Did you do a select without the column_name to see what was returned?
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME = 'Stores'
December 20, 2011 at 11:50 am
ulteriorm (12/20/2011)
I am pretty sure that we have only one Stores table;
I'm pretty sure that you do and the fact that you are only "pretty sure" and not positive indicates that you didn't bother to run the query to find out.
Also adding the Table_Schema did not help.
Adding the Table_Schema where? If you add it to the UPDATE statement, then it won't help. You need to add it to the WHERE clause of the EXISTS statement.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 20, 2011 at 12:00 pm
There is actually another possibility, but it's highly unlikely.
Your table is set up with case sensitive column names and you have another column in that same table that differs only in case. This would involve someone overriding the default settings for case at some level when setting up the server or database or table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 20, 2011 at 12:11 pm
bwoulfe (12/20/2011)
Have you verified that though? Did you do a select without the column_name to see what was returned?SELECT *
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME = 'Stores'
@bwolfe : yes the Select statement returns me NO Record.
I suspect the bind phase of query execution is trying to resolve the stores table data, and since it could not find the Info column, it threw an error.
@ Thanks Drew for being sarcastic and questioning where the Table_Schema needs to be added. I have added it in the IF EXISTS statement and just to make matters simpler for you..I am "positive" that there is only one stores table.
December 20, 2011 at 12:29 pm
ulteriorm (12/20/2011)
bwoulfe (12/20/2011)
Have you verified that though? Did you do a select without the column_name to see what was returned?SELECT *
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE TABLE_NAME = 'Stores'
@bwolfe : yes the Select statement returns me NO Record.
Well, that seems like a problem. Are you running it under the correct database context? Are you doing this in SSMS? I would verify the database you're running this against.
December 20, 2011 at 12:53 pm
ulteriorm (12/20/2011)
I suspect the bind phase of query execution is trying to resolve the stores table data, and since it could not find the Info column, it threw an error.
This is why someone suggested dynamic SQL. It delays the validation until after the EXISTS clause has been evaluated. I'm not sure why it works on the other server, though.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply