SQL Error while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi all,

I am bit new to sql concepts. Please help out in improving my logic in-terms of Sql Query.

Below is what i am doing here with sql. When i run the scrip i am getting error as "An error occurred while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown"

****************************************************

SQL

Select AccountID into #northaccount from AFS_Account WHERE AccountID like 'Y%' and EffectiveTo is null  -- (85724 row(s) affected)

declare table_cursor cursor for
                SELECT * FROM #northaccount

declare @AcctID as varchar(20)
declare @count as bigint
set @count = 0;
open table_cursor;
            fetch next from table_cursor into @AcctID;
            while @@fetch_status = 0
            begin
            set @count = @count + 1
                select @AcctID, @count
                BEGIN TRANSACTION
                Delete From table1 where AccountId= @AcctID
                Delete From table2 where AccountId= @AcctID
                Delete From table3 where AccountId= @AcctID
                Delete From table4 where AccountId= @AcctID
                Delete From table5 where AccountId= @AcctID
                Delete From table6 where AccountId= @AcctID
                Delete From table7 where AccountId= @AcctID
                Delete From table8 where AccountId= @AcctID
                Delete From table9 where AccountId= @AcctID
                Delete From table10 where AccountId= @AcctID
                Delete From table11 where AccountId= @AcctID
                Delete From table12 where AccountId= @AcctID
                Delete From table13 where AccountId= @AcctID
                Delete From table14 where AccountId= @AcctID
                commit transaction
                fetch next from table_cursor into @AcctID;
            end;
close table_cursor;
deallocate table_cursor;
drop table #northaccount;

******************************************************

Please help me out in this issue as early as possible.

Thanks
Chirnajeevi

Posted 7-Mar-13 17:59pm

ommi.chiru

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Accepted Answer · 2013-03-07T19:01:00

Hi friend,

Don't use Cursor... try this as follows
before start check your Query Properly, because your are going to delete 14 tables, If anything wrongly Deleted then problem. So take Data Backup and check the statement.

SQL

DECLARE @AFSAccount TABLE(AccountID INT)

INSERT INTO @AFSAccount (AccountID) 
SELECT AccountID FROM AFS_Account WHERE AccountID like 'Y%' and EffectiveTo is null  -- (85724 row(s) affected)

DELETE T1 FROM table1 T1 INNER JOIN @AFSAccount AFS ON AFS.AccountID=T1.AccountID
DELETE T2 FROM table2 T2 INNER JOIN @AFSAccount AFS ON AFS.AccountID=T2.AccountID
DELETE T3 FROM table3 T3 INNER JOIN @AFSAccount AFS ON AFS.AccountID=T3.AccountID
DELETE T4 FROM table4 T4 INNER JOIN @AFSAccount AFS ON AFS.AccountID=T4.AccountID
DELETE T5 FROM table5 T5 INNER JOIN @AFSAccount AFS ON AFS.AccountID=T5.AccountID
DELETE T6 FROM table6 T6 INNER JOIN @AFSAccount AFS ON AFS.AccountID=T6.AccountID
DELETE T7 FROM table7 T7 INNER JOIN @AFSAccount AFS ON AFS.AccountID=T7.AccountID
DELETE T8 FROM table8 T8 INNER JOIN @AFSAccount AFS ON AFS.AccountID=T8.AccountID
DELETE T9 FROM table9 T9 INNER JOIN @AFSAccount AFS ON AFS.AccountID=T9.AccountID
DELETE T10 FROM table10 T10 INNER JOIN @AFSAccount AFS ON AFS.AccountID=T10.AccountID
DELETE T11 FROM table11 T11 INNER JOIN @AFSAccount AFS ON AFS.AccountID=T11.AccountID
DELETE T12 FROM table12 T12 INNER JOIN @AFSAccount AFS ON AFS.AccountID=T12.AccountID
DELETE T13 FROM table13 T13 INNER JOIN @AFSAccount AFS ON AFS.AccountID=T13.AccountID
DELETE T14 FROM table14 T14 INNER JOIN @AFSAccount AFS ON AFS.AccountID=T14.AccountID

Regards,
GVPrabu