Out-of-Memory error on Sort Transformation

Trying to determine if anyone else has encountered this problem and, if so, have you obtained a resolution:

I have a data flow initiated from a Foreach Container. Inside the Data Flow there is a Source Script transformation connected to a Sort transformation with an OLE DB Destination.

The Sort transformation has the sort keys defined and the "Eliminate Duplicates" option checked.

The first pass into the Data Flow is successful. The 2nd pass fails in the Sort:

Information: 0x4004300A at Load TC03, DTS.Pipeline: Validation phase is beginning.
Information: 0x40043006 at Load TC03, DTS.Pipeline: Prepare for Execute phase is beginning.
Information: 0x40043007 at Load TC03, DTS.Pipeline: Pre-Execute phase is beginning.
Information: 0x4004300C at Load TC03, DTS.Pipeline: Execute phase is beginning.
Warning: 0x80004005 at Load TC03, Sort [1506]: Unspecified error
Error: 0xC020821B at Load TC03, Sort [1506]: A worker thread in the Sort transformation stopped with error code 0x80004005. A catastrophic error was encountered while sorting a buffer.
Error: 0xC020821B at Load TC03, Sort [1506]: A worker thread in the Sort transformation stopped with error code 0x%1!8.8X!. A catastrophic error was encountered while sorting a buffer.
Error: 0xC0208296 at Load TC03, Sort [1506]: The input buffer could not be cloned. An out-of-memory condition occurred or there was an internal error.
Error: 0xC0047022 at Load TC03, DTS.Pipeline: The ProcessInput method on component "Sort" (1506) failed with error code 0x80004005. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
Error: 0xC0047021 at Load TC03, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0x80004005.
Error: 0xC0047039 at Load TC03, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at Load TC03, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Information: 0x40043008 at Load TC03, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x402090DF at Load TC03, hlp_ISOTypeLossTC03 [379]: The final commit for the data insertion has started.
Information: 0x402090E0 at Load TC03, hlp_ISOTypeLossTC03 [379]: The final commit for the data insertion has ended.
Information: 0x40043009 at Load TC03, DTS.Pipeline: Cleanup phase is beginning.
Information: 0x4004300B at Load TC03, DTS.Pipeline: "component "hlp_ISOTypeLossTC03" (379)" wrote 5388 rows.
Task failed: Load TC03
Warning: 0x80019002 at Foreach TC03 File: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at ISOTypeLoss: The Execution method succeeded, but the number of errors raised (7) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "ISOTypeLoss.dtsx" finished: Failure.

Does this ring a bell with anyone?

ThanksI think this is a known issue that they are currently looknig into.
its not necassarily a problem with the Sort, its more caused by the Foreach loop (I think).

Check out thread "For Loop container - out of memory condition"

-Jamie|||

Hi Raul,

Yes, this is a known issue. We've fixed it for the next CTP. In the meantime, the only workaround I'm aware of is to put the data flow task in a separate package and use an Execute Package task to schedule the execution of the data flow task.
regards,
ash

|||What volume of data do you get this error on?

It would be interesting to see if ExtraSort component (http://www.ivolva.com/extrasort.html) would hit the same problem.|||Very small - about 300 rows|||Ash:

I have the June CTP installed and am getting the same error:

Warning: 0x80004005 at Load V6 Policy, Sort [4166]: Unspecified error

Error: 0xC020821B at Load V6 Policy, Sort [4166]: A worker thread in the Sort transformation stopped with error code 0x80004005. A catastrophic error was encountered while sorting a buffer.

Error: 0xC020821B at Load V6 Policy, Sort [4166]: A worker thread in the Sort transformation stopped with error code 0x%1!8.8X!. A catastrophic error was encountered while sorting a buffer.

Error: 0xC0208296 at Load V6 Policy, Sort [4166]: The input buffer could not be cloned. An out-of-memory condition occurred or there was an internal error.

Error: 0xC0047022 at Load V6 Policy, DTS.Pipeline: The ProcessInput method on component "Sort" (4166) failed with error code 0x80004005. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

Error: 0xC0047021 at Load V6 Policy, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0x80004005.

Error: 0xC0047039 at Load V6 Policy, DTS.Pipeline: Thread "WorkThread1" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.

Error: 0xC0047021 at Load V6 Policy, DTS.Pipeline: Thread "WorkThread1" has exited with error code 0xC0047039.
Another poster suggested a thread for "For Loop container - out of memory condition" - I searched this forum and it returned no results.

Any update as to whether the fix made the June CTP?

|||

Raul Villaronga wrote:

Any update as to whether the fix made the June CTP?

It didn't. It'll be in the next CTP.|||Yes, this was caused by a bug in the sort transform. It was not correctly releasing some resources when the dataflow completed. The forloop just exposes the problem because the sort doesn't get freed between executions.|||FYI - Still getting this in September CTP.
|||Both memory issues I had with June CTP including the catastrophic failure of the sort in a loop and the data viewer consuming memory have been fixed in September CTP.

outer join operation?

Hello everyone:

I am doing a sql exercise which states like this, "Determine which orders have not yet shipped and the name of the customer that placed each order. Sort the results by the date on which the order was placed."

this exercise asks me to use traditional approach(using where clause along with outer join operator(+), and using the join keyword)

the tables used for this exercise is customers and orders. the data type of each table are as follows:

customers table: contaion all the customers who placed orders and who have not recently placed an order

Name Null? Type
------------- --- -------
CUSTOMER# NOT NULL NUMBER(4)
LASTNAME VARCHAR2(10)
FIRSTNAME VARCHAR2(10)
ADDRESS VARCHAR2(20)
CITY VARCHAR2(12)
STATE VARCHAR2(2)
ZIP VARCHAR2(5)
REFERRED NUMBER(4)

order table: this table contains data for current month orders or unfilled orders from previous month.

Name Null? Type
------------- --- -----
ORDER# NOT NULL NUMBER(4)
CUSTOMER# NUMBER(4)
ORDERDATE DATE
SHIPDATE DATE
SHIPSTREET VARCHAR2(18)
SHIPCITY VARCHAR2(15)
SHIPSTATE VARCHAR2(2)
SHIPZIP VARCHAR2(5)

the two tables have customer# attribute in common. I think I should use outer join for this exercise. I did several tries. i guess i am still little confused about the defination for ourter join when it comes to using 'join' keyword along with left, right full option.

I did three attempts but each one give me different results. I couldn't figure out which one is right. Please help.

--My own selection statement are as follows:
a) traditional approache (using where clause)

select order#, lastname, firstname, shipdate
from customers c, orders o
where c.customer# = o.customer#(+)
order by o.shipdate

27 rows were selected

my thinking on this is if there is no corresponding row in customers table, which means that customer who have not been place an order recently. the results will display customer who have not been place an order recently. what if I put (+) on left side will this give me the correct result?

b) use join keyword.

select lastname, firstname, order#, shipdate
from customers c right join orders o
on c.customer# = o.customer#
order by o.shipdate

21 rows were selected.

Please give me some suggestion on how to solve this problem. if you know some good web source that give clear explnation on outer join along with examples, pleate refer them to me. I will appreciate your help!

Sincerely,
sjgrad03
1-25-05if the assignment actually requires that you use an OUTER JOIN, the first thing you need to do is decide which of the tables will have a "missing" row

in your second example, customers c right join orders o, you are saying that there can be orders without matching customers

i think that's wrong

what you want is customers c left join orders o

this will return all customers, including those who have no matching orders

it would be extremely unlikely to have an order without a matching customer

the second thing you need to focus on is the actual requirement -- "orders which have not yet shipped"

so what you want is orders

therefore, an OUTER JOIN here would be wrong!!

you don't want customers without orders, you want orders and their customers

(assuming, again, that you cannot have an order without a customer)

if this course or assignment specifically said you must use an OUTER JOIN, then it stinks!

in addition, there is no reason why they should be teaching you a proprietary method like (+), unless the purpose of the course or assignment is to teach you how to move away from that syntax and start to learn the standard

OS Service pack

Is there a way from within a SQL Script to determine what OS service
pack is installed other than SELECT @.@.VERSION? I am trying to
automate a script based upon the OS service pack installed (if any),
and having to somehow parse the results of @.@.VERSION seems unreliable.
Any ideas?
Brandon
--
"In the beginning the universe was created. This has made a lot of
people very angry, and has been widely regarded as a bad move." -
Douglas Noel Adams (1952-2001)Have a look at SERVERPROPERTY and xp_msver.
Off the top of my head I think you need to run :- xp_msver windowsversion
--
HTH
Ryan Waight, MCDBA, MCSE
"Brandon Lilly" <brandon.lilly@.nospam_medevolve.com> wrote in message
news:eZw5EB3kDHA.3612@.TK2MSFTNGP11.phx.gbl...
> Is there a way from within a SQL Script to determine what OS service
> pack is installed other than SELECT @.@.VERSION? I am trying to
> automate a script based upon the OS service pack installed (if any),
> and having to somehow parse the results of @.@.VERSION seems unreliable.
> Any ideas?
> Brandon
> --
> "In the beginning the universe was created. This has made a lot of
> people very angry, and has been widely regarded as a bad move." -
> Douglas Noel Adams (1952-2001)
>|||You can get the OS information by running the
xp_msver 'WindowsVersion' extended stored procedure.
Like so...
CREATE TABLE #windows_info
(
idx INT
, name NVARCHAR(128)
, internal_value INT
, character_value NVARCHAR(255)
)
INSERT #windows_info
EXECUTE xp_msver 'WindowsVersion'
SELECT * FROM #windows_info
I don't know if this is easier than parsing the @.@.VERSION
string though.
Tim
>--Original Message--
>Is there a way from within a SQL Script to determine
what OS service
>pack is installed other than SELECT @.@.VERSION? I am
trying to
>automate a script based upon the OS service pack
installed (if any),
>and having to somehow parse the results of @.@.VERSION
seems unreliable.
>Any ideas?
>Brandon
>--
>"In the beginning the universe was created. This has
made a lot of
>people very angry, and has been widely regarded as a bad
move." -
>Douglas Noel Adams (1952-2001)
>
>.
>|||You can get it from the registry, using the undocumented
xp_regread. Try:
exec
master..xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsof
t\Windows NT\CurrentVersion', 'CSDVersion'
If your OS is NT 4, this won't distinguish between SP6 and
SP6a.
HTH
Vern
>--Original Message--
>Is there a way from within a SQL Script to determine what
OS service
>pack is installed other than SELECT @.@.VERSION? I am
trying to
>automate a script based upon the OS service pack
installed (if any),
>and having to somehow parse the results of @.@.VERSION
seems unreliable.
>Any ideas?
>Brandon
>--
>"In the beginning the universe was created. This has
made a lot of
>people very angry, and has been widely regarded as a bad
move." -
>Douglas Noel Adams (1952-2001)
>
>.
>