|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Long running stored procedureapprox 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 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. __________________________________ Show quote "Locus" <Lo***@discussions.microsoft.com> wrote in message news:93EBD110-E1A2-4E6D-BF43-51D198202D8E@microsoft.com... >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 > > > 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. -- Show quoteGregory A. Beamer ************************************************* Think Outside the Box! ************************************************* "Locus" <Lo***@discussions.microsoft.com> wrote in message news:93EBD110-E1A2-4E6D-BF43-51D198202D8E@microsoft.com... >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 > > >  |
|||||||||||||||||||||||
Other interesting topics