I have 2 packages called "A" and "B".

Package "A" is schadulaed to be executed every 10 minutes and it is supoosed
to call Package "B".In package "B" I call an Stored procedure called "Bsp".

Here are my questions:

1) Say that calling package "B" and it is running,10 minues is passes and
it's still working.Package "A" tries to call package "B" (but it is still
working).What happens?

2) If there is any error occured in "Bsp" ,how can I log it some where which
next morning ,operator can figure out what has happened to the packages .Is
there a systable or something from which I can obtain specific information
about pachages whcih were supposed to be run at schadules?


Thanks for your help.
Ray

Hi RayAll
If package A calls package B, and package B runs past 10 minutes, then
package A is executed again, that will instantiate a new package A (as
the old package A is still waiting for Package B to complete), and the
new package A will instantiate a new package B - you won't have conficts
there. So far not a problem.
But it depends on what your stored procedure "Bsp" is doing. There
could be conflicts within the Bsp stored proc that would cause the spid
for the orignal package B's bsp call to block the spid for the new
package B bsp call. Something like original package B's BSP call is
doing a massive update to a table, and new package B's Bsp call is
trying to truncate the table - you could run into blocking processes ect.

Do you really want package A to run again if the previous package A is
still running from 10 minutes ago?

Simon Worth

RayAll wrote:

Hi Simon,

Let me answer this first because it's more important to me.

I really want *NOT* to have two instances of A running at the same time
because of the problem you mentioned.Yes definitely I will be trapped
,because Bsp is working on one table only and Bsp dose have a Transaction
inside which locks the table for some seconds and it might cause some
problems for the other one,Is that right?

You know somehow what I'm doing !back to some days ago.In my storedprocedure
I update a series of fields in each record(in a buffer table) based on some
business rules (not in transaction) and then I distribute each record into
different tables or I update different tables (these are insdie a
transaction)

Thanks Simon,

Ray


Yes,that would be a problem because each
"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

I forgot to ask about the second part of my original posting.
Thanks
"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

If you're using a config type table to hold settings for your
application, I would add a setting in there that sets the flag of the
dts package so that when the job executes it can check your config table
to see if another instance is running (or at what step the package is
at, or what step your stored proc is at etc). That way if package A
starts it can check to see where the other job is at, and if conflicts
can exit without issues.
You can log stored procedure results (errors or other information) in a
table for support to view at anytime. There is not built in mechanism
for a stored procedure to log activity like a DTS package can. But you
certainly can log critical information in tables for review.


--
Simon Worth

Ray5531 wrote:

That's a nice idea (Having a config type Table to track packages executions
and other useful information).I can even use this table for my stored
procedure errors as well,how about that?

Thanks simon
"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

Well that's up to you really, but you would then be adding configuration
and execution settings to a process execution log table. You'll want to
split those things out for readability.
example
Config table

CREATE TABLE [dbo].[ConfigSettings] (
[ConfigId] [int] IDENTITY (1, 1) NOT NULL ,
[SettingName] [nvarchar] (255) NOT NULL ,
[SettingValue] [nvarchar] (2000) NOT NULL ,
[ActiveStatus] [bit] NOT NULL,
[InsertUser] [varchar] (50) NOT NULL CONSTRAINT
[DF_ConfigSettings_InsertUser] DEFAULT (suser_sname()),
[InsertDate] [datetime] NOT NULL CONSTRAINT
[DF_ConfigSettings_InsertDate] DEFAULT (GETDATE()),
[UpdateBy] [varchar] (50) NULL ,
[UpdateDate] [datetime] NULL) ON [PRIMARY]
GO
Here you can hold all kinds of app settings or database settings to
query during execution of packages to check states, file paths, etc.

CREATE TABLE [dbo].[ImportProcessDetails] (
[ProcessID] [int] IDENTITY (1, 1) NOT NULL ,
[ProcessName] [varchar] (50) NOT NULL ,
[ProcessDesc] [varchar] (150) NOT NULL ,
[ProcessNotes] [varchar] (300) NULL ,
[CreateUser] [varchar] (50) NOT NULL CONSTRAINT
[DF_ProcessDetails_CreateUser] DEFAULT (suser_sname()),
[CreateDate] [datetime] NOT NULL CONSTRAINT
[DF_ProcessDetails_InsertDate] DEFAULT (getdate())) ON [PRIMARY]
GO

Here you can log stored procedure results for support to review.

Obviously you'd want to modify the above tables to meet your needs - as
this is off the top of my head, but you get the idea. If you were to
mix the 2 tables it would become confusing as you are mixing 2 very
different types of data.




Simon Worth

Ray5531 wrote:

Thanks Simon.

"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote