Multiple problems with a running job

From: Scott M. Lyon (scott.RED.lyon.WHITE_at_rapistan.BLUE.com)
Date: 11/03/04 

Date: Wed, 3 Nov 2004 10:30:34 -0500

I've got a stored procedure that essentially consists of about a half dozen
CURSORs (one after the other, not nested), each of which processes some
data. Each CURSOR has a source that is a SELECT with at least one INNER JOIN
(which I cannot change).

I'm trying to run this stored procedure as a job on our SQL server (so it
will run regardless of whether I'm there or not, or if my PC is on or not).

Here are the two major problems I'm having:

1) The job is locking my source database (despite the fact that it's a
database that I know is unchanging), making it impossible for me to run
multiple instances of the job simultaneously
2) When the job is running, I cannot even go into Enterprise Manager, and
the databas in question, and Management->Current Activity, as it locks up my
machine (never coming back) - I suspect this may be related to the locking
issue mentioned above.

First of all, I'm declaring the cursors as LOCAL STATIC.

Second, for every FROM and JOIN, after the table name I'm including WITH
(NOLOCK, READUNCOMMITTED) to prevent locks. Obviously this isn't working as
planned.

Can anyone offer suggestions what I can try to resolve both issues?

Thanks!

Relevant Pages

  • Re: Opinions on approach, please...
    ... that INTENTIONALLY used "locks" to serialize ... It all comes down to database design taking in consideration what the ... It is perfectly possible to declare 2 cursors. ... With standard COBOL indexed files, if any process added a records PAST ...
    (comp.lang.cobol)
  • Re: Opinions on approach, please...
    ... I the reason that I mentioned "locks" is that I am familiar with applications ... It all comes down to database design taking in consideration what the ... It is perfectly possible to declare 2 cursors. ... With standard COBOL indexed files, if any process added a records PAST ...
    (comp.lang.cobol)
  • Re: Multiple problems with a running job
    ... SQL Server MVP ... > database that I know is unchanging), making it impossible for me to run ... I'm declaring the cursors as LOCAL STATIC. ... > to prevent locks. ...
    (microsoft.public.sqlserver.programming)
  • Re: Decouple SQL queries from class in OOP design
    ... > when mixing SQL queries directly with the client classes. ... > Some of my database tables are not stable during the development ... That is done for a reason. ... or leaks cursors this is the way to catch it. ...
    (comp.object)
  • Re: Data Comparison
    ... the missing and the different records have to be ... The database being used is Oracle 9i. ... Cursors - For each of the tables, cursors can be opened and then the ... Is there a primary key? ...
    (comp.databases.oracle.misc)
Loading