Begin/End and BeginTrans/Commit/Rollback

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 2008 Forums

Transact-SQL (2008)

Begin/End and BeginTrans/Commit/Rollback

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-08-18 : 23:10:06

Can someone check my code below. I do not believe my EXEC (@SQLCmd) line in my While look is running. I thought I had my If Exists/Begin/Ends all ok. I want my WHILE set of code to run regardless if that copying old store to new store already processed. Note, I am not getting errors, but my updates are not happening. Yet, when I run the update one by one in a different window, they do indeed update.Which is why I wonder if I have something set so that it skips this unknowingly.

Thanks

BEGIN TRY
BEGIN TRANSACTION

IF NOT EXISTS (SELECT * FROM #NewStoreExists)
BEGIN
--copy old store into temp
SELECT * INTO #DataMoveTemp_gblStore
FROM #OldStoreExists

--update temp store records to new store ID
SET @SQLCmd = 'UPDATE #DataMoveTemp_gblStore
SET StoreID = ' + CAST(@New_StoreID as varchar(10)) +
' WHERE StoreID = ' + CAST(@Original_StoreID as varchar(10))
EXEC (@SQLCmd)

--add new store from Temp table to gblstore table with new storeID
SET @SQLCmd = 'INSERT INTO [' + @New_Server + '].' + @New_CompanyID + '.dbo.GblStore
SELECT * FROM #DataMoveTemp_gblStore
WHERE StoreID = ' + Cast(@New_StoreID as varchar(10))
EXEC (@SQLCmd)

DROP TABLE #DataMoveTemp_gblStore
END

-- Now the work starts - Start Changing the Store ID's
SET @i = 1
WHILE @i <= ISNULL((SELECT MAX(UniqueID) FROM #UpdateCommands),0)
BEGIN

--Example UpdateCommand: SET ROWCOUNT 10000 WHILE EXISTS (SELECT TOP 1 1 FROM BreakRule WHERE FKStoreID = 99) BEGIN UPDATE BreakRule SET FKStoreID = 1223 WHERE FKStoreID = 99 END
SET @SQLCmd = (SELECT UpdateCommand FROM #UpdateCommands WHERE UniqueID = @i)
SET @TableName = (SELECT TableName FROM #UpdateCommands WHERE UniqueID = @i)
SET @FieldName = (SELECT FieldName FROM #UpdateCommands WHERE UniqueID = @i)

EXEC (@SQLCmd)

SET @i = @i + 1
END

--Delete original store ID
SET @SQLCmd = 'DELETE FROM ' + '[' + @Original_Server + '].' + @Original_CompanyID + '.dbo.GblStore
WHERE StoreId = ' + CAST(@Original_StoreID as varchar(10))
EXEC (@SQLCmd)

COMMIT TRANSACTION

PRINT 'Store Number Changed Successfully'

END TRY
BEGIN CATCH
SET @ErrorMessage = 'ERROR running SQL... ' + ERROR_MESSAGE()
RAISERROR(@ErrorMessage,16,1)

ROLLBACK TRANSACTION

PRINT 'Store Number Change Failed. Updates have been rolled back.'
END CATCH

JAdauto
Posting Yak Master

160 Posts

Posted - 2014-08-18 : 23:46:03

I should add that #UpdateCommands is populated above teh Transaction portion. I can post that if it would help. I ran a test to make sure that #updateCommands actually had records and it does indeed and the syntax in the UpdateCommand field works fine. It is just from here, it is like it gets skipped and the updateCommands never run. ???

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources