Post reply

COMMIT ALTER TABLE command before UPDATE

  • October 15, 2003 at 4:03 am

    #107215

    Hello I have a script that first update the table structure whereafter it add some values to the newly created colums.

    But I can not get it working. Please help?

    if exists (select * from dbo.sysobjects where id = object_id(N'[BackupUserTables]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    BEGIN-- table exist

    IF NOT exists (Select * from dbo.syscolumns where name = 'DDelBkUpsTime' AND id in (select ID from dbo.sysobjects where id = object_id(N'[BackupUserTables]')and OBJECTPROPERTY(id, N'IsUserTable') = 1))

    BEGIN

    ALTER TABLE msdb..BackupUserTables

    ADD DDelBkUpsTime varchar(100) NULL

    UPDATE msdb..BackupUserTables SET DDelBkUpsTime = '2weeks' WHERE DDelBkUpsTime IS NULL

    END

    END-- table exist

    the script above result in error:

    Invalid column name DDelBkUpsTime

  • October 15, 2003 at 7:19 am

    #478284

    How about trying this one...

    if exists (select * from dbo.sysobjects where id = object_id(N'[BackupUserTables]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

    BEGIN -- table exist

    IF NOT exists (Select * from dbo.syscolumns where name = 'DDelBkUpsTime' AND id in (select ID from dbo.sysobjects where id = object_id(N'[BackupUserTables]')and OBJECTPROPERTY(id, N'IsUserTable') = 1))

    ALTER TABLE msdb..BackupUserTables

    ADD DDelBkUpsTime varchar(100) NULL

    END -- table exist

    GO

    UPDATE msdb..BackupUserTables SET DDelBkUpsTime = '2weeks' WHERE DDelBkUpsTime IS NULL

    GO

    Sachin


    Regards,
    Sachin Dedhia

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply