Very Computer

This seems to be a pretty hot topic so I guess I'll ad my post.

We have SQL 2000 as a cluster on 2 servers.

Our DTS package brings a small amount of data into some tables into another
database and then runs a visual basic executable which validates the data,
then pushes it back into the production database.

2 of the cluster instances are identical: one is for test, one is for
production.

Here is what is happening:

In the test instance the DTS package runs fast, both when executed directly
and when scheduled as a job (about 30 seconds).

In the production instance the DTS package runs fast when executed directly
(about 30 seconds) but when scheduled it takes about an hour and a half!
This makes no sense to me.

I checked to make sure that the user who owns the DTS package and the
scheduled job has permissions (they do - they are a sysadmin) AND that they
have the correct registry access permissions using DCOMCNFG- they do.

Any ideas?

Without seeing your configuration, the first thing I would
do is to put some writes into your DTS (to a flat file, or
to a database).  Though this will slow you down a bit
initially, it will at least gear you to the exact action
(connection/query/execution) that is taking so long.

I'll check back into this message later once you have
narrowed down location.

shawn

Quote:>-----Original Message-----
>This seems to be a pretty hot topic so I guess I'll ad my
post.

>We have SQL 2000 as a cluster on 2 servers.

>Our DTS package brings a small amount of data into some
tables into another
>database and then runs a visual basic executable which
validates the data,
>then pushes it back into the production database.

>2 of the cluster instances are identical: one is for
test, one is for
>production.

>Here is what is happening:

>In the test instance the DTS package runs fast, both when
executed directly
>and when scheduled as a job (about 30 seconds).

>In the production instance the DTS package runs fast when
executed directly
>(about 30 seconds) but when scheduled it takes about an
hour and a half!
>This makes no sense to me.

>I checked to make sure that the user who owns the DTS
package and the
>scheduled job has permissions (they do - they are a

sysadmin) AND that they

Quote:>have the correct registry access permissions using
DCOMCNFG- they do.

>Any ideas?

>.

Sorry- I should have been more explicit. The execution of the VB executable
is what is taking so long. It executes over 2200 stored procedures (located
in the "edit" database) and runs them against the tables in the edit
database. If the dts package (which calls the VB program) is executed
directly the VB executable takes about 30 seconds to finish. If the DTS
package is scheduled it takes 1.5 hours.

Quote:> Without seeing your configuration, the first thing I would
> do is to put some writes into your DTS (to a flat file, or
> to a database).  Though this will slow you down a bit
> initially, it will at least gear you to the exact action
> (connection/query/execution) that is taking so long.

> I'll check back into this message later once you have
> narrowed down location.

> shawn

> >-----Original Message-----
> >This seems to be a pretty hot topic so I guess I'll ad my
> post.

> >We have SQL 2000 as a cluster on 2 servers.

> >Our DTS package brings a small amount of data into some
> tables into another
> >database and then runs a visual basic executable which
> validates the data,
> >then pushes it back into the production database.

> >2 of the cluster instances are identical: one is for
> test, one is for
> >production.

> >Here is what is happening:

> >In the test instance the DTS package runs fast, both when
> executed directly
> >and when scheduled as a job (about 30 seconds).

> >In the production instance the DTS package runs fast when
> executed directly
> >(about 30 seconds) but when scheduled it takes about an
> hour and a half!
> >This makes no sense to me.

> >I checked to make sure that the user who owns the DTS
> package and the
> >scheduled job has permissions (they do - they are a
> sysadmin) AND that they
> >have the correct registry access permissions using
> DCOMCNFG- they do.

> >Any ideas?

> >.

I think we figured this out. The drive that the VB executable is on is a
virtual drive in a virtual drive array. We had been specifying the drive
letter for the path, but we changed it to the UNC. That and a re-boot seems
to have cured it.

Go figure.

Quote:> This seems to be a pretty hot topic so I guess I'll ad my post.

> We have SQL 2000 as a cluster on 2 servers.

> Our DTS package brings a small amount of data into some tables into
another
> database and then runs a visual basic executable which validates the data,
> then pushes it back into the production database.

> 2 of the cluster instances are identical: one is for test, one is for
> production.

> Here is what is happening:

> In the test instance the DTS package runs fast, both when executed
directly
> and when scheduled as a job (about 30 seconds).

> In the production instance the DTS package runs fast when executed
directly
> (about 30 seconds) but when scheduled it takes about an hour and a half!
> This makes no sense to me.

> I checked to make sure that the user who owns the DTS package and the
> scheduled job has permissions (they do - they are a sysadmin) AND that
they
> have the correct registry access permissions using DCOMCNFG- they do.

> Any ideas?