It's not SQLAgent that you need to be concerned with... it's the security setting on the server itself... WHO is the SQL Server SERVICE logging in as?  If it's not...

What's wrong with LEFT(somentextval,100) ???

I absolutely agree with using a tally table as Remi and Sreejith have... I just can't bring myself to create a tool that helps avoid cursors and loops by using...

Yes... instead of using the "-o somefilename" parameter, use the DOS "redirect as append" characters ">> somefilename".

Sorry Vandy... I just can't read minds today... what's not right about the total sum?

Vandy... how about telling us which columns the +ive and =ive numbers are stored in?  If they are both in the InvoicedValue column, then Marino's should have done the trick...

Better make sure you do a backup before you truncate the log or you won't be able to recover to a point in time which is the purpose of Full...

Sure... what's the Primary Key of the person table?

Maybe this weekend... right now, I'm still sucking my thumb wonder what the heck I was thinking

I'll say... as for me, I can't find the examples I had made for something else (might not even be the same problem ...

Outstanding... thank you for the feedback.

Ok... first things first...

Rob... I'm confused... I thought you were saying that some sproc OUTSIDE of the trigger was being rolled back and that you need what was happening inside...

The only way to keep from rolling back the record inserted by the trigger, is to do it with another connection. 

SP_OA could be used to create such a connection...

...and, still, if you have FULL RECOVERY set, none of that will help the LOG file because all of the updates will be recorded whether they be single updates, batches,...

JUST in case you need it as a single query (In the absence of CTE's, I do like the temp table solution better which is why I didn't answer previously...