I've been having an issue with a step in a job.

I have a step (SQL Server Analysis Services Query) inside of a 2005 SQL
Server job which calls an Analysis Services stored procedure. I have begin,
commit, rollback transactions all within the code.

However, if the job itself fails (for instance, broken network connection,
SQL Agent goes down), the entire processing rolls back. It's as if the step
itself is being ran as a transaction. When this happens none of the
transactions actually commit.

This has been a major stumbling block and is preventing the cube from
rolling out in production. It's quite frustrating. Any ideas?

Thanks in advance!






Call

AMO.ProcessMeasureGroupProd

(
"Sales Hourly",
"Sales",
"vFact_HourlyActivitySummaryCurrent",
"Production"
)

Anyone have a clue on this?

This is stopping us from putting SSAS2005 into production.

I've been stuck on it for weeks.


"Jesse O." <jesperzz (AT) hotmail (DOT) com> wrote

My guess is that each request (begin transaction, execute processing,
commit) is being executed in its own session...

I would highly recommend running SQL Profiler against Analysis Services and
check the sessions that the commands are running on.

HTH,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/Sho...ID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.


"Jesse O." <jesperzz (AT) hotmail (DOT) com> wrote

Ran a trace, and it seems that they are all running under the same session
(ConnectionID) and same SPID. Everything from the calling of the stored
procedure to the processing of the partitions. All the same ConnectionID, 7.

Any other ideas? I've been banging my head on this for awhile now.










"Akshai Mirchandani [MS]" <akshaim (AT) online (DOT) microsoft.com> wrote

Does Profiler show what happens when you simulate the failure? I would guess
that for some reason the session is getting cancelled or something like
that...

Thanks,
Akshai
--
Try out the MSDN Forums for Analysis Services at:
http://forums.microsoft.com/MSDN/Sho...ID=83&SiteID=1

This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Jesse O." <jesperzz (AT) hotmail (DOT) com> wrote