执行批处理时出现SQL错误。错误消息是：抛出了类型'System.OutOfMemoryException'的异常。 [英] SQL Error while executing batch. Error message is: Exception of type 'System.OutOfMemoryException' was thrown.

问题描述

大家好，



我对sql概念有点新鲜。请帮助改进我在Sql Query方面的逻辑。



以下是我在这里用sql做的事情。当我运行脚本时，我收到错误执行批处理时出错。错误信息是：抛出类型''System.OutOfMemoryException''的异常被抛出



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

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"

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

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;

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



请尽早帮我解决这个问题。



谢谢

Chirnajeevi

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

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

Thanks
Chirnajeevi

推荐答案

朋友，



不要使用Cursor ...试试这个如下

开始检查你的查询正确，因为你要删除14个表，如果有什么错误删除然后问题。所以请使用数据备份并检查语句。

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.

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

问候，

GVPrabu

Regards,
GVPrabu

这篇关于执行批处理时出现SQL错误。错误消息是：抛出了类型'System.OutOfMemoryException'的异常。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！