Help with error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with error

Author

Topic

rapplewhite
Starting Member

2 Posts

Posted - 2007-04-25 : 12:05:48

I am having trouble with the following tsql job that I wrote. Basically, it runs ok the first time I run it on a database, but any subsequent runs give me an error on the UPDATE part because my field no longer exists in the first table. If the FinanceChargeID field does not exist in the Entity table, I want this to just terminate and print the "ELSE" message at the bottom of the query.

Is there a way to fix this query so that it can be run multiple times, and update if it needs to otherwise just print the message that no update is needed??

SET @ERROR = 0
BEGIN TRAN
PRINT ''
PRINT ''
PRINT 'Moving FinanceChargeID data from Entity to EntityBalanceTotal'

if exists(select * from CTCPeople.dbo.SysColumns

where name = 'FinanceChargeID' and ID in (select ID from

CTCPeople.dbo.sysobjects where (id = object_id(N'[dbo].[Entity]')

and OBJECTPROPERTY(id, N'IsUserTable') = 1)))

AND

exists(select * from syscolumns where name = 'FinanceChargeID'

and ID in (select ID from dbo.sysobjects where

(id = object_id(N'[dbo].[EntityBalanceTotal]')

and OBJECTPROPERTY(id, N'IsUserTable') = 1)))

BEGIN

UPDATE CTCReceivables.dbo.EntityBalanceTotal

SET CTCReceivables.dbo.EntityBalanceTotal.FinanceChargeID = e.FinanceChargeID

From ctcpeople.dbo.Entity e

INNER JOIN CTCReceivables.dbo.EntityBalanceTotal ebt

ON e.ID = ebt.EntityID

If @@ERROR <> 0
SET @ERROR = 1
If @ERROR = 0

BEGIN

PRINT 'Dropping FinanceChargeID column from Entity table'

PRINT ''

if exists(select * from CTCPeople.dbo.SysColumns

where name = 'FinanceChargeID' and ID in (select ID from

CTCPeople.dbo.sysobjects where (id = object_id(N'[dbo].[Entity]')

and OBJECTPROPERTY(id, N'IsUserTable') = 1)))

ALTER TABLE ctcPeople.dbo.Entity

DROP COLUMN [FinanceChargeID]
If @@ERROR <> 0

BEGIN

SET @ERROR = 1

Print 'Failed to drop FinanceChargeID column from Entity table'

END

END

IF @ERROR = 1

BEGIN

PRINT 'Errors occurred. Rolling back transactions'

ROLLBACK TRAN

END

ELSE

BEGIN

PRINT 'No errors encountered. Committing db changes'

COMMIT TRAN

END

END

ELSE

BEGIN

PRINT ''

PRINT 'No data to move. FinanceChargeID column missing from Entity and/or EntityBalanceTotal'

END

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-25 : 12:13:38

Add following IF Exists() condition before Update:

If exists(select * from information_schema.columns where column_name = 'FinanceChargeID' and table_name = 'Entity')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources