Re: SQL state (error) is 37000

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: 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: SQL state (error) is 37000
    ... there is one already there just exit the script? ... > an Openrowset query against the local server for the results ... >>> Since the problem is that SQL server requires that the job names are unique, ... >>> would happen if I gave all of these jobs unique names instead of trying to ...
    (microsoft.public.sqlserver.connect)
  • Re: changing collations
    ... > We have around 150 databases as case sensitive, ... If you want to use ALTER TABLE, I think you still need to script all ... ALTER TABLE ALTER COLUMN command. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)