Problem with linked server stored proc in a maintenance plan

Hello there,

I have a scenario where I need a few stored procs to auto-execute on an hourly basis so I thought it would be nicely done in a maintenance plan job list. I have experience with this in sql 2000 but I am struggling with sql 2005.

I have been struggling with my maintenance plan to successfully run the 2 jobs that it has to complete:
1) execute a stored proc that creates/updates a client in the Client table on the local server
(This step works fine without hassles)
2) execute a stored proc that synchronizes this entry with a database on another server. This stored proc works fine outside the maintenance plan, but inside the maintenance plan job it gives me an error :
Executed as user: NT AUTHORITY\SYSTEM. Cannot roll back T1. No transaction or savepoint of that name was found. [SQLSTATE 25000] (Error 6401)

I have tried looking on the net and forum to see whether i can solve this but i am stuck. What do I have to keep in mind executing this stored proc as a maintenance plan? What am i missing.

Thanks for any advice
Mike
Doesn't anyone know what this could be?

I believe that it has something to do with permissions or security but don't know for sure.|||

Mike,

It looks like the error is being caused by embedded transactions. Then a rollback is occurring due to some event (possibly one of the insert/update further ahead failing and issuing a rollback, which tries to rollback ALL transactions.

I found this article, because I'm seeing the same thing, and looking at the code that's failing, I do have several nested transactions.

http://www.informit.com/articles/article.asp?p=26657&seqNum=5&rl=1

It sounds like we might be having the same problem.

Hope this helps.

Bill

sql