Re: SQL Server Analysis Services Query in a SQL Server job step

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/ShowForum.aspx?ForumID=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@xxxxxxxxxxx> wrote in message
news:eZQMsbxtGHA.1808@xxxxxxxxxxxxxxxxxxxxxxx
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@xxxxxxxxxxx> wrote in message
news:uwTPkrZtGHA.3552@xxxxxxxxxxxxxxxxxxxxxxx
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"
)





.

Relevant Pages

  • Re: Locking and Delay in a Bottleneck
    ... that should avoid the gap issue on rollback, ... SQL Server MVP ... the saving transaction, this journal voucher is having a header table ... number in a table whereupon you commit. ...
    (microsoft.public.sqlserver.programming)
  • Re: Trigger does not seem to fire from front end or enterprise manager
    ... there is no need to even do COMMIT in a trigger. ... > the transaction will be committed as the statement completes. ... > against the inserted and deleted tables inside your trigger code. ... run a profiler trace and you will see what SQL EM submits. ...
    (microsoft.public.sqlserver.programming)
  • Re: Behavior of Connection.commit()
    ... Does it say "commit" and "rollback" and other SQL commands that affect the transaction state are not allowed? ... How sure can the driver ever be about the state of the transaction? ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: Autonomous Transactions
    ... You mean that I always have to explicitly commit or rollback sql ... You don't have much SQL in your app. ... Now what happens is that at the beginning of each procedure you start a transaction and at the end you either commit or rollback whatever the procedure does. ... The outermost procedure represents the interface to your app and you app does NOT contain any COMMIT/ROLLBACK. ...
    (comp.databases.oracle.server)
  • Re: Commit in SQL Task step
    ... Why then are you using a package ... Each step should commit its work up to the point of failure. ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... >>A transaction is a unit of work right so if I had 9 steps ...
    (microsoft.public.sqlserver.dts)
Loading