Re: SQL state (error) is 37000

OK. I see the light at the end of the tunnel (hopefully its not a train!).

Since all I want to do is see if the job is already there couldn't I just
check sysjobs and if
there is one already there just exit the script? As I stated before even if
the script exits,
the next time its run it will pick up any records that were not tranfered.

-Mike

"Sue Hoegemeier" wrote:

> It's not pretty...sp_help_job is already doing an insert so
> you can't create a temp table and insert the results in a
> stored procedure or query. I think you can do something like
> an Openrowset query against the local server for the results
> of sp_help_job but Jasper Smith wrote a good example of a
> job check. Check this:
> http://groups.google.com/group/microsoft.public.sqlserver.server/msg/9887db008328bef3
>
> -Sue
>
> On Mon, 29 Aug 2005 13:47:57 -0700, "mherberger"
> <mherberger@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
> >After reviewing the process again I seem to have made an error. Sorry :-(
> >
> >This "job" is only used to start a DTS package that then transfers data from
> >a SQL Server
> >table were records that are marked (by a field) as unsent are transfered to
> >a Ingres DB
> >and then marks them as sent.
> >
> >Back to square one, how can I use "sp_help_job" (or something else) in the
> >code below
> >to confirm that a job isnt already running? If I find a job is already
> >running, I am thinking
> >that I will abort the process (not create a new one or delete the existing
> >one) and then
> >the next time this script is run (hopefully) the job will be gone. It
> >appears to clear itself
> >up after a few seconds so I believe this fix will work if I can find a way
> >to accuratly
> >determine an existing job.
> >
> >
> >"mherberger" wrote:
> >
> >> Since the problem is that SQL server requires that the job names are unique,
> >> what
> >> would happen if I gave all of these jobs unique names instead of trying to
> >> use the same
> >> name? Since I am not monitoring them and only wish them to create new
> >> entries in the
> >> SQL Server DB what problems (if any) can you think of? Would this be a solid
> >> approach?
> >>
> >> -Mike
> >>
> >> P.S. Thank you for your assistance with this.
> >>
> >> "mherberger" wrote:
> >>
> >> > What is being changed is new footage data. A basic way of describing it is to
> >> > image a
> >> > length of material entering a machine. When the PLC's first read it, it is
> >> > the beginning of the
> >> > length or "0". Later it is again but this time the length is > "0" as the
> >> > material has been run
> >> > through. This number is what is what we need to save. Its Footage length or
> >> > the
> >> > amount of material used to complete the scheduled job on the shop floor.
> >> >
> >> > I think that it was thought that this would never happen. That the job was
> >> > so small that
> >> > SQL Server would always be able to finish the job before the next job needed
> >> > to be
> >> > created. For the most part this is true. Most of the time when the job is
> >> > created there
> >> > is no other job running. However once or twice a day there is an overlap and
> >> > this
> >> > is when the problem occurs. Dropping the job and then recreating it is not
> >> > the solution
> >> > that this application requires. What would be better is to instead create
> >> > jobs as required regardless
> >> > of the status of the previous jobs. This way there would be no loss of data
> >> > trnsfered
> >> > between the databases.
> >> >
> >> > -Mike
> >> >
> >> >
> >> > "Sue Hoegemeier" wrote:
> >> >
> >> > > It runs a package. I have no idea why a job is being dropped
> >> > > and recreated to run the package. What is it that is being
> >> > > changed?
> >> > >
> >> > > -Sue
> >> > >
> >> > > On Sun, 28 Aug 2005 17:11:02 -0700, "mherberger"
> >> > > <mherberger@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> >> > >
> >> > > >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: SQL state (error) is 37000
    ... would happen if I gave all of these jobs unique names instead of trying to ... SQL Server DB what problems can you think of? ... >>>It is a way to transport data between databases. ... >>>the script below? ...
    (microsoft.public.sqlserver.connect)
  • 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: 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)
  • Re: SQL Security
    ... except I'm having problems making it work in a script. ... ;Set properties of DB objects and open connection to database ... > from Books Online (within the SQL Server program group): ... > communicate with SQL Server. ...
    (microsoft.public.sqlserver.server)
  • 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.sqlserver.server)
Loading