Re: SQL state (error) is 37000

It's hard to say without knowing more about your script. How
are you checking to see if the job already exists and is
pending? Just wondering if you are querying system tables.
The SQL Server Agent jobs run in a job cache. Statuses are
written to the tables after the fact so you can't query
system tables. You can use sp_help_job as this executes the
extended stored procedure xp_sqlagent_enum_jobs to obtain
the current job status from the job cache.
Not sure how you are handling the scheduling or if you are
executing manually, etc.

-Sue

On Wed, 24 Aug 2005 12:10:02 -0700, "mherberger"
<mherberger@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>Any takers?
>
>"mherberger" wrote:
>
>> Hello,
>> We are experiencing an intermittent problem where we loose our connection to
>> SQL server from RSSQL. The system logs are reporting this in the following
>> order:
>>
>> 1 8/23/2005 1:58:59 AM 5 256 RSSql N/A TSSFOOTAGECTR (52152) SQL state
>> (error) is 37000
>> 1 8/23/2005 1:58:59 AM 5 256 RSSql N/A TSSFOOTAGECTR (52153) SQL state
>> (error) text is [Microsoft][ODBC SQL Server Driver][SQL Server]SQLServerAgent
>> Error: Request to run job sfd_foot_strip_lgth (from User
>> TSSFOOTAGECTR\Administrator) refused because the job already has a pending
>> request from User TSSFOOTAGECTR\Administrator.
>> 1 8/23/2005 1:58:59 AM 5 256 RSSql N/A TSSFOOTAGECTR (52155) A Database
>> error transaction occurred: -1: { ? = CALL dbo.sp_LengthReady (543, 1,
>> 8.400000e+001, 1, 0, 1 )
>> 1 8/23/2005 1:58:59 AM 1 256 RSSql N/A TSSFOOTAGECTR (53773) 000050 064193
>> Closer thread closed transaction 216_Slitter_Reel_1 occurrence 64193 with
>> error 33147
>> 2 8/23/2005 1:58:59 AM 5 256 RSSql N/A TSSFOOTAGECTR (52080) Attempting to
>> reconnect for DB FootageCounter ConnectionId: 0
>>
>> After which all is fine for awhile, then is will occur again.
>>
>> The job sfd_foot_strip_lgth is a very small SQL script that tries to create
>> a new job from RSSQL to SQL Server. It first checks to see if there is
>> already a job with the same name pending and if not it creates a new one.
>> This cycle is around one second in length.
>>
>> It appears that there is a job in SQL Server even thou SQL Server says there
>> isn?t.
>>
>> Any help with this would be greatly appreciated.
>>

.

Relevant Pages

  • Re: SQL state (error) is 37000
    ... Below you will find the script I am using. ... DECLARE @JobID BINARY ... > The SQL Server Agent jobs run in a job cache. ... >>> TSSFOOTAGECTR\Administrator) refused because the job already has a pending ...
    (microsoft.public.sqlserver.connect)
  • SQL state (error) is 37000
    ... We are experiencing an intermittent problem where we loose our connection to ... SQL server from RSSQL. ... already a job with the same name pending and if not it creates a new one. ...
    (microsoft.public.sqlserver.connect)
  • RE: Attempt to initiate a new SQL Server operation with results pending.
    ... > Subject: Re: Attempt to initiate a new SQL Server operation ... >>>initiate a new SQL Server operation with results pending. ...
    (perl.dbi.users)
  • Re: Monitoring replication from subscriber
    ... have a look on my site for a script to find pending merge changes. ... some discussion about the reliability of the data in it, ... Paul Ibison SQL Server MVP, ...
    (microsoft.public.sqlserver.replication)
  • RE: SQL state (error) is 37000
    ... > We are experiencing an intermittent problem where we loose our connection to ... > a new job from RSSQL to SQL Server. ... > already a job with the same name pending and if not it creates a new one. ...
    (microsoft.public.sqlserver.connect)