Board index » delphi » MS SQL Server - Columns Exists problem. Condition not met but SQL statment still fails

MS SQL Server - Columns Exists problem. Condition not met but SQL statment still fails


2007-09-05 03:40:41 PM
delphi206
Hi all,
we have a "DO NO HARM" script that gets run our DB whenever an update to our
software is applied.
The part shown below is fine when the condition is met and it dies the
updates and drops the column.
If it is ran again, the conditions are NOT met so none of the updates should
occur - Hence "DO NO HARM"
Howeever, it seems that something behind the scenes checks the statments
anyway.
For instance, if I run the script below a second time, i get the error
"Msg 207, Level 16, State 1, Line 17 Invalid column name 'bGA'."
How can I get round this? and execute the statement without error.
if EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SupplierFacilityConfiguration' AND COLUMN_NAME = 'bGA' )
BEGIN
if EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SupplierFacilitySection' AND COLUMN_NAME =
'GA' )
BEGIN
Blah Blah..........
UPDATE SupplierFacilitySection
SET GA = 1,
ScreenX = 100, ScreenY = 100,
ScreenWidth = 100, ScreenHeight = 100
WHERE
SupplierFacilitySection.ConfigurationId in
( Select ConfigurationId from SupplierFacilityConfiguration
WHERE bGA = 1 )
ALTER TABLE SupplierFacilityConfiguration
DROP COLUMN bGA
END
END
 
 

Re:MS SQL Server - Columns Exists problem. Condition not met but SQL statment still fails

AHA
I have found Exec(''), which is a bit dirty
or
be a bit creative using a join
Update Blah WHere ConfigurationId = (
SELECT DISTINCT ConfigurationId FROM SupplierFacilityConfiguration
INNER JOIN
INFORMATION_SCHEMA.COLUMNS COLS ON
COLS.TABLE_NAME = 'SupplierFacilityConfiguration' AND COLS.COLUMN_NAME =
'bGA')
"DC" <XXXX@XXXXX.COM>writes
Quote
Hi all,
we have a "DO NO HARM" script that gets run our DB whenever an update to
our software is applied.

The part shown below is fine when the condition is met and it dies the
updates and drops the column.
If it is ran again, the conditions are NOT met so none of the updates
should occur - Hence "DO NO HARM"

Howeever, it seems that something behind the scenes checks the statments
anyway.

For instance, if I run the script below a second time, i get the error
"Msg 207, Level 16, State 1, Line 17 Invalid column name 'bGA'."

How can I get round this? and execute the statement without error.

if EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SupplierFacilityConfiguration' AND COLUMN_NAME =
'bGA' )
BEGIN
if EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'SupplierFacilitySection' AND COLUMN_NAME =
'GA' )
BEGIN

Blah Blah..........

UPDATE SupplierFacilitySection
SET GA = 1,
ScreenX = 100, ScreenY = 100,
ScreenWidth = 100, ScreenHeight = 100
WHERE
SupplierFacilitySection.ConfigurationId in
( Select ConfigurationId from SupplierFacilityConfiguration
WHERE bGA = 1 )

ALTER TABLE SupplierFacilityConfiguration
DROP COLUMN bGA

END
END

 

Re:MS SQL Server - Columns Exists problem. Condition not met but SQL statment still fails

Quote
Howeever, it seems that something behind the scenes checks the statments
anyway.
Put a GO statement in after you've modified the table.
Oliver Townshend
 

Re:MS SQL Server - Columns Exists problem. Condition not met but SQL statment still fails

Oliver Townshend presented the following explanation :
Quote
>Howeever, it seems that something behind the scenes checks the statments
>anyway.

Put a GO statement in after you've modified the table.

Oliver Townshend
Well it depends how this statement is executed GO has a meaning only
inside the Query analyzer. IF it included on a SQL statement executed
from an TADOConnection or a TADOQuery or TADOCommand object will raise
and invalid command on the statement.
Regards
Yannis.
 

Re:MS SQL Server - Columns Exists problem. Condition not met but SQL statment still fails

Probably
where ConfigurationId in (...), not ConfigurationId = (...)
to handle case when there is>1 ConfigurationId returned.
//------------------------------------------
Regards,
Vassiliev V. V.
www.managed-vcl.com - using .Net objects in Delphi for Win32 +
ADO.Net
www.oledbdirect.com - The fastest way to access MS SQL Server,
MS Jet (Access) and Interbase (through OLEDB)
"DC" <XXXX@XXXXX.COM>сообщи?сообщила ?новостя?следующе?
Quote
AHA

I have found Exec(''), which is a bit dirty

or

be a bit creative using a join
Update Blah WHere ConfigurationId = (

SELECT DISTINCT ConfigurationId FROM SupplierFacilityConfiguration

INNER JOIN

INFORMATION_SCHEMA.COLUMNS COLS ON

COLS.TABLE_NAME = 'SupplierFacilityConfiguration' AND COLS.COLUMN_NAME =
'bGA')





"DC" <XXXX@XXXXX.COM>writes
news:XXXX@XXXXX.COM...
>Hi all,
>we have a "DO NO HARM" script that gets run our DB whenever an update to
>our software is applied.
>
>The part shown below is fine when the condition is met and it dies the
>updates and drops the column.
>If it is ran again, the conditions are NOT met so none of the updates
>should occur - Hence "DO NO HARM"
>
>Howeever, it seems that something behind the scenes checks the statments
>anyway.
>
>For instance, if I run the script below a second time, i get the error
>"Msg 207, Level 16, State 1, Line 17 Invalid column name 'bGA'."
>
>How can I get round this? and execute the statement without error.
>
>if EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
>WHERE TABLE_NAME = 'SupplierFacilityConfiguration' AND COLUMN_NAME =
>'bGA' )
>BEGIN
>if EXISTS( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
>WHERE TABLE_NAME = 'SupplierFacilitySection' AND COLUMN_NAME =
>'GA' )
>BEGIN
>
>Blah Blah..........
>
>UPDATE SupplierFacilitySection
>SET GA = 1,
>ScreenX = 100, ScreenY = 100,
>ScreenWidth = 100, ScreenHeight = 100
>WHERE
>SupplierFacilitySection.ConfigurationId in
>( Select ConfigurationId from SupplierFacilityConfiguration
>WHERE bGA = 1 )
>
>ALTER TABLE SupplierFacilityConfiguration
>DROP COLUMN bGA
>
>END
>END
>