Re: SQL state (error) is 37000

Really all that this script does is send some information to SQL server in
the form of a job.
It is a way to transport data between databases. The amount of data is very
small; about
5 fields. Both databases exist on the same server. Is there any other way to
do this besides
the script below?

-Mike

"Sue Hoegemeier" wrote:

> 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: Error 15401 using sp_grantlogin (not addressed by current KB articles)
    ... Restarting Windows 2000 resolved the problem for this particular account, ... confused when it sees a duplicate SID. ... > One way to get SQL Server to agree with the renamed NT ... > Preview (to ensure the script was created), ...
    (microsoft.public.sqlserver.security)
  • Re: How do you create a databse?
    ... Wayne Snyder, MCDBA, SQL Server MVP ... I support the Professional Association of SQL Server and it's ... > I think that it may be time to use a script to create databases. ...
    (microsoft.public.sqlserver.server)
  • Re: SQL state (error) is 37000
    ... What is being changed is new footage data. ... SQL Server would always be able to finish the job before the next job needed ... >>It is a way to transport data between databases. ... >>the script below? ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL Server metadata compare
    ... > Anyone knows a VCL component to compare between two SQL Server ... > databases (or one script and a database), ...
    (borland.public.delphi.thirdpartytools.general)
  • Re: Scripting a SQL database
    ... Virtually everything SQL Server Enterprise Manager does can be ... To run the generated script in ASP, ... You can also create a DTS (Data Transformation Package) ... that you'll want to select the "copy database objects and data" ...
    (microsoft.public.inetserver.asp.db)
Loading