Re: SQL state (error) is 37000

Without knowing what you are trying to accomplished, why
this was written, I really can't say. Sorry but I have no
idea what the purpose of changing the jobs is all about so I
really can't tell you a better way.

-Sue

On Fri, 26 Aug 2005 03:26:02 -0700, "mherberger"
<mherberger@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

>This script is inherited and I am not sure why it was setup that way.
>More that likely because the previous developer either didn?t think that
>it was an issue or didn?t discover it. I would definitely like to know of
>anyway
>to get around deleting a job (and losing the data) and since my SQL skills
>are limited what would you suggest? What different approach would be
>better? Your input is proving to be most useful in helping with this
>problem and I hope that a more efficient method can be found.
>
>-Mike
>
>"Sue Hoegemeier" wrote:
>
>> Yeah...I'm not surprised. So the job is in the job cache
>> with a pending request and then you delete it, create a new
>> one with the same name and try to run the new one. They just
>> collide. As I posted, you could use sp_help_job but I would
>> guess that's not really the best solution. Seems you
>> probably could benefit from a different approach that leads
>> you to dropping and recreating the job like this. Why are
>> you doing that anyway?
>>
>> -Sue
>>
>> On Thu, 25 Aug 2005 05:08:02 -0700, "mherberger"
>> <mherberger@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>>
>> >Thank you for your response Sue,
>> >
>> >From your post I am beginning to understand what is going on and that is
>> >what I suspected.
>> >
>> >This job is being created automatically when a signal is received from a PLC
>> >with a reset bit turned on.
>> >
>> >Below you will find the script I am using.
>> >
>> >BEGIN TRANSACTION
>> > DECLARE @JobID BINARY(16)
>> > DECLARE @ReturnCode INT
>> > SELECT @ReturnCode = 0
>> >IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name =
>> >N'[Uncategorized (Local)]') < 1
>> > EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
>> >
>> > -- Delete the job with the same name (if it exists)
>> > SELECT @JobID = job_id
>> > FROM msdb.dbo.sysjobs
>> > WHERE (name = N'sfd_foot_strip_lgth')
>> > IF (@JobID IS NOT NULL)
>> > BEGIN
>> > -- Check if the job is a multi-server job
>> > IF (EXISTS (SELECT *
>> > FROM msdb.dbo.sysjobservers
>> > WHERE (job_id = @JobID) AND (server_id <> 0)))
>> > BEGIN
>> > -- There is, so abort the script
>> > RAISERROR (N'Unable to import job ''sfd_foot_strip_lgth'' since there is
>> >already a multi-server job with this name.', 16, 1)
>> > GOTO QuitWithRollback
>> > END
>> > ELSE
>> > -- Delete the [local] job
>> > EXECUTE msdb.dbo.sp_delete_job @job_name = N'sfd_foot_strip_lgth'
>> > SELECT @JobID = NULL
>> > END
>> >
>> >BEGIN
>> >
>> > -- Add the job
>> > EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT ,
>> >@job_name = N'sfd_foot_strip_lgth', @owner_login_name =
>> >N'TSSFOOTAGECTR\Administrator', @description = N'Execute package:
>> >sfd_foot_strip_lgth', @category_name = N'[Uncategorized (Local)]', @enabled =
>> >1, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend =
>> >0, @notify_level_eventlog = 2, @delete_level= 0
>> > IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
>> >
>> > -- Add the job steps
>> > EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id =
>> >1, @step_name = N'sfd_foot_strip_lgth', @command = N'DTSRun
>> >/~Z0x0D0529348822A81EFB2F7244320066ECF16FF81A987DC083441C31296B9F20CE490D0B3C8DD6D561218C04A8A2C448A88FDB9AA3507AB91B90D3466BEF80BF2C6E79D20C69C906A91403D1915295AC501CB9297335D0C7A185F738DC10CE21796B956FEB604A0D3F605256
>> >', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem
>> >= N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0,
>> >@retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0,
>> >@on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
>> > IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
>> > EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID,
>> >@start_step_id = 1
>> >
>> > IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
>> >
>> > -- Add the Target Servers
>> > EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID,
>> >@server_name = N'(local)'
>> > IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
>> >
>> >END
>> >COMMIT TRANSACTION
>> >GOTO EndSave
>> >QuitWithRollback:
>> > IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
>> >EndSave:
>> >
>> >Any assistance would be greatly appreciated,
>> >
>> >Mike
>> >
>> >
>> >
>> >"Sue Hoegemeier" wrote:
>> >
>> >> 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
    ... This script is inherited and I am not sure why it was setup that way. ... >> GOTO QuitWithRollback ... >>COMMIT TRANSACTION ... >>> The SQL Server Agent jobs run in a job cache. ...
    (microsoft.public.sqlserver.connect)
  • Error Running generated job creation script
    ... I then tried to run the script to ... IF (@JobID IS NOT NULL) ... GOTO QuitWithRollback ... COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.server)
  • Re: Commit Transaction Gets Deleted - Unable to save SP
    ... the last line 'Commit Transaction' has been removed. ... All I have done is wrap the script into various ... > CREATE TABLE #Extract ( ... > INNER JOIN DEALER_SOURCE_DATA SD WITH (NOLOCK) ...
    (comp.databases.ms-sqlserver)
Loading