INSERT Fails - 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 2000 Forums

Transact-SQL (2000)

INSERT Fails

Author

Topic

bsav
Starting Member

7 Posts

Posted - 2004-08-26 : 09:57:51

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

BSav

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-08-26 : 11:09:15

You've got a ROLLBACK TRAN in the error handler for the INSERT, however your comment above states no TRAN necessary and you have no BEGIN TRAN statement.
The @@ERROR <> 0 trap is after the PRINT statement, rather than after the INSERT, so @@ERROR will be 0 (unless there's an error in the PRINT statement).
Why is everything in EXEC() statements ??

bsav
Starting Member

7 Posts

Posted - 2004-08-26 : 11:29:39

Yes, you're right. I've been tweaking this for hours trying to figure it out. Tried it in the tran above. Tried it in it's own tran. Always the same, no new row. Even did a select right after the insert in the script and the row is there. The "execs" were the only way I could the new columns added in a way that the statements below would see them. At any rate, I've changed it as you suggest. Still behaves the same. Here it is now:

EXEC("
PRINT 'Creating SystemSetting CipherConversion'
INSERT INTO SystemSettings (OptionName, OptionValue) VALUES('CipherConversion', 'Yes')
IF @@Error <> 0
BEGIN
PRINT 'Failed!'
RETURN
END
PRINT 'Success!'
")

BSav

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources