Link to home
Start Free TrialLog in
Avatar of rwheeler23
rwheeler23Flag for United States of America

asked on

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.
SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
Avatar of rwheeler23

ASKER

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.
Step-4-rbsMoveSOPTrxsToHistory.sql
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks everyone for the helpful tips.