September 24, 2009 at 11:39 am
I have a proc that does a whole lot of things. SO i am not gonna go into its detail. I will defanetly post the proc here.
Everytime I exec the proc, only two things happens, either i get the successfull run message or the following error message. -"Msg 266, Level 16, State 2, Procedure P_POS_DISSEMINATE_PRODUCT_DATA_1, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1."
No matter what it happens, after executing it, when i try to close the query window, A warning window from MSQL SERVER Management Studio with the follwoing message - "There are uncommitted transactions. DO you wish to commit these transactions before closing the window.", pops up.
Can anyone help me to find the error in the proc. I think its a logical error than a TSQL code error.
any help??
I took some of the code which bascially was to create temp tbls
September 24, 2009 at 12:00 pm
I think we'd like to help you, but you can't post a hundred lines of SQL and expect a lot.
Strip down the code to the begins and commits for the transactions. Then look for places where branching might prevent some of those from being committed.
I'm stripping out your code because it's too long. If you want to post the whole thing, please us an attachment. It's causing issues with browsers as is.
September 24, 2009 at 12:16 pm
SOrry Steve..I thought posting everything will help more clearly to look @ the code, bUT i think I was wrong.
here is the codeSET NOCOUNT ON
DECLARE @ERRORNUMBER INT
DECLARE @ERRORSEVERITY INT
DECLARE @ERRORSTATE INT
DECLARE @ERRORPROCEDURE NVARCHAR(126)
DECLARE @ERRORMESSAGE NVARCHAR(2048)
DECLARE @ERRORLINE INT
DECLARE @READYSTATUSID TINYINT
BEGIN TRY
BEGIN TRANSACTION
--I DO HERE BUNCH OF THINGS
--***************************
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'MERGE', @ErrorProcedure, 'ERROR LOADING PRODUCT DATA 1', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
BEGIN TRY
BEGIN TRANSACTION
--I DO HERE BUNCH OF THINGS
--***************************
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'MERGE II', @ErrorProcedure, 'ERROR LOADING LOCATIONPRODUCT DATA 1', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
BEGIN TRY
BEGIN TRANSACTION
--I DO HERE BUNCH OF THINGS
--***************************
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'MERGE', @ErrorProcedure, 'ERROR LOADING PRODROLES DATA 1', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
BEGIN TRY
BEGIN TRANSACTION
--I DO HERE BUNCH OF THINGS
--***************************
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'MERGE', @ErrorProcedure, 'ERROR LOADING PRODPRICING DATA 1', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
BEGIN TRY
BEGIN TRANSACTION
--I DO HERE BUNCH OF THINGS
--***************************
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'MERGE', @ErrorProcedure, 'ERROR LOADING PRODLOOKUPKEYS DATA 1', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
BEGIN TRY
BEGIN TRANSACTION
--I DO HERE BUNCH OF THINGS
--***************************
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'MERGE', @ErrorProcedure, 'ERROR LOADING LOCATIONPRODPRICING DATA 1', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
BEGIN TRY
BEGIN TRANSACTION
--I DO HERE BUNCH OF THINGS
--***************************
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'MERGE', @ErrorProcedure, 'ERROR LOADING LOCATIONPRODROLES DATA 1', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
September 25, 2009 at 3:13 am
The code u have added doesn't seem to have logical problems ,
u have to do these for more investigation on this scenario
Add Full code as an attachment or atleast the sp P_POS_ERRORMESSAGE
If not make sure u have implicit transaction is not on on u r Sqlwb .
September 25, 2009 at 8:44 am
I don't see a logical reason in there either. Have you tried grabbing @@TRANCOUNT in between all the try..catches to see if it's actually being increased and you have an open tran?
September 25, 2009 at 9:02 am
@vmavoodu - Please see above, my first post has error message.
Since Steve, suggested me to put only part of the code I took everything that was within begin and end tran.
Its a long proc and putting everything gave some sorts of issue while trying to open the forum.
Steve, I did added a few steps in the proc, for one of the proc it seems to work but rest 4 doesn't.
I am working on it @ the moment.
But in brief what i did was I first check @@trancount for any open tranactions and then commit if it is greater than 0 else i start a new tranacstions and capture the @@trancount into a variable and then based on that variable value i do commit or rollabck.
I will post everything in a while
thanks guys
September 25, 2009 at 9:57 am
Per BOL, COMMIT Transaction does the following:
Marks the end of a successful implicit or explicit transaction. If @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the transaction, and decrements @@TRANCOUNT to 0. If @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1 and the transaction stays active.
So - like Steve is implying, if you have 2 BEGIN TRAN, and only 1 COMMIT TRAN, then your original transaction is still open. That's why it may be necessary to use trace PRINT statements to tell out how many open transactions you have at any point in your procedure.
If you were doing this during debugging, it may simply be that you had a transaction open form one run which had an execution error of some kind. Or, you may have another BEGIN TRAN somewhere in the code you've just taken out.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 25, 2009 at 10:19 am
here is the whole codeAS
SET IMPLICIT_TRANSACTIONS ON
SET NOCOUNT ON
DECLARE @ERRORNUMBER INT
DECLARE @ERRORSEVERITY INT
DECLARE @ERRORSTATE INT
DECLARE @ERRORPROCEDURE NVARCHAR(126)
DECLARE @ERRORMESSAGE NVARCHAR(2048)
DECLARE @ERRORLINE INT
DECLARE @READYSTATUSID TINYINT
BEGIN TRY
--GET THE READY STATUS ID
SELECT @READYSTATUSID = STATUSID
FROM [POSIMPORT].[DBO].MAINTSTATUS (NOLOCK)
WHERE [CATEGORY] = 'POSIMPORT'
AND [STATUS] = 'A'
AND = 'R'
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'SELECT', @ErrorProcedure, 'MAINTSTATUS', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
BEGIN TRY
CREATE TABLE #DS_PRODLOOKUPKEYS2
(
[ID] [int] IDENTITY(1, 1)
NOT NULL
,[LookupKey] [nvarchar](30) NOT NULL
,[StoreId] [int] NOT NULL
,[CompanyId] [int] NOT NULL
,[ProductId] [int] NOT NULL
,[Mode] [nvarchar](1) NOT NULL
,[ApplyDate] [smalldatetime] NULL
,[ProcessSeq] [tinyint] NOT NULL
)
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'CREATE TABLE', @ErrorProcedure, '#DS_PRODLOOKUPKEYS2', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
--**************************************************************************************
--**************************************************************************************
--****** PRODUCTS SEQ 1
--**************************************************************************************
DECLARE @TranCounter INT
SELECT @TranCounter = @@TRANCOUNT;
SELECT @TranCounter as InitialTran1
IF @TranCounter > 0
WHILE @TranCounter > 0
BEGIN
COMMIT TRAN
PRINT 'Initial Commit1'
SELECT @TranCounter = @TranCounter - 1
END
ELSE
BEGIN
BEGIN TRY
BEGIN TRANSACTION
--PRINT 'PRODUCTS SEQ 5'
DELETE B
FROM [POSIMPORT].[DBO].[TMP_DS_PRODUCT] A
JOIN [POSIMPORT].[DBO].[TMP_DS_PRODUCT] B ON A.[PRODUCTID] = B.[PRODUCTID]
WHERE A.[PRODUCTID] IN ( SELECT [PRODUCTID]
FROM [POSIMPORT].[DBO].[TMP_DS_PRODUCT]
WHERE (
(
[MODE] IN ( 'S', 'N' )
AND [APPLYDATE] 1 )
AND (
(
A.[MODE] IN ( 'S', 'N' )
AND A.[APPLYDATE] < GETDATE()
)
OR ( A.[MODE] IN ( 'U' ) )
)
AND A.[STOREID] B.[STOREID]
AND B.[STOREID] = 0
AND A.[PROCESSSEQ] = 5
AND B.[PROCESSSEQ] = 5
MERGE [SALLYMASTERPOS].[DBO].[PRODUCT] AS TARGET USING ( SELECT DISTINCT
[PRODUCTID]
,[SKU]
,[COMPANYID]
,[DESCRIPTION]
,[UNITSIZE]
,0 AS [POLLTYPE]
,1 AS [STATUSID]
,GETDATE() AS [ENTRYDATE]
,GETDATE() AS [UPDATEDATE]
FROM [POSIMPORT].[DBO].[TMP_DS_PRODUCT]
WHERE [MODE] = 'U'
AND [PROCESSSEQ] = 5
AND COMPANYID = 32 ) AS SOURCE ( [PRODUCTID], [SKU], [COMPANYID], [DESCRIPTION], [UNITSIZE], [POLLTYPE], [STATUSID], [ENTRYDATE], [UPDATEDATE] )
ON ( TARGET.[PRODUCTID] = SOURCE.[PRODUCTID] ) WHEN MATCHED
AND (
ISNULL(LTRIM(RTRIM(TARGET.[DESCRIPTION])), '') ISNULL(LTRIM(RTRIM(SOURCE.[DESCRIPTION])), '')
OR ISNULL(LTRIM(RTRIM(TARGET.[UNITSIZE])), '') ISNULL(LTRIM(RTRIM(SOURCE.[UNITSIZE])), '')
) THEN UPDATE
SET TARGET.[DESCRIPTION] = SOURCE.[DESCRIPTION]
,TARGET.[UNITSIZE] = SOURCE.[UNITSIZE]
,TARGET.[STATUSID] = SOURCE.[STATUSID]
,TARGET.[UPDATEDATE] = SOURCE.[UPDATEDATE] WHEN NOT MATCHED BY TARGET THEN
INSERT ( [PRODUCTID], [SKU], [COMPANYID], [DESCRIPTION], [UNITSIZE], [POLLTYPE], [STATUSID], [ENTRYDATE] )
VALUES
(
SOURCE.[PRODUCTID]
,SOURCE.[SKU]
,SOURCE.[COMPANYID]
,SOURCE.[DESCRIPTION]
,SOURCE.[UNITSIZE]
,SOURCE.[POLLTYPE]
,SOURCE.[STATUSID]
,SOURCE.[ENTRYDATE]
) ;
COMMIT TRANSACTION
PRINT '1st COMMIT'
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
PRINT '1st ROLLBACK'
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'MERGE', @ErrorProcedure, 'ERROR LOADING PRODUCT DATA 5', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
END
--**************************************************************************************
--****** LOCATION PRODUCTS SEQ 1
--**************************************************************************************
SELECT @TranCounter = @@TRANCOUNT;
SELECT @TranCounter as InitialTran2
IF @TranCounter > 0
WHILE @TranCounter > 0
BEGIN
COMMIT TRAN
PRINT 'Initial Commit2'
SELECT @TranCounter = @TranCounter - 1
END
ELSE
BEGIN
BEGIN TRY
BEGIN TRANSACTION
--PRINT 'LOCATION PRODUCTS SEQ 5'
MERGE [SALLYMASTERPOS].[DBO].[PRODUCT] AS TARGET USING ( SELECT DISTINCT
[PRODUCTID]
,[SKU]
,[COMPANYID]
,[DESCRIPTION]
,[UNITSIZE]
,0 AS [POLLTYPE]
,1 AS [STATUSID]
,GETDATE() AS [ENTRYDATE]
,GETDATE() AS [UPDATEDATE]
FROM [POSIMPORT].[DBO].[TMP_DS_LOCATIONPRODUCT]
WHERE [MODE] = 'D'
AND [PROCESSSEQ] = 5 ) AS SOURCE ( [PRODUCTID], [SKU], [COMPANYID], [DESCRIPTION], [UNITSIZE], [POLLTYPE], [STATUSID], [ENTRYDATE], [UPDATEDATE] )
ON ( TARGET.[PRODUCTID] = SOURCE.[PRODUCTID] ) WHEN MATCHED THEN DELETE ;
COMMIT TRANSACTION
PRINT '2nd Commit'
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
PRINT '2nd Rollback'
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'MERGE II', @ErrorProcedure, 'ERROR LOADING LOCATIONPRODUCT DATA 5', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
END
--**************************************************************************************
--****** PRODUCT ROLES SEQ 1
--**************************************************************************************
SELECT @TranCounter = @@TRANCOUNT;
SELECT @TranCounter as InitialTran3
IF @TranCounter > 0
WHILE @TranCounter > 0
BEGIN
COMMIT TRAN
PRINT 'Initial Commit3'
SELECT @TranCounter = @TranCounter - 1
END
ELSE
BEGIN
BEGIN TRY
BEGIN TRANSACTION
--PRINT 'PRODUCT ROLES SEQ 5'
DELETE A
FROM [POSIMPORT].[DBO].[TMP_DS_PRODROLES] A
JOIN [POSIMPORT].[DBO].[TMP_DS_PRODROLES] C ON A.[PRODUCTID] = C.[PRODUCTID]
AND A.[ROLETYPE] = C.[ROLETYPE]
WHERE (
(
A.[MODE] IN ( 'S', 'N' )
AND A.[APPLYDATE] < GETDATE()
)
OR ( A.[MODE] IN ( 'U' ) )
)
AND A.[STOREID] = 0
AND A.[STOREID] C.[STOREID]
AND (
(
C.[MODE] IN ( 'S', 'N' )
AND C.[APPLYDATE] < GETDATE()
)
OR ( C.[MODE] IN ( 'U' ) )
)
AND A.[PROCESSSEQ] = 5
AND C.[PROCESSSEQ] = 5
INSERT INTO #ProdRoles
SELECT DISTINCT
A.[PRODUCTID]
,A.[ROLETYPE]
,A.[ROLEID]
,A.[MODE]
FROM [POSIMPORT].[DBO].[TMP_DS_PRODROLES] A
JOIN ( SELECT DISTINCT
[PRODUCTID]
,[ROLETYPE]
,MAX(ROLEID) AS ROLEID
FROM [POSIMPORT].[DBO].[TMP_DS_PRODROLES]
WHERE [PROCESSSEQ] = 5
GROUP BY [PRODUCTID]
,[ROLETYPE] ) E ON A.[PRODUCTID] = E.[PRODUCTID]
AND A.[ROLETYPE] = E.[ROLETYPE]
AND A.[ROLEID] = E.[ROLEID]
JOIN [SALLYMASTERPOS].[DBO].[PRODUCT] B ( NOLOCK ) ON A.[PRODUCTID] = B.[PRODUCTID]
JOIN [SALLYMASTERPOS].[DBO].[PRODROLETYPES] C ( NOLOCK ) ON A.[ROLETYPE] = C.[ROLETYPE]
JOIN [SALLYMASTERPOS].[DBO].[PRODROLEDEFINITIONS] D ( NOLOCK ) ON A.[ROLEID] = D.[ROLEID]
WHERE (
(
[MODE] IN ( 'S', 'N' )
AND [APPLYDATE] 1)
COMMIT TRANSACTION
PRINT '3rd Commit'
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
PRINT '3rd ROllback'
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'MERGE', @ErrorProcedure, 'ERROR LOADING PRODROLES DATA 5', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
BEGIN TRY
TRUNCATE TABLE #ProdRoles
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'TRUNCATE', @ErrorProcedure, '#ProdRoles', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
END
--**************************************************************************************
--****** PRODUCT PRICING SEQ 1
--**************************************************************************************
SELECT @TranCounter = @@TRANCOUNT;
SELECT @TranCounter as InitialTran4
IF @TranCounter > 0
WHILE @TranCounter > 0
BEGIN
COMMIT TRAN
PRINT 'Initial Commit4'
SELECT @TranCounter = @TranCounter - 1
END
ELSE
BEGIN
BEGIN TRY
BEGIN TRANSACTION
--PRINT 'PRODUCT PRICING SEQ 5'
MERGE [SALLYMASTERPOS].[DBO].[PRODPRICING] AS TARGET USING ( SELECT DISTINCT
[PRODUCTID]
,[PRICEZONEID]
,[NETPRICE]
,[LISTPRICE]
,[LASTNET]
,[LASTLIST]
,0 AS [POLLTYPE]
,1 AS [STATUSID]
,GETDATE() AS [ENTRYDATE]
,GETDATE() AS [UPDATEDATE]
FROM #ProdPricing
WHERE MODE = 'N' ) AS SOURCE ( [PRODUCTID], [PRICEZONEID], [NETPRICE], [LISTPRICE], [LASTNET], [LASTLIST], [POLLTYPE], [STATUSID], [ENTRYDATE], [UPDATEDATE] )
ON (
TARGET.[PRODUCTID] = SOURCE.[PRODUCTID]
AND TARGET.[PRICEZONEID] = SOURCE.[PRICEZONEID]
) WHEN MATCHED AND (
ISNULL(TARGET.[NETPRICE], '') ISNULL(SOURCE.[NETPRICE], '')
OR ISNULL(TARGET.[LISTPRICE], '') ISNULL(SOURCE.[LISTPRICE], '')
) THEN UPDATE
SET TARGET.[NETPRICE] = SOURCE.[NETPRICE]
,TARGET.[LISTPRICE] = SOURCE.[LISTPRICE]
,TARGET.[LASTNET] = SOURCE.[LASTNET]
,TARGET.[LASTLIST] = SOURCE.[LASTLIST]
,TARGET.[STATUSID] = SOURCE.[STATUSID]
,TARGET.[UPDATEDATE] = SOURCE.[UPDATEDATE] WHEN NOT MATCHED BY TARGET THEN
INSERT ( [PRODUCTID], [PRICEZONEID], [NETPRICE], [LISTPRICE], [LASTNET], [LASTLIST], [POLLTYPE], [STATUSID], [ENTRYDATE] )
VALUES
(
SOURCE.[PRODUCTID]
,SOURCE.[PRICEZONEID]
,SOURCE.[NETPRICE]
,SOURCE.[LISTPRICE]
,SOURCE.[LASTNET]
,SOURCE.[LASTLIST]
,SOURCE.[POLLTYPE]
,SOURCE.[STATUSID]
,SOURCE.[ENTRYDATE]
) ;
COMMIT TRANSACTION
PRINT '4th Commit'
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
PRINT '4th Rollback'
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'MERGE', @ErrorProcedure, 'ERROR LOADING PRODPRICING DATA 5', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
END
BEGIN TRY
TRUNCATE TABLE #ProdPricing
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'TRUNCATE', @ErrorProcedure, '#ProdPricing', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
--**************************************************************************************
--****** PRODUCT LOOKUPKEYS SEQ 1
--**************************************************************************************
SELECT @TranCounter = @@TRANCOUNT;
SELECT @TranCounter as InitialTran5
IF @TranCounter > 0
WHILE @TranCounter > 0
BEGIN
COMMIT TRAN
PRINT 'Initial Commit5'
SELECT @TranCounter = @TranCounter - 1
END
ELSE
BEGIN
BEGIN TRY
BEGIN TRANSACTION
--PRINT 'PRODUCT LOOKUPKEYS SEQ 5'
INSERT INTO #DS_PRODLOOKUPKEYS
SELECT [LookupKey]
,[StoreId]
,[CompanyId]
,[ProductId]
,[Mode]
,[ApplyDate]
,[ProcessSeq]
FROM [POSIMPORT].[DBO].[TMP_DS_PRODLOOKUPKEYS]
WHERE (
(
[MODE] IN ( 'S', 'N' )
AND [APPLYDATE] 1
COMMIT TRANSACTION
PRINT '5th Commit'
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
PRINT '5th ROllback'
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'MERGE', @ErrorProcedure, 'ERROR LOADING PRODLOOKUPKEYS DATA 5', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
BEGIN TRY
TRUNCATE TABLE #DS_PRODLOOKUPKEYS
TRUNCATE TABLE #DS_PRODLOOKUPKEYS2
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'TRUNCATE', @ErrorProcedure, '#DS_PRODLOOKUPKEYS', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
END
--**************************************************************************************
--****** LOCATION PRODUCT PRICING SEQ 1
--**************************************************************************************
SELECT @TranCounter = @@TRANCOUNT;
SELECT @TranCounter as InitialTran6
IF @TranCounter > 0
WHILE @TranCounter > 0
BEGIN
COMMIT TRAN
PRINT 'Initial Commit6'
SELECT @TranCounter = @TranCounter - 1
END
ELSE
BEGIN
BEGIN TRY
BEGIN TRANSACTION
--PRINT 'LOCATION PRODUCT PRICING SEQ 5'
DELETE A
FROM [POSIMPORT].[DBO].[TMP_DS_LOCATIONPRODPRICING] A
JOIN [POSIMPORT].[DBO].[TMP_DS_LOCATIONPRODPRICING] C ON A.[PRODUCTID] = C.[PRODUCTID]
AND A.[PRICEZONEID] = C.[PRICEZONEID]
WHERE A.[STOREID] = 0
AND A.[STOREID] C.[STOREID]
AND (
(
A.[MODE] IN ( 'S', 'N' )
AND A.[APPLYDATE] < GETDATE()
)
OR ( A.[MODE] IN ( 'U' ) )
)
AND (
(
C.[MODE] IN ( 'S', 'N' )
AND C.[APPLYDATE] 0
ROLLBACK TRANSACTION
PRINT '6th Rollback'
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'MERGE', @ErrorProcedure, 'ERROR LOADING LOCATIONPRODPRICING DATA 5', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
END
BEGIN TRY
TRUNCATE TABLE #ProdPricing
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'TRUNCATE', @ErrorProcedure, '#ProdPricing', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
--**************************************************************************************
--****** LOCATION PRODUCT ROLES SEQ 1
--**************************************************************************************
SELECT @TranCounter = @@TRANCOUNT;
SELECT @TranCounter as InitialTran7
IF @TranCounter > 0
WHILE @TranCounter > 0
BEGIN
COMMIT TRAN
PRINT 'Initial Commit7'
SELECT @TranCounter = @TranCounter - 1
END
ELSE
BEGIN
BEGIN TRY
BEGIN TRANSACTION
--PRINT 'LOCATION PRODUCT ROLES SEQ 5'
MERGE [SALLYMASTERPOS].[DBO].[PRODROLES] AS TARGET USING ( SELECT DISTINCT
[PRODUCTID]
,[ROLETYPE]
,[ROLEID]
,0 AS [POLLTYPE]
,1 AS [STATUSID]
,GETDATE() AS [ENTRYDATE]
,GETDATE() AS [UPDATEDATE]
FROM #ProdRoles
WHERE MODE = 'U' ) AS SOURCE ( [PRODUCTID], [ROLETYPE], [ROLEID], [POLLTYPE], [STATUSID], [ENTRYDATE], [UPDATEDATE] )
ON (
TARGET.[PRODUCTID] = SOURCE.[PRODUCTID]
AND TARGET.[ROLETYPE] = SOURCE.[ROLETYPE]
) WHEN MATCHED
AND ( ISNULL(TARGET.[ROLEID], 0) ISNULL(SOURCE.[ROLEID], 0) ) THEN UPDATE
SET TARGET.[ROLEID] = SOURCE.[ROLEID]
,TARGET.[STATUSID] = SOURCE.[STATUSID]
,TARGET.[UPDATEDATE] = SOURCE.[UPDATEDATE] WHEN NOT MATCHED BY TARGET THEN
INSERT ( [PRODUCTID], [ROLETYPE], [ROLEID], [POLLTYPE], [STATUSID], [ENTRYDATE] )
VALUES
(
SOURCE.[PRODUCTID]
,SOURCE.[ROLETYPE]
,SOURCE.[ROLEID]
,SOURCE.[POLLTYPE]
,SOURCE.[STATUSID]
,SOURCE.[ENTRYDATE]
) ;
MERGE [SALLYMASTERPOS].[DBO].[PRODROLES] AS TARGET USING ( SELECT DISTINCT
[PRODUCTID]
,[ROLETYPE]
FROM #ProdRoles
WHERE MODE = 'D' ) AS SOURCE ( [PRODUCTID], [ROLETYPE] )
ON (
TARGET.[PRODUCTID] = SOURCE.[PRODUCTID]
AND TARGET.[ROLETYPE] = SOURCE.[ROLETYPE]
) WHEN MATCHED THEN DELETE ;
COMMIT TRANSACTION
PRINT '7th COmmit'
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
PRINT '7th ROllback'
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'MERGE', @ErrorProcedure, 'ERROR LOADING LOCATIONPRODROLES DATA 5', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
END
BEGIN TRY
TRUNCATE TABLE #ProdRoles
END TRY
BEGIN CATCH
SELECT @ErrorNumber = ERROR_NUMBER()
,@ErrorSeverity = ERROR_SEVERITY()
,@ErrorState = ERROR_STATE()
,@ErrorProcedure = ERROR_PROCEDURE()
,@ErrorMessage = ERROR_MESSAGE()
,@ErrorLine = ERROR_LINE() ;
EXEC [POSIMPORT].[DBO].[P_POS_ERRORMESSAGE] 'TRYCATCH', 'TRUNCATE', @ErrorProcedure, '#ProdRoles', @ErrorMessage, @ErrorLine, @ErrorNumber, @ErrorSeverity, @ErrorState
END CATCH
DROP TABLE #ProdRoles
DROP TABLE #ProdPricing
DROP TABLE #DS_PRODLOOKUPKEYS
DROP TABLE #DS_PRODLOOKUPKEYS2
SELECT @TranCounter = @@TRANCOUNT;
SELECT @TranCounter as InitialTranEnd
IF @TranCounter > 0
WHILE @TranCounter > 0
BEGIN
COMMIT TRAN
PRINT 'Initial CommitEnd'
SELECT @TranCounter = @TranCounter - 1
END
SET IMPLICIT_TRANSACTIONS OFF
GO
September 25, 2009 at 11:04 am
Guys Thanks to all....Looks like this piece of code seems to work....
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply