Issues with script for updating structures

• JKSQL

  SSCrazy Eights

  Points: 8481

  More actions

  June 9, 2009 at 1:27 pm

  #222263

  I have a script that works so when I make changes to a DB it will update the clients out there so they can run this and there structures are up to date. Question is when there is an issue the "go" statement is making it so changes can not be rolled back. here is an example:

  IF dbo.fnGetVersion() < 3.78
  BEGIN
  SET XACT_ABORT ON
  ALTER TABLE [Transaction] ALTER COLUMN Number [UniqueIdentifier] NOT NULL
  END--@Version < 3.78
  GO
  IF dbo.fnGetVersion() < 3.80
  BEGIN
  SET XACT_ABORT ON
  IF col_length('[dbo].[ActiveGroups]','Description') IS NULL
  BEGIN
  ALTER TABLE [ActiveGroups] ADD Description [NVarchar](255) NULL
  END
  END--@Version < 3.80
  GO
  /* Now we can update the DB version */
  DECLARE @NewVersion FLOAT
  DECLARE @PreviousVersion FLOAT
  SET @NewVersion = 3.85
  SET @PreviousVersion = dbo.fnGetVersion()
  DELETE FROM DBVersion

  INSERT INTO DBVersion (Version, PreviousVersion, PreviousMitekOwnedDataVersion) VALUES (@NewVersion, @PreviousVersion, @PreviousVersion)
  [/code]

  So if it blows up in the middle I want to roll back all the changes. Is there an easy way to do this? XACT is not working.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 9, 2009 at 2:53 pm

  #1007069

  SET XACT_ABORT ON only works in combination with BEGIN TRAN and COMMIT TRAN.you set XACT_ABORT on just once...and that is how it works for the session, or until you explicitly turn it off.

  your proc doesn't start a transaction, so there's nothing to auto rollback.

  also, a transaction should never have GO statements in it, so you should have either three pairs of BEGIN TRAN/COMMIT TRAN, one for each GO section, or you should have one transaction and no "GO" statements at all

  try it like this instead:

  SET XACT_ABORT ON --set once for the session

  BEGIN TRAN --start a transaction which can be rolled back

  IF dbo.fnGetVersion() < 3.78

  BEGIN

  ALTER TABLE [Transaction] ALTER COLUMN Number [UniqueIdentifier] NOT NULL

  END--@Version < 3.78

  IF dbo.fnGetVersion() < 3.80

  BEGIN

  IF col_length('[dbo].[ActiveGroups]','Description') IS NULL

  BEGIN

  ALTER TABLE [ActiveGroups] ADD Description [NVarchar](255) NULL

  END

  END--@Version < 3.80

  /* Now we can update the DB version */

  DECLARE @NewVersion FLOAT

  DECLARE @PreviousVersion FLOAT

  SET @NewVersion = 3.85

  SET @PreviousVersion = dbo.fnGetVersion()

  DELETE FROM DBVersion

  INSERT INTO DBVersion (Version, PreviousVersion, PreviousMitekOwnedDataVersion) VALUES (@NewVersion, @PreviousVersion, @PreviousVersion)

  COMMIT TRAN --if any errors, process would stop before this point and rollback.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!