|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Stored Procedure Deathlenght, as its parameter. It then parses that string to develop a set of inputs which will be input, deleted or updated in the database. The amount of time required to run is dependant on the length of the stirng passed in but can exceed one second in rare cases. When run, using Execute direct, via ODBC, this stored procedure will begin execution but then just abruptly quit. It does not take any exit path but simply quits. The ODBC connection returns SQL_SUCCESS, but the stored procedure died in process. If one is to run the exact same stored procedure, using the exact same parameter, but run from Query Analyser, it will dependably execut to end and produce the expected results. I assume that I am running into some user time limit at ODBC or Database level, but can not find it or find how to change it. We are running SQL Server 7.0, 2000, and 2003 at dufferent location and experience the exact same results on each. Can anyone help. Al I would use Profiler to track down what the sp is doing and when/where it is
stopping. "Al K" <A**@discussions.microsoft.com> wrote in message I have a stored procedure which accepts a single input string of variablenews:B2404024-2F19-4332-A10A-B55B30293EA2@microsoft.com... lenght, as its parameter. It then parses that string to develop a set of inputs which will be input, deleted or updated in the database. The amount of time required to run is dependant on the length of the stirng passed in but can exceed one second in rare cases. When run, using Execute direct, via ODBC, this stored procedure will begin execution but then just abruptly quit. It does not take any exit path but simply quits. The ODBC connection returns SQL_SUCCESS, but the stored procedure died in process. If one is to run the exact same stored procedure, using the exact same parameter, but run from Query Analyser, it will dependably execut to end and produce the expected results. I assume that I am running into some user time limit at ODBC or Database level, but can not find it or find how to change it. We are running SQL Server 7.0, 2000, and 2003 at dufferent location and experience the exact same results on each. Can anyone help. Al Thanks but Sorry, I have tried to do so.
The profile simply stops. Abrupt end of profile If you use the same parameter it will almost, but not always stop at the same place, but may be two or three steps off. Since profiler only records to the nearest second I can not determine if it is running an exact number of milli seconds, but that is what I suspect. Al Show quote "Narayana Vyas Kondreddi" wrote: > I would use Profiler to track down what the sp is doing and when/where it is > stopping. > -- > HTH, > Vyas, MVP (SQL Server) > SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/ > > > "Al K" <A**@discussions.microsoft.com> wrote in message > news:B2404024-2F19-4332-A10A-B55B30293EA2@microsoft.com... > I have a stored procedure which accepts a single input string of variable > lenght, as its parameter. It then parses that string to develop a set of > inputs which will be input, deleted or updated in the database. The amount > of time required to run is dependant on the length of the stirng passed in > but can exceed one second in rare cases. > > When run, using Execute direct, via ODBC, this stored procedure will begin > execution but then just abruptly quit. It does not take any exit path but > simply quits. The ODBC connection returns SQL_SUCCESS, but the stored > procedure died in process. > > If one is to run the exact same stored procedure, using the exact same > parameter, but run from Query Analyser, it will dependably execut to end and > produce the expected results. > > I assume that I am running into some user time limit at ODBC or Database > level, but can not find it or find how to change it. > > We are running SQL Server 7.0, 2000, and 2003 at dufferent location and > experience the exact same results on each. > > Can anyone help. > > Al > > > Are you running from within a .NET app? If so, you can set the
..CommandTimeout property of your OdbcCommand object. If not, there should be another way to set the Command Timeout property (can't remember the old VB6 way to do this off the top of my head...) If it's not returning an error code, I'd look for reasons other than a timeout though... For instance are all necessary parameters being properly populated and passed via ODBC as they are via QA? You might run SQLProfiler against both to make sure. Show quote "Al K" <A**@discussions.microsoft.com> wrote in message news:B2404024-2F19-4332-A10A-B55B30293EA2@microsoft.com... >I have a stored procedure which accepts a single input string of variable > lenght, as its parameter. It then parses that string to develop a set of > inputs which will be input, deleted or updated in the database. The > amount > of time required to run is dependant on the length of the stirng passed in > but can exceed one second in rare cases. > > When run, using Execute direct, via ODBC, this stored procedure will begin > execution but then just abruptly quit. It does not take any exit path but > simply quits. The ODBC connection returns SQL_SUCCESS, but the stored > procedure died in process. > > If one is to run the exact same stored procedure, using the exact same > parameter, but run from Query Analyser, it will dependably execut to end > and > produce the expected results. > > I assume that I am running into some user time limit at ODBC or Database > level, but can not find it or find how to change it. > > We are running SQL Server 7.0, 2000, and 2003 at dufferent location and > experience the exact same results on each. > > Can anyone help. > > Al Yes, I have run both in Profiler. No I am not using dot net or C#.. I am
running from VC++ 6.0 and if you run the profiler in but QA and ODBC the first steps are exactly the same, but then the stored procedure abruptly stoppes after about 80 steps if called form code. I have actually called it from code inside of the debugger and froze the executable immediatly after the call. Exactly the same results. I am running other stored procedures called from the same code, in the same way, with the only exception is that the other stored procedure will execute in only a few MS and contain only simple code. This one runs several hundred lines of actual code in the stored procedure. Al Show quote "Michael C#" wrote: > Are you running from within a .NET app? If so, you can set the > ..CommandTimeout property of your OdbcCommand object. If not, there should > be another way to set the Command Timeout property (can't remember the old > VB6 way to do this off the top of my head...) > > If it's not returning an error code, I'd look for reasons other than a > timeout though... For instance are all necessary parameters being properly > populated and passed via ODBC as they are via QA? You might run SQLProfiler > against both to make sure. > > "Al K" <A**@discussions.microsoft.com> wrote in message > news:B2404024-2F19-4332-A10A-B55B30293EA2@microsoft.com... > >I have a stored procedure which accepts a single input string of variable > > lenght, as its parameter. It then parses that string to develop a set of > > inputs which will be input, deleted or updated in the database. The > > amount > > of time required to run is dependant on the length of the stirng passed in > > but can exceed one second in rare cases. > > > > When run, using Execute direct, via ODBC, this stored procedure will begin > > execution but then just abruptly quit. It does not take any exit path but > > simply quits. The ODBC connection returns SQL_SUCCESS, but the stored > > procedure died in process. > > > > If one is to run the exact same stored procedure, using the exact same > > parameter, but run from Query Analyser, it will dependably execut to end > > and > > produce the expected results. > > > > I assume that I am running into some user time limit at ODBC or Database > > level, but can not find it or find how to change it. > > > > We are running SQL Server 7.0, 2000, and 2003 at dufferent location and > > experience the exact same results on each. > > > > Can anyone help. > > > > Al > > > Been a while since I did any DB stuff in VC++ but if I recall correctly,
assuming you're using the MFC, I believe CDatabase::SetQueryTimeout (DWORD s) should set the timeout for s seconds. Also I think the default is either 15 or 30 seconds, and setting it to 0 seconds should tell it to never time out. Try setting it for like 600 secs (10 mins) and see if that clears it up. Just checking, but your SP is calling another SP and the second SP is timing out? If so, that might have something to do with why the timeout error is not being passed all the way back to your app. Show quote "Al K" <A**@discussions.microsoft.com> wrote in message news:2CBF7373-ACE3-4C76-8DEC-3128C30674C4@microsoft.com... > Yes, I have run both in Profiler. No I am not using dot net or C#.. I am > running from VC++ 6.0 and if you run the profiler in but QA and ODBC the > first steps are exactly the same, but then the stored procedure abruptly > stoppes after about 80 steps if called form code. I have actually called > it > from code inside of the debugger and froze the executable immediatly after > the call. Exactly the same results. > > I am running other stored procedures called from the same code, in the > same > way, with the only exception is that the other stored procedure will > execute > in only a few MS and contain only simple code. This one runs several > hundred > lines of actual code in the stored procedure. > Al > > "Michael C#" wrote: > >> Are you running from within a .NET app? If so, you can set the >> ..CommandTimeout property of your OdbcCommand object. If not, there >> should >> be another way to set the Command Timeout property (can't remember the >> old >> VB6 way to do this off the top of my head...) >> >> If it's not returning an error code, I'd look for reasons other than a >> timeout though... For instance are all necessary parameters being >> properly >> populated and passed via ODBC as they are via QA? You might run >> SQLProfiler >> against both to make sure. >> >> "Al K" <A**@discussions.microsoft.com> wrote in message >> news:B2404024-2F19-4332-A10A-B55B30293EA2@microsoft.com... >> >I have a stored procedure which accepts a single input string of >> >variable >> > lenght, as its parameter. It then parses that string to develop a set >> > of >> > inputs which will be input, deleted or updated in the database. The >> > amount >> > of time required to run is dependant on the length of the stirng passed >> > in >> > but can exceed one second in rare cases. >> > >> > When run, using Execute direct, via ODBC, this stored procedure will >> > begin >> > execution but then just abruptly quit. It does not take any exit path >> > but >> > simply quits. The ODBC connection returns SQL_SUCCESS, but the stored >> > procedure died in process. >> > >> > If one is to run the exact same stored procedure, using the exact same >> > parameter, but run from Query Analyser, it will dependably execut to >> > end >> > and >> > produce the expected results. >> > >> > I assume that I am running into some user time limit at ODBC or >> > Database >> > level, but can not find it or find how to change it. >> > >> > We are running SQL Server 7.0, 2000, and 2003 at dufferent location and >> > experience the exact same results on each. >> > >> > Can anyone help. >> > >> > Al >> >> >> PS - I believe SetQueryTimeout is also a member of CDaoDatabase.
Show quote "Al K" <A**@discussions.microsoft.com> wrote in message news:2CBF7373-ACE3-4C76-8DEC-3128C30674C4@microsoft.com... > Yes, I have run both in Profiler. No I am not using dot net or C#.. I am > running from VC++ 6.0 and if you run the profiler in but QA and ODBC the > first steps are exactly the same, but then the stored procedure abruptly > stoppes after about 80 steps if called form code. I have actually called > it > from code inside of the debugger and froze the executable immediatly after > the call. Exactly the same results. > > I am running other stored procedures called from the same code, in the > same > way, with the only exception is that the other stored procedure will > execute > in only a few MS and contain only simple code. This one runs several > hundred > lines of actual code in the stored procedure. > Al > > "Michael C#" wrote: > >> Are you running from within a .NET app? If so, you can set the >> ..CommandTimeout property of your OdbcCommand object. If not, there >> should >> be another way to set the Command Timeout property (can't remember the >> old >> VB6 way to do this off the top of my head...) >> >> If it's not returning an error code, I'd look for reasons other than a >> timeout though... For instance are all necessary parameters being >> properly >> populated and passed via ODBC as they are via QA? You might run >> SQLProfiler >> against both to make sure. >> >> "Al K" <A**@discussions.microsoft.com> wrote in message >> news:B2404024-2F19-4332-A10A-B55B30293EA2@microsoft.com... >> >I have a stored procedure which accepts a single input string of >> >variable >> > lenght, as its parameter. It then parses that string to develop a set >> > of >> > inputs which will be input, deleted or updated in the database. The >> > amount >> > of time required to run is dependant on the length of the stirng passed >> > in >> > but can exceed one second in rare cases. >> > >> > When run, using Execute direct, via ODBC, this stored procedure will >> > begin >> > execution but then just abruptly quit. It does not take any exit path >> > but >> > simply quits. The ODBC connection returns SQL_SUCCESS, but the stored >> > procedure died in process. >> > >> > If one is to run the exact same stored procedure, using the exact same >> > parameter, but run from Query Analyser, it will dependably execut to >> > end >> > and >> > produce the expected results. >> > >> > I assume that I am running into some user time limit at ODBC or >> > Database >> > level, but can not find it or find how to change it. >> > >> > We are running SQL Server 7.0, 2000, and 2003 at dufferent location and >> > experience the exact same results on each. >> > >> > Can anyone help. >> > >> > Al >> >> >>  |
|||||||||||||||||||||||
Other interesting topics