|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
1.9 million line transaction in profiler with no begin end using WAITFORServer is WinOS 2000 Advanced Server SQL is 2000 sp3a collation 437_CI_AS I have a developer who is using the following code (this is only a subset and all confidential data has been changed) to move members around. DBCC OPENTRAN shows 1,2,1,2,1,2... for this spid. Profiler only listed one record with 1.9 million lines of code attached to it. There are no begin..ends in there. We are trying to figure out how opentran is showing two open transactions. I would think that it would show 0,1,0,1,0,1.... I understand that the waitfor's are their own transaction. Any help on this would be greatly appreciated. set nocount on declare @company_new smallint set @company_new = 1 ---- AH_MEMBER ---- declare @AH_MEMBER__ACCOUNT__map table (ACCOUNT_old BigInt, ACCOUNT_new BigInt) declare @AH_MEMBER__ACCOUNT BigInt exec dbo.spv_GetNewAcctNum @company_new, @AH_MEMBER__ACCOUNT output select @AH_MEMBER__ACCOUNT ACCOUNT_new insert AH_MEMBER ( [COMPANY], [ACCOUNT], [PAYOR_ACCOUNT], [FIRST_NAME] , [LAST_NAME]) values ( @company_new, @AH_MEMBER__ACCOUNT, '0000000', 'DOE', 'JOHN') if @@error = 0 insert @AH_MEMBER__ACCOUNT__map values (0000000, @AH_MEMBER__ACCOUNT) WAITFOR DELAY '0:00:00.000' ---- AH_ACTIVITY_LOG ---- declare @AH_ACTIVITY_LOG__Account BigInt select @AH_ACTIVITY_LOG__Account = ACCOUNT_new from @AH_MEMBER__ACCOUNT__map where ACCOUNT_old = 0000000 if @@rowcount = 0 set @AH_ACTIVITY_LOG__Account = 0 insert AH_ACTIVITY_LOG ( [Company] , [Account], [UserID], [DateTimeIn], [DateTimeOut]) values ( @company_new, @AH_ACTIVITY_LOG__Account, 000, '01/01/2004 01:00:00.000', '01/01/2004 01:00:00.001') WAITFOR DELAY '0:00:00.000' ---- AH_MEMBER_OPEN ---- declare @AH_MEMBER_OPEN__hs_refnbr__map table (hs_refnbr_old BigInt, hs_refnbr_new BigInt) declare @AH_MEMBER_OPEN__account BigInt declare @AH_MEMBER_OPEN__inscode_id Int select @AH_MEMBER_OPEN__account = ACCOUNT_new from @AH_MEMBER__ACCOUNT__map where ACCOUNT_old = 0000000 if @@rowcount = 0 set @AH_MEMBER_OPEN__account = 0 select @AH_MEMBER_OPEN__inscode_id = inscode_id_new from #inscode_id_map where inscode_id_old = 000 if @@rowcount = 0 set @AH_MEMBER_OPEN__inscode_id = 000 insert AH_MEMBER_OPEN ( [company] , [account], [inscode_id]) values ( @company_new, @AH_MEMBER_OPEN__account, @AH_MEMBER_OPEN__inscode_id) if @@error = 0 insert @AH_MEMBER_OPEN__hs_refnbr__map values (000000, scope_identity()) WAITFOR DELAY '0:00:00.000' set nocount off Hi
With 1.9 million lines of code it could probably do with refactoring! If this is a stored procedure have you looked at using the debugger in QA to run this, then you can have a watch on @@TRANCOUNT. At a guess you have a trigger that has incremented the TRANCOUNT. John <cheeta***@gmail.com> wrote in message Show quote news:1157474511.922220.30130@i3g2000cwc.googlegroups.com... > Need some help... > > Server is WinOS 2000 Advanced Server > SQL is 2000 sp3a collation 437_CI_AS > > I have a developer who is using the following code (this is only a > subset and all confidential data has been changed) to move members > around. DBCC OPENTRAN shows 1,2,1,2,1,2... for this spid. Profiler > only listed one record with 1.9 million lines of code attached to it. > There are no begin..ends in there. We are trying to figure out how > opentran is showing two open transactions. I would think that it would > show 0,1,0,1,0,1.... I understand that the waitfor's are their own > transaction. > > Any help on this would be greatly appreciated. > > set nocount on > > declare @company_new smallint > set @company_new = 1 > > ---- AH_MEMBER ---- > declare @AH_MEMBER__ACCOUNT__map table (ACCOUNT_old BigInt, ACCOUNT_new > BigInt) > > declare @AH_MEMBER__ACCOUNT BigInt > > exec dbo.spv_GetNewAcctNum @company_new, > @AH_MEMBER__ACCOUNT output > > select @AH_MEMBER__ACCOUNT ACCOUNT_new > > insert AH_MEMBER ( > [COMPANY], [ACCOUNT], [PAYOR_ACCOUNT], [FIRST_NAME] , [LAST_NAME]) > values ( > @company_new, @AH_MEMBER__ACCOUNT, '0000000', 'DOE', 'JOHN') > > if @@error = 0 insert @AH_MEMBER__ACCOUNT__map values (0000000, > @AH_MEMBER__ACCOUNT) > > WAITFOR DELAY '0:00:00.000' > > ---- AH_ACTIVITY_LOG ---- > declare @AH_ACTIVITY_LOG__Account BigInt > > select @AH_ACTIVITY_LOG__Account = ACCOUNT_new from > @AH_MEMBER__ACCOUNT__map where ACCOUNT_old = 0000000 > if @@rowcount = 0 set @AH_ACTIVITY_LOG__Account = 0 > > insert AH_ACTIVITY_LOG ( > [Company] , [Account], [UserID], [DateTimeIn], [DateTimeOut]) values ( > @company_new, @AH_ACTIVITY_LOG__Account, 000, '01/01/2004 > 01:00:00.000', '01/01/2004 01:00:00.001') > > WAITFOR DELAY '0:00:00.000' > > ---- AH_MEMBER_OPEN ---- > declare @AH_MEMBER_OPEN__hs_refnbr__map table (hs_refnbr_old BigInt, > hs_refnbr_new BigInt) > > declare @AH_MEMBER_OPEN__account BigInt > declare @AH_MEMBER_OPEN__inscode_id Int > > select @AH_MEMBER_OPEN__account = ACCOUNT_new from > @AH_MEMBER__ACCOUNT__map where ACCOUNT_old = 0000000 > if @@rowcount = 0 set @AH_MEMBER_OPEN__account = 0 > > select @AH_MEMBER_OPEN__inscode_id = inscode_id_new from > #inscode_id_map where inscode_id_old = 000 > if @@rowcount = 0 set @AH_MEMBER_OPEN__inscode_id = 000 > > insert AH_MEMBER_OPEN ( > [company] , [account], [inscode_id]) values ( > @company_new, @AH_MEMBER_OPEN__account, @AH_MEMBER_OPEN__inscode_id) > > if @@error = 0 insert @AH_MEMBER_OPEN__hs_refnbr__map values (000000, > scope_identity()) > > WAITFOR DELAY '0:00:00.000' > > set nocount off > This is not a stored procedure but generated code from an application.
We have no triggers in our databases. Thanks, though. John Bell wrote: Show quote > Hi > > With 1.9 million lines of code it could probably do with refactoring! If > this is a stored procedure have you looked at using the debugger in QA to > run this, then you can have a watch on @@TRANCOUNT. > > At a guess you have a trigger that has incremented the TRANCOUNT. > > John > > <cheeta***@gmail.com> wrote in message > news:1157474511.922220.30130@i3g2000cwc.googlegroups.com... > > Need some help... > > > > Server is WinOS 2000 Advanced Server > > SQL is 2000 sp3a collation 437_CI_AS > > > > I have a developer who is using the following code (this is only a > > subset and all confidential data has been changed) to move members > > around. DBCC OPENTRAN shows 1,2,1,2,1,2... for this spid. Profiler > > only listed one record with 1.9 million lines of code attached to it. > > There are no begin..ends in there. We are trying to figure out how > > opentran is showing two open transactions. I would think that it would > > show 0,1,0,1,0,1.... I understand that the waitfor's are their own > > transaction. > > > > Any help on this would be greatly appreciated. > > > > set nocount on > > > > declare @company_new smallint > > set @company_new = 1 > > > > ---- AH_MEMBER ---- > > declare @AH_MEMBER__ACCOUNT__map table (ACCOUNT_old BigInt, ACCOUNT_new > > BigInt) > > > > declare @AH_MEMBER__ACCOUNT BigInt > > > > exec dbo.spv_GetNewAcctNum @company_new, > > @AH_MEMBER__ACCOUNT output > > > > select @AH_MEMBER__ACCOUNT ACCOUNT_new > > > > insert AH_MEMBER ( > > [COMPANY], [ACCOUNT], [PAYOR_ACCOUNT], [FIRST_NAME] , [LAST_NAME]) > > values ( > > @company_new, @AH_MEMBER__ACCOUNT, '0000000', 'DOE', 'JOHN') > > > > if @@error = 0 insert @AH_MEMBER__ACCOUNT__map values (0000000, > > @AH_MEMBER__ACCOUNT) > > > > WAITFOR DELAY '0:00:00.000' > > > > ---- AH_ACTIVITY_LOG ---- > > declare @AH_ACTIVITY_LOG__Account BigInt > > > > select @AH_ACTIVITY_LOG__Account = ACCOUNT_new from > > @AH_MEMBER__ACCOUNT__map where ACCOUNT_old = 0000000 > > if @@rowcount = 0 set @AH_ACTIVITY_LOG__Account = 0 > > > > insert AH_ACTIVITY_LOG ( > > [Company] , [Account], [UserID], [DateTimeIn], [DateTimeOut]) values ( > > @company_new, @AH_ACTIVITY_LOG__Account, 000, '01/01/2004 > > 01:00:00.000', '01/01/2004 01:00:00.001') > > > > WAITFOR DELAY '0:00:00.000' > > > > ---- AH_MEMBER_OPEN ---- > > declare @AH_MEMBER_OPEN__hs_refnbr__map table (hs_refnbr_old BigInt, > > hs_refnbr_new BigInt) > > > > declare @AH_MEMBER_OPEN__account BigInt > > declare @AH_MEMBER_OPEN__inscode_id Int > > > > select @AH_MEMBER_OPEN__account = ACCOUNT_new from > > @AH_MEMBER__ACCOUNT__map where ACCOUNT_old = 0000000 > > if @@rowcount = 0 set @AH_MEMBER_OPEN__account = 0 > > > > select @AH_MEMBER_OPEN__inscode_id = inscode_id_new from > > #inscode_id_map where inscode_id_old = 000 > > if @@rowcount = 0 set @AH_MEMBER_OPEN__inscode_id = 000 > > > > insert AH_MEMBER_OPEN ( > > [company] , [account], [inscode_id]) values ( > > @company_new, @AH_MEMBER_OPEN__account, @AH_MEMBER_OPEN__inscode_id) > > > > if @@error = 0 insert @AH_MEMBER_OPEN__hs_refnbr__map values (000000, > > scope_identity()) > > > > WAITFOR DELAY '0:00:00.000' > > > > set nocount off > > Hi
You could always post the statements into QA and add some SELECT @@TRANCOUNTs after each statement. It seems an awful lot to be doing as a single batch, it seems that you are effectively using a cursor to process items one at a time rather than using a set based solution. You may also want to look at using identity values to generate your manufactured keys. John <cheeta***@gmail.com> wrote in message Show quote news:1157479465.409903.132130@h48g2000cwc.googlegroups.com... > This is not a stored procedure but generated code from an application. > We have no triggers in our databases. > > Thanks, though. > > > John Bell wrote: >> Hi >> >> With 1.9 million lines of code it could probably do with refactoring! If >> this is a stored procedure have you looked at using the debugger in QA to >> run this, then you can have a watch on @@TRANCOUNT. >> >> At a guess you have a trigger that has incremented the TRANCOUNT. >> >> John >> >> <cheeta***@gmail.com> wrote in message >> news:1157474511.922220.30130@i3g2000cwc.googlegroups.com... >> > Need some help... >> > >> > Server is WinOS 2000 Advanced Server >> > SQL is 2000 sp3a collation 437_CI_AS >> > >> > I have a developer who is using the following code (this is only a >> > subset and all confidential data has been changed) to move members >> > around. DBCC OPENTRAN shows 1,2,1,2,1,2... for this spid. Profiler >> > only listed one record with 1.9 million lines of code attached to it. >> > There are no begin..ends in there. We are trying to figure out how >> > opentran is showing two open transactions. I would think that it would >> > show 0,1,0,1,0,1.... I understand that the waitfor's are their own >> > transaction. >> > >> > Any help on this would be greatly appreciated. >> > >> > set nocount on >> > >> > declare @company_new smallint >> > set @company_new = 1 >> > >> > ---- AH_MEMBER ---- >> > declare @AH_MEMBER__ACCOUNT__map table (ACCOUNT_old BigInt, ACCOUNT_new >> > BigInt) >> > >> > declare @AH_MEMBER__ACCOUNT BigInt >> > >> > exec dbo.spv_GetNewAcctNum @company_new, >> > @AH_MEMBER__ACCOUNT output >> > >> > select @AH_MEMBER__ACCOUNT ACCOUNT_new >> > >> > insert AH_MEMBER ( >> > [COMPANY], [ACCOUNT], [PAYOR_ACCOUNT], [FIRST_NAME] , [LAST_NAME]) >> > values ( >> > @company_new, @AH_MEMBER__ACCOUNT, '0000000', 'DOE', 'JOHN') >> > >> > if @@error = 0 insert @AH_MEMBER__ACCOUNT__map values (0000000, >> > @AH_MEMBER__ACCOUNT) >> > >> > WAITFOR DELAY '0:00:00.000' >> > >> > ---- AH_ACTIVITY_LOG ---- >> > declare @AH_ACTIVITY_LOG__Account BigInt >> > >> > select @AH_ACTIVITY_LOG__Account = ACCOUNT_new from >> > @AH_MEMBER__ACCOUNT__map where ACCOUNT_old = 0000000 >> > if @@rowcount = 0 set @AH_ACTIVITY_LOG__Account = 0 >> > >> > insert AH_ACTIVITY_LOG ( >> > [Company] , [Account], [UserID], [DateTimeIn], [DateTimeOut]) values ( >> > @company_new, @AH_ACTIVITY_LOG__Account, 000, '01/01/2004 >> > 01:00:00.000', '01/01/2004 01:00:00.001') >> > >> > WAITFOR DELAY '0:00:00.000' >> > >> > ---- AH_MEMBER_OPEN ---- >> > declare @AH_MEMBER_OPEN__hs_refnbr__map table (hs_refnbr_old BigInt, >> > hs_refnbr_new BigInt) >> > >> > declare @AH_MEMBER_OPEN__account BigInt >> > declare @AH_MEMBER_OPEN__inscode_id Int >> > >> > select @AH_MEMBER_OPEN__account = ACCOUNT_new from >> > @AH_MEMBER__ACCOUNT__map where ACCOUNT_old = 0000000 >> > if @@rowcount = 0 set @AH_MEMBER_OPEN__account = 0 >> > >> > select @AH_MEMBER_OPEN__inscode_id = inscode_id_new from >> > #inscode_id_map where inscode_id_old = 000 >> > if @@rowcount = 0 set @AH_MEMBER_OPEN__inscode_id = 000 >> > >> > insert AH_MEMBER_OPEN ( >> > [company] , [account], [inscode_id]) values ( >> > @company_new, @AH_MEMBER_OPEN__account, @AH_MEMBER_OPEN__inscode_id) >> > >> > if @@error = 0 insert @AH_MEMBER_OPEN__hs_refnbr__map values (000000, >> > scope_identity()) >> > >> > WAITFOR DELAY '0:00:00.000' >> > >> > set nocount off >> > > Unfortunately, we are in an environment where the DBAs are not part of
the architecture piece. We just get to fix the problems after the fact, as in this case. I agree that it is not written in the best way. I will try the QA thing and see what I get. Thanks John Bell wrote: Show quote > Hi > > You could always post the statements into QA and add some SELECT > @@TRANCOUNTs after each statement. It seems an awful lot to be doing as a > single batch, it seems that you are effectively using a cursor to process > items one at a time rather than using a set based solution. You may also > want to look at using identity values to generate your manufactured keys. > > John > > <cheeta***@gmail.com> wrote in message > news:1157479465.409903.132130@h48g2000cwc.googlegroups.com... > > This is not a stored procedure but generated code from an application. > > We have no triggers in our databases. > > > > Thanks, though. > > > > > > John Bell wrote: > >> Hi > >> > >> With 1.9 million lines of code it could probably do with refactoring! If > >> this is a stored procedure have you looked at using the debugger in QA to > >> run this, then you can have a watch on @@TRANCOUNT. > >> > >> At a guess you have a trigger that has incremented the TRANCOUNT. > >> > >> John > >> > >> <cheeta***@gmail.com> wrote in message > >> news:1157474511.922220.30130@i3g2000cwc.googlegroups.com... > >> > Need some help... > >> > > >> > Server is WinOS 2000 Advanced Server > >> > SQL is 2000 sp3a collation 437_CI_AS > >> > > >> > I have a developer who is using the following code (this is only a > >> > subset and all confidential data has been changed) to move members > >> > around. DBCC OPENTRAN shows 1,2,1,2,1,2... for this spid. Profiler > >> > only listed one record with 1.9 million lines of code attached to it. > >> > There are no begin..ends in there. We are trying to figure out how > >> > opentran is showing two open transactions. I would think that it would > >> > show 0,1,0,1,0,1.... I understand that the waitfor's are their own > >> > transaction. > >> > > >> > Any help on this would be greatly appreciated. > >> > > >> > set nocount on > >> > > >> > declare @company_new smallint > >> > set @company_new = 1 > >> > > >> > ---- AH_MEMBER ---- > >> > declare @AH_MEMBER__ACCOUNT__map table (ACCOUNT_old BigInt, ACCOUNT_new > >> > BigInt) > >> > > >> > declare @AH_MEMBER__ACCOUNT BigInt > >> > > >> > exec dbo.spv_GetNewAcctNum @company_new, > >> > @AH_MEMBER__ACCOUNT output > >> > > >> > select @AH_MEMBER__ACCOUNT ACCOUNT_new > >> > > >> > insert AH_MEMBER ( > >> > [COMPANY], [ACCOUNT], [PAYOR_ACCOUNT], [FIRST_NAME] , [LAST_NAME]) > >> > values ( > >> > @company_new, @AH_MEMBER__ACCOUNT, '0000000', 'DOE', 'JOHN') > >> > > >> > if @@error = 0 insert @AH_MEMBER__ACCOUNT__map values (0000000, > >> > @AH_MEMBER__ACCOUNT) > >> > > >> > WAITFOR DELAY '0:00:00.000' > >> > > >> > ---- AH_ACTIVITY_LOG ---- > >> > declare @AH_ACTIVITY_LOG__Account BigInt > >> > > >> > select @AH_ACTIVITY_LOG__Account = ACCOUNT_new from > >> > @AH_MEMBER__ACCOUNT__map where ACCOUNT_old = 0000000 > >> > if @@rowcount = 0 set @AH_ACTIVITY_LOG__Account = 0 > >> > > >> > insert AH_ACTIVITY_LOG ( > >> > [Company] , [Account], [UserID], [DateTimeIn], [DateTimeOut]) values ( > >> > @company_new, @AH_ACTIVITY_LOG__Account, 000, '01/01/2004 > >> > 01:00:00.000', '01/01/2004 01:00:00.001') > >> > > >> > WAITFOR DELAY '0:00:00.000' > >> > > >> > ---- AH_MEMBER_OPEN ---- > >> > declare @AH_MEMBER_OPEN__hs_refnbr__map table (hs_refnbr_old BigInt, > >> > hs_refnbr_new BigInt) > >> > > >> > declare @AH_MEMBER_OPEN__account BigInt > >> > declare @AH_MEMBER_OPEN__inscode_id Int > >> > > >> > select @AH_MEMBER_OPEN__account = ACCOUNT_new from > >> > @AH_MEMBER__ACCOUNT__map where ACCOUNT_old = 0000000 > >> > if @@rowcount = 0 set @AH_MEMBER_OPEN__account = 0 > >> > > >> > select @AH_MEMBER_OPEN__inscode_id = inscode_id_new from > >> > #inscode_id_map where inscode_id_old = 000 > >> > if @@rowcount = 0 set @AH_MEMBER_OPEN__inscode_id = 000 > >> > > >> > insert AH_MEMBER_OPEN ( > >> > [company] , [account], [inscode_id]) values ( > >> > @company_new, @AH_MEMBER_OPEN__account, @AH_MEMBER_OPEN__inscode_id) > >> > > >> > if @@error = 0 insert @AH_MEMBER_OPEN__hs_refnbr__map values (000000, > >> > scope_identity()) > >> > > >> > WAITFOR DELAY '0:00:00.000' > >> > > >> > set nocount off > >> > > >  |
|||||||||||||||||||||||
Other interesting topics