Re: Multiple problems with a running job

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 11/03/04 

Date: Wed, 3 Nov 2004 10:38:17 -0500

Can you post your code as well as a narrative about what it's doing and why? 

-- 
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Scott M. Lyon" <scott.RED.lyon.WHITE@rapistan.BLUE.com> wrote in message
news:eZSsYobwEHA.3292@TK2MSFTNGP15.phx.gbl...
> 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: Broker task stop
    ... And locks are always mostly from BROKER_TASK_STOP so, ... So this BROKER_TASK_STOP would go and lock things in the user database? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (microsoft.public.sqlserver.programming)
  • Need help with DAO and SQL Server locking issue
    ... SQL Server 2000 blocking locks on Access upsized database. ...
    (microsoft.public.sqlserver.odbc)
  • 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: Back end database lock-up
    ... of access for this application (no SQL Server, ... database into front end and back end databases and put the front end ... only lock the records that are being updated, but it locks the entire ... I think in Novell for rights; read, write, erase, modify, create. ...
    (comp.databases.ms-access)