August 26, 2004 at 8:08 am
The following TSQL fragment runs fine on it's own. When embeded in it's larger parent script, everythibg still works except the final "INSERT" statement. It acts like it worked, @@RowCount reports one row added, but when the entire script completes, that row is not there. It's as if just the INSERT is being implicitly rolled back somehow. Any thoughts out there? Thanks
/*******************************************************************************************
Upgrading to 4.7.01
*******************************************************************************************/
DECLARE @CurVersion varchar(10)
SELECT @CurVersion = Version FROM #Version
PRINT 'Testing 4.7.01'
IF @CurVersion < '4.7.01'
BEGIN
PRINT 'Upgrading From ' + @CurVersion + ' to 4.7.01'
BEGIN TRAN
--* Drop the CCSwipes table. Can't store those anymore.
EXEC("
IF EXISTS (SELECT * FROM sysobjects where id = object_id('CCSwipes') and sysstat & 0xf = 3)
BEGIN
PRINT 'Dropping CCSwipes'
DROP TABLE CCSwipes
IF @@Error <> 0
BEGIN
PRINT 'Failed!'
ROLLBACK TRAN
RETURN
END
PRINT 'Success!'
END
")
--* Prepare the CCAuths table
EXEC("
PRINT 'CCAuths: Creating CCNumTmp to convert CCNum to varbinary(32)'
ALTER TABLE CCAuths ADD CCNumTmp varbinary(32) DEFAULT 0 NOT NULL
IF @@Error <> 0
BEGIN
PRINT 'Failed!'
ROLLBACK TRAN
RETURN
END
PRINT 'Success!'
")
EXEC("
PRINT 'CCAuths: Moving CCNum data to the temp column'
UPDATE CCAuths SET CCNumTmp = CONVERT(varbinary(32), CCNum)
IF @@Error <> 0
BEGIN
PRINT 'Failed!'
ROLLBACK TRAN
RETURN
END
PRINT 'Success!'
")
EXEC("
PRINT 'CCAuths: DROP the old CCNum column'
ALTER TABLE CCAuths DROP COLUMN CCNum
IF @@Error <> 0
BEGIN
PRINT 'Failed!'
ROLLBACK TRAN
RETURN
END
PRINT 'Success!'
")
EXEC("
PRINT 'CCAuths: Finally, rename the new CCNumTmp column to CCNum.'
EXEC sp_rename 'CCAuths.CCNumTmp', 'CCNum', 'COLUMN'
IF @@Error <> 0
BEGIN
PRINT 'Failed!'
ROLLBACK TRAN
RETURN
END
PRINT 'Success!'
")
--* Prepare the CCTransactions table
EXEC("
PRINT 'CCTransactions: Creating CCNumTmp to convert CCNum to varbinary(32)'
ALTER TABLE CCTransactions ADD CCNumTmp varbinary(32) DEFAULT 0 NOT NULL
IF @@Error <> 0
BEGIN
PRINT 'Failed!'
ROLLBACK TRAN
RETURN
END
PRINT 'Success!'
")
EXEC("
PRINT 'CCTransactions: Moving CCNum data to the temp column'
UPDATE CCTransactions SET CCNumTmp = CONVERT(varbinary(32), CCNum)
IF @@Error <> 0
BEGIN
PRINT 'Failed!'
ROLLBACK TRAN
RETURN
END
PRINT 'Success!'
")
EXEC("
PRINT 'CCTransactions: DROP the old CCNum column'
ALTER TABLE CCTransactions DROP COLUMN CCNum
IF @@Error <> 0
BEGIN
PRINT 'Failed!'
ROLLBACK TRAN
RETURN
END
PRINT 'Success!'
")
EXEC("
PRINT 'CCTransactions: Finally, rename the new CCNumTmp column to CCNum.'
EXEC sp_rename 'CCTransactions.CCNumTmp', 'CCNum', 'COLUMN'
IF @@Error <> 0
BEGIN
PRINT 'Failed!'
ROLLBACK TRAN
RETURN
END
PRINT 'Success!'
")
--* Prepare the FolioRouting Table
--* Prepare the GroupEvents table
--* Prepare the GroupEventsHist table
--* Prepare the GuestLinks table
--* Prepare the GuesLinksHist table
--* Prepare the Guests table
--* Prepare the GuestsHist table
--* Prepare the Stays table
--* Prepare the StaysHist table
EXEC("
PRINT 'Creating table CCVerifyCodes'
CREATE TABLE CCVerifyCodes (
VerifyCode varchar(8) NOT NULL,
Description varchar(255) NULL,
AcceptAsValid bit DEFAULT 0,
PRIMARY KEY (VerifyCode))
IF @@Error <> 0
BEGIN
PRINT 'Failed!'
ROLLBACK TRAN
RETURN
END
PRINT 'Success!'
")
COMMIT TRAN
--* Misc. Stuff. Non destructive, no TRAN necessary.
EXEC("
PRINT 'Creating SystemSetting CipherConversion'
INSERT INTO SystemSettings (OptionName, OptionValue) VALUES ('CipherConversion', 'Yes')
PRINT Convert(varchar, @@RowCount) + ' Rows Inserted into system settings'
IF @@Error <> 0
BEGIN
PRINT 'Failed!'
ROLLBACK TRAN
RETURN
END
PRINT 'Success!'
")
END
GO
.
August 26, 2004 at 8:57 pm
Move the "commit transaction".
COMMIT TRAN
--* Misc. Stuff. Non destructive, no TRAN necessary.
EXEC("
PRINT 'Creating SystemSetting CipherConversion'
INSERT INTO SystemSettings (OptionName, OptionValue) VALUES ('CipherConversion', 'Yes')
PRINT Convert(varchar, @@RowCount) + ' Rows Inserted into system settings'
IF @@Error <> 0
BEGIN
PRINT 'Failed!'
ROLLBACK TRAN
RETURN
END
PRINT 'Success!'
")
COMMIT TRAN
END
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply