troubleshooting Question

SQLServer2000: Procedure Hangs part way through Execution...

Avatar of dlhalsey
dlhalsey asked on
Microsoft SQL Server
7 Comments1 Solution209 ViewsLast Modified:
I have SQL Server 2000 with SP2 installed on an Intel-based single processor SCSI machine running Windows 2000 Server in Standalone mode; the machine has 1GB RAM, 160GB drive capacity, low usage; all client machines have the latest version of MDAC 2.6 installed to conform with SQLServer2000/SP2 native MDAC requirements.

I have a Stored Procedure that gets called from a Visual Basic 6 App via an ADO Execute command; the stored procedure populates a table with summed values that a Crystal Reports 8 document retrieves after the stored procedure ends and control is returned to the client machine (synchronous connection).  This stored procedure completes execution twice correctly; on the third attempt it hangs at the following UPDATE command, never returning control to the SP to continue its execution (the EXACT SAME criterion are provided the SP on each execution, and table data are unchanged):

BEGIN TRAN
     UPDATE SISReports..SIS_MonthPerformance
          SET     CurrentCasesPTD = ISNULL((SELECT SUM(Cases) FROM SISReports..SIS_Reporting
                         WHERE YEAR(SISReports..SIS_Reporting.[InvoiceDate])=@Year
                         AND MONTH(SISReports..SIS_Reporting.[InvoiceDate])=@Month
                         AND SISReports..SIS_Reporting.CompanyCode=SISReports..SIS_MonthPerformance.CompanyCode
                         AND SISReports..SIS_Reporting.ManagerNum=SISReports..SIS_MonthPerformance.SalesManager
                         AND SISReports..SIS_Reporting.BrokerNum=SISReports..SIS_MonthPerformance.Broker
                         AND SISReports..SIS_Reporting.CustomerNum=SISReports..SIS_MonthPerformance.Customer
                         AND SISReports..SIS_Reporting.ShipTo=SISReports..SIS_MonthPerformance.ShipTo
                         AND SISReports..SIS_Reporting.ItemNum=SISReports..SIS_MonthPerformance.Item),0),
               CurrentDollarsPTD = ISNULL((SELECT SUM(Dollars) FROM SISReports..SIS_Reporting
                         WHERE YEAR(SISReports..SIS_Reporting.[InvoiceDate])=@Year
                         AND MONTH(SISReports..SIS_Reporting.[InvoiceDate])=@Month
                         AND SISReports..SIS_Reporting.CompanyCode=SISReports..SIS_MonthPerformance.CompanyCode
                         AND SISReports..SIS_Reporting.ManagerNum=SISReports..SIS_MonthPerformance.SalesManager
                         AND SISReports..SIS_Reporting.BrokerNum=SISReports..SIS_MonthPerformance.Broker
                         AND SISReports..SIS_Reporting.CustomerNum=SISReports..SIS_MonthPerformance.Customer
                         AND SISReports..SIS_Reporting.ShipTo=SISReports..SIS_MonthPerformance.ShipTo
                         AND SISReports..SIS_Reporting.ItemNum=SISReports..SIS_MonthPerformance.Item),0)
     WHERE UserName=@UserName

COMMIT TRAN

No error is ever reported.

Profiler shows
  'SQL: StmtStarting' for "BEGIN TRAN",
  'SQL: StmtCompleted' for "BEGIN TRAN",
  'SQL: StmtStarting' for "UPDATE SISReports..",

then a few thousand:
  'Lock: Acquired',
  'Lock: Released'
events (normal so far compared with the two previous successful executions),

then two successive:
  'Scan: Stopped'

events.

Missing are:
  'SQL: StmtCompleted' for "UPDATE SISReports.."
  'SQL: StmtStarted' for "COMMIT TRAN"
  'SQL: StmtCompleted' for "COMMIT TRAN"

events; no further execution of the stored procedure takes place according to Profiler.  During all this on the Server side, the Client VB application remains locked and no reporting about it or its SPID# shows up under Profiler UNTIL the client application is manually terminated with Task Manager on the users machine.

After the client application has been Terminated it can be restarted immediately, and the above procedure will again complete normally twice; again it will lock on the third go as before.  
 
There are no other applications, Stored Procedures, Triggers, etc... working with the tables involved.

Any ideas?

Thanks,

Dave.
ASKER CERTIFIED SOLUTION
modulo

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros