SQL Begin/End tranasction block size limitation

Is there some internal size limitation on the amount of records and/or commands that can be executed with a Begin/End block? I ask because I just wrote a VS C# program that calls a stored procedure. Inside that stored procedure is a begin/end transaction. In my initial tests on a relative small dataset(less then 100 records) my code executed fine. I just tried to test this on a complete dataset(almost 1,000 records) and I am getting a message about no matching end transaction statement for begin transaction. It is as if the stored procedure has used up available memory and the begin/end block cuts cut in half. This is SQL Server 2008 R2. Is there some SET command I can embed in the sp to give it more space? There are four sections to this sp. Two insert statements and two updates statement. All four statements are embedded with the same begin/end block. Would it be best to put each statement within its own begin/end block? The issue I have there is that I want all the instructions to be rolled back if any one fails.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Data DudeCommented:
>end transaction statement for begin transaction.
By any chance are you using dynamic SQL, where the values being embedded may have a 'begin' or 'end'?
The number of bytes allowed in a stored procedure is 250MB.  I doubt that you are hitting that limit with four statements.  Most likely there is a syntax error that is preventing the stored procedure from executing properly.  You also mentioned that you want it to treat it as one transaction.  Are you using BEGIN TRANSACTION, COMMIT and ROLLBACK?
rwheeler23Author Commented:
There are actually TRY CATCH statements within the sp then the Begin Transaction, Commit or Rollback Transactions statement. I actually did this month by month and it worked fine. Then I did one month, then two months and it was fine until I got to 8 months. We are only talking about 300 or so records. It is as if there is an incomplete statement between the begin and end statement. This stored procedure is called from a VS C# program and it is self-contained. It inserts order header records, then order detail records, then it does some updates on each. There are no dynamics sql statements here. I am inserting records into one table based on data in another table. I have attached the code. There is nothing special in here. I suppose I could try executing it in SQL to see what happens.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Your SQL looks pretty straight forward.  Nothing obvious popped out at me.  Executing it in SSMS is a good idea.  That should help narrow down where the issue is.  You could also throw in some print statements before each statement to make it easier to follow the execution.
rwheeler23Author Commented:
Well now I have to look at my VS C# code. I just ran it in SSMS on the entire dataset and it ran with no issues. The really scary thing is that the transactions do get rolled back. Perhaps I will also turn on SQL Profiler to see what is gong on under the covers. Thanks for taking the time to look at this.
No problem.  Good luck!
Scott PletcherSenior DBACommented:
The COMMIT TRAN should be in the TRY block, not after the CATCH  block: you don't want to try to commit after rolling back in the CATCH.

Also, you should condition the ROLLBACK on XACT_STATE(), to make sure that a transaction is active, otherwise you will get the annoying error:
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.
    IF XACT_STATE() <> 0
        ROLLBACK TRAN MoveSOPTrxsToHistory

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwheeler23Author Commented:
Thanks everyone for the helpful tips.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.