Long running stored procedure

[Guest]

I have a very long stored procedure "runMRP".
approx 1000 lines and can runs for 30 minutes at Query Analyser.

It runs sucessfully at query analyser with all the result.
It runs successfully on osql.

It runs fine from VB.Net program on small data set. (with less than few sec
running time)
It always crushes when I runs from VB.Net program with large data set. The
Exception catch from VB.Net side (SQLException) is "Cursor is READONLY. The
statement has been terminated.".

The code is a simple data adaptor call:
objCmd = objConn.CreateCommand
objCmd.CommandText = "runMRP"
objCmd.CommandType = CommandType.StoredProcedure
objCmd.CommandTimeout = 0
objAdp.SelectCommand = objCmd

objAdp.Fill(myDs) ' fail here

I use VS 2005, .Net 2.0 and SQL Server 2000.

1. I have set the CommandTimeout to 0 (and at the SQL server side, I set the
Query Timeout to 0), is there any other place that timeout could occur?

2. I have great difficulty to debug this problem. At VB.Net side, when I
trace to objAdp.Fill line, it will pass to SQL Server to execute the command.
I have no way to know what is going on. At the SQL Server side, I can't see
what is going on. Is there any tools that can help?

3. Any suggestion to solve this problem?

regards
Locus

 

[William \(Bill\) Vaughn]

Turn on the profiler and see how OSQL and ADO.NET are executing the SP. I
expect you'll notice a difference. Make sure you're calling the SP with the
right syntax--using the CommandType.StoredProcedure. If you do, ADO.NET
simply passes through the name of the SP with the parameters (in 2.0). If
not, it does an "exec sp_executesql" instead which might cause the problem
you're seeing.

____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Cowboy \(Gregory A. Beamer\)]

1. Turn on SQL profiler and find out how it is executing. This will give you
an idea of how the SQL is being run.

2. Try using a DataReader and write out time on every loop.

3. Watch memory usage and see if you are overloading your memory with the
size of the DataSet.

4. Do the same for CPU time.

I would also examine the stored procedure, as the cursor is read only error
could be an issue in the sproc itself. Depending on how it is run, the error
might not rear its head (reason to profile while running the sproc). Without
seeing the code and sproc, I cannot do anything but suggest generically.

--
Gregory A. Beamer

*************************************************
Think Outside the Box!
*************************************************