Rogue Cursor

Can anybody help'
I have a stored procedure that takes 6 minutes to run when
executed through Query analyser When its called by a
scheduled job it takes over 6 hours. Nothing else runs on
the server so there are no issues with conflicts.
The stored procedure is a simple fast forward cursor that
goes through the rows of a table containing approx 300k
rows. I have evaluated the execution using the SQL
profiler and when the stored procedure is run from a job
the cursor seems to pause for approx 600ms every 7
cycles , this pause does not happen at all when it is
executed via query analyser. As there are over
The server is SQLServer7 Standard (evaluation) and run on
an NT4 platform. I have tried to run the same stored
procedure on a much higher spec machine (SQLServer 2k,
windows 2k server, dual processor, raid config) but
although the runtimes for either method were a lot
quicker, calling the stored procedure from a job is at
least 50% slower.
Can anyone tell me?
A. What causes this?
B. Can it be fixed?
C. What else could be affected?You very very seldom need any cursors at all. Can you post the code so
someone here can rewrite it without a cursor? Based on my experience your sp
will then run in about 6 seconds, without a cursor.
--
Jacco Schalkwijk
SQL Server MVP
"H jones" <hmjones@.atlanticeg.com> wrote in message
news:0a3e01c3c975$333c1cc0$a401280a@.phx.gbl...
> Can anybody help'
> I have a stored procedure that takes 6 minutes to run when
> executed through Query analyser When its called by a
> scheduled job it takes over 6 hours. Nothing else runs on
> the server so there are no issues with conflicts.
> The stored procedure is a simple fast forward cursor that
> goes through the rows of a table containing approx 300k
> rows. I have evaluated the execution using the SQL
> profiler and when the stored procedure is run from a job
> the cursor seems to pause for approx 600ms every 7
> cycles , this pause does not happen at all when it is
> executed via query analyser. As there are over
> The server is SQLServer7 Standard (evaluation) and run on
> an NT4 platform. I have tried to run the same stored
> procedure on a much higher spec machine (SQLServer 2k,
> windows 2k server, dual processor, raid config) but
> although the runtimes for either method were a lot
> quicker, calling the stored procedure from a job is at
> least 50% slower.
> Can anyone tell me?
> A. What causes this?
> B. Can it be fixed?
> C. What else could be affected?
>

Returning ERRORs from SP's

Hi all,
am using an access front end (XP) and ODBC. SQL Server 2k
When doing an update statement in Query Analyser, QA will tell me I have
violated a, eg. ForeignKey in table in database.
Is there a way to return that error description from a Stored Procedure (the
violation would be from a statement in the SP)? and therefore return it
to my front end.
thanksTake a look at RAISERROR and sysmessages system table
in Books on line
"SJ" wrote:

> Hi all,
> am using an access front end (XP) and ODBC. SQL Server 2k
> When doing an update statement in Query Analyser, QA will tell me I have
> violated a, eg. ForeignKey in table in database.
> Is there a way to return that error description from a Stored Procedure (t
he
> violation would be from a statement in the SP)? and therefore return i
t
> to my front end.
> thanks
>
>|||Wouldn't that be nice :) No, not in the 2k version of SQL Server. It is up
to the client to handle and deal with errors. If your program doesn't
cancel the batch, you can raise another error after the offending statement
so you can see where the error occurred though.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"SJ" <myocard@.hotmail.com> wrote in message
news:asq_d.10693$1S4.1124667@.news.xtra.co.nz...
> Hi all,
> am using an access front end (XP) and ODBC. SQL Server 2k
> When doing an update statement in Query Analyser, QA will tell me I have
> violated a, eg. ForeignKey in table in database.
> Is there a way to return that error description from a Stored Procedure
> (the violation would be from a statement in the SP)? and therefore
> return it to my front end.
> thanks
>