Symptoms: DTS with Analysis Services Task , job status
is "executing" if run as scheduled job and the total
excecuting time is more than one day

package info
The total time for executing the pacckage is 30 mins
the data source is oracle database

Environment:
Window 2000 server running SQL Server 2000 and
Analysis Services both SP4.

If I run through as a "job", the job status is executing
there is no error is logged.

Hi,

What is te latest entry from the job history of the SQL Server Agent job?

Regards,
Bas

"This posting is provided "AS IS" with no warranties, and confers no
rights."

I stopped the job after the job executing more than one
day, the error/message is
the following:

Executed as user: [server name]\SYSTEM. The step was
cancelled (stopped) as the result of a stop job request.

Hi Annie,

That is indeed the error if you stop it manually what was the message
before this one. We need to know what it was doing when it was running for
a day? I want to know if the message is ""Performing completion actions."
because I am aware of a problem that has your symtoms and is hanging with
this error.

Regards,
Bas

"This posting is provided "AS IS" with no warranties, and confers no
rights."

I've seen the same thing with MS Analysis Services



How else can one automate cube processing?!!? I've found the same
issues on multiple forums. No one seems to know.


--
Posted via http://dbforums.com

Hi,

Ok, this is the situation we know that could cause this:

When you run a SQL Server Agent job, the job may not respond if all the
following conditions are true:

- The job is configured to send an e-mail notification upon completion.
- The mail profile of SQL Server Agent is configured to use Microsoft
Exchange Server and the Microsoft Outlook Address Book service.
- The Outlook Address Book service references the Outlook Contacts folder.
- The computer that is running Exchange Server is not available.

When the computer that is running Exchange Server is not available, a SQL
Server Agent job that is configured to send an e-mail notification by using
that computer may stop responding. If you verify the job history of the
SQL Server Agent job, you may see that the job steps are completed
successfully, but the job remains active with the status of "Performing
completion actions." This status indicates that the job is trying to send
the e-mail notification.

Even if the computer that is running Exchange Server is available later,
the affected job may not respond. At the next scheduled time, the affected
job still has a status of "Performing completion actions." Also, the
affected job may not run until you restart the SQL Server Agent service.

This is caused by the SQL Server Agent mail connectivity is handled by a
single mail interface thread. All the SQL Server Agent job uses this mail
interface thread for email communication. Each SQL Server Agent job waits
without any timeout limit until the mail command of the job returns from
the mail interface thread. If the mail interface thread stops responding
because of some external reason, the dependent job keep waiting
infinitely.

This occurs because the mail interface thread stops responding while
trying to resolve the mail recipient names through the Microsoft Outlook
Address Book.

To work around this problem, perform one of the following tasks:

- Remove the reference to the Contacts folder from the Microsoft Outlook
Address Book service in the SQL Server Agent mail profile.
- Remove the complete Microsoft Outlook Address Book service from the SQL
Server Agent mail profile.

If any one of these tasks is performed, the mail interface thread detects
if the Microsoft Exchange Server is unavailable. The SQL Server Agent job
that is configured to send an email notification upon completion,
completes the job after approximately two minutes of wait time for sending
the email notification. The job will not fail or stop responding if there
is a failure in sending email notification.

HTH,
Bas

"This posting is provided "AS IS" with no warranties, and confers no
rights."

Bas



Interesting thoughts. No where has anyone mentioned exchange or SQL
Agent, However.



I have no Send mail tasks in my job. I simply what to process 2 cubes
including their respective shared dimensions.



I have an output log, but nothing peculiar is being logged. The thing
just hangs.


--
Posted via http://dbforums.com

Hi,

ok, here are a few things that you could check.

Is this DTS package runned on the local machine, if no is sp3 (ptsfull.exe)
installed on the machine where the package is runned from?

Is the user whre the SCHEDULED package is running under (SQL agent) a
member of the olap administrator group?

Are you maybe running multiple DTS package in parallel?

Did you sent the execute on main package option in the DTS workflow?

HTH,
Bas

"This posting is provided "AS IS" with no warranties, and confers no
rights."

Thanks for the reply Bas



The server is SQL Service Pack 3a. Not sure what ptsfull.exe is?



Yes, the user is a Domain Admin AND a member of the local OLAP
administrator group.



No. I use the 'completion' workflow steps.



Not sure what 'Execute on main package' option is. Please advise.


--
Posted via http://dbforums.com

Hi,

PTSfull.exe is the AS sp3 client it installs MDAC2.7sp1, DSO and PTS
components on your machine. Install this on the machine where you run the
DTS package from. It's located in the Analysis server 2000 servicepack
directory msolap\install\PTS

BOL SQL server:
"Execute on main package thread" > Force the task associated with this step
to execute on the main package thread rather than on a spawned thread.
Choose this option if you are executing a task against a data provider that
is not free-threaded and does not support parallel execution of tasks.

You can set it as a workflow property in your DTS package.

HTH,
Bas

"This posting is provided "AS IS" with no warranties, and confers no
rights."