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.
Microsoft SQL Server 2008
Last Comment
rwheeler23
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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?
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
Step-4-rbsMoveSOPTrxsToHistory.sql
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks everyone for the helpful tips.