Anyone else have problems with Sort/Merge in For Each loop?

Just wondering if I am an isolated case or just doing stuff other people don't. Ive created around a dozen packages for importing various types of data into a datawarehouse and some of the packages have random crashes. Over time I have been trying to tie together what the crashing ones have in common and now believe I have completely isolated it: Sort + Merge inside a dataflow in a for each loop. The more sorts and merges in the flow the more likely the problem is to occur.

This week I created 2 identical packages to import some XML... one used some gnarly complicated substrings and such to extract some data and add it as derived columns while the other used the XML correctly that produced 2 streams (one being meta data - other being detail data) and sorted + merged them. The gnarly substring package could run all night (processes about a file every 2 or 3 seconds so you can do the math) while the merged data flows crashes somewhere between 1 and 4 hrs of running - usually right around 3 hrs (also processes about 1 file every 2 seconds). The file it crashes on wil be completly random and not related to file size - I've had crashes on files with as little as 1 row of data and 1 row of metadata and as big as 1 row of metadata and 100,000+ rows of data. Another more complicated package I created has 4 merges (and thus 8 sorts) and it will crash anywhere between 5 minutes and 20 minutes of running.

The crash is usually one where the whole process just shuts down with a memory dump but occasionally I have gotten errors about out of memory or warnings about threads leaking buffers.

I am trying to do some "research" to see if others have this problem. I've spent 1.5 months writing all ETL jobs in SSIS and its gotten to the point management has asked me to explore other platforms. I do have a case open with Microsoft on the package with 4 merges - but we've spent weeks now just trying to get debugging tools to get them some useful info when the process crashes. I am running SP1 and kb91822.

Anyone else?

FYI to anyone that comes across this thread - msft identified a bug in the sort object (stack heap corruption) and now has a hotfix. If you run into this same situation please contact them (until the patch is released to the public)

any ideas?

I have a system currently runs in house only.
The system is for enforcement officers where they are creating and working
on case files.
When they create a new case, the insert statement has logic that will go out
and find the last case number used and then increment that number by 1 and
assign this number as the case number.
I am looking to extend this application to Tablet PC's so they can take the
application out into the field. I have the database setup for merge
replication and I am using the Windows Syncrozination Manager to syncronize
the database when they logged into the network.
I would like to have the ability for them to create a new case while in the
field and disconnected from the network.
that is where the problem comes in, while they are disconnected they won't
know what the last case number was, so I need to assign a temporary case
number till they syncronize, during the syncrozination process assign
permannet case numbers.
Anybody had a situation like this or have any ideas how I could do this?
ThanksHi Mike
I have not deal with you situation, but I could envisage that a solution may
be to sync with a set of "holding" tables rather than the main ones and then
a batch process would update from the main tables at some other point.
John
"Mike Read" wrote:
> I have a system currently runs in house only.
> The system is for enforcement officers where they are creating and working
> on case files.
> When they create a new case, the insert statement has logic that will go out
> and find the last case number used and then increment that number by 1 and
> assign this number as the case number.
> I am looking to extend this application to Tablet PC's so they can take the
> application out into the field. I have the database setup for merge
> replication and I am using the Windows Syncrozination Manager to syncronize
> the database when they logged into the network.
> I would like to have the ability for them to create a new case while in the
> field and disconnected from the network.
> that is where the problem comes in, while they are disconnected they won't
> know what the last case number was, so I need to assign a temporary case
> number till they syncronize, during the syncrozination process assign
> permannet case numbers.
> Anybody had a situation like this or have any ideas how I could do this?
> Thanks
>
>

any ideas?

I have a system currently runs in house only.
The system is for enforcement officers where they are creating and working
on case files.
When they create a new case, the insert statement has logic that will go out
and find the last case number used and then increment that number by 1 and
assign this number as the case number.
I am looking to extend this application to Tablet PC's so they can take the
application out into the field. I have the database setup for merge
replication and I am using the Windows Syncrozination Manager to syncronize
the database when they logged into the network.
I would like to have the ability for them to create a new case while in the
field and disconnected from the network.
that is where the problem comes in, while they are disconnected they won't
know what the last case number was, so I need to assign a temporary case
number till they syncronize, during the syncrozination process assign
permannet case numbers.
Anybody had a situation like this or have any ideas how I could do this?
Thanks
Hi Mike
I have not deal with you situation, but I could envisage that a solution may
be to sync with a set of "holding" tables rather than the main ones and then
a batch process would update from the main tables at some other point.
John
"Mike Read" wrote:

> I have a system currently runs in house only.
> The system is for enforcement officers where they are creating and working
> on case files.
> When they create a new case, the insert statement has logic that will go out
> and find the last case number used and then increment that number by 1 and
> assign this number as the case number.
> I am looking to extend this application to Tablet PC's so they can take the
> application out into the field. I have the database setup for merge
> replication and I am using the Windows Syncrozination Manager to syncronize
> the database when they logged into the network.
> I would like to have the ability for them to create a new case while in the
> field and disconnected from the network.
> that is where the problem comes in, while they are disconnected they won't
> know what the last case number was, so I need to assign a temporary case
> number till they syncronize, during the syncrozination process assign
> permannet case numbers.
> Anybody had a situation like this or have any ideas how I could do this?
> Thanks
>
>

any ideas?

I have a system currently runs in house only.
The system is for enforcement officers where they are creating and working
on case files.
When they create a new case, the insert statement has logic that will go out
and find the last case number used and then increment that number by 1 and
assign this number as the case number.
I am looking to extend this application to Tablet PC's so they can take the
application out into the field. I have the database setup for merge
replication and I am using the Windows Syncrozination Manager to syncronize
the database when they logged into the network.
I would like to have the ability for them to create a new case while in the
field and disconnected from the network.
that is where the problem comes in, while they are disconnected they won't
know what the last case number was, so I need to assign a temporary case
number till they syncronize, during the syncrozination process assign
permannet case numbers.
Anybody had a situation like this or have any ideas how I could do this?
ThanksHi Mike
I have not deal with you situation, but I could envisage that a solution may
be to sync with a set of "holding" tables rather than the main ones and then
a batch process would update from the main tables at some other point.
John
"Mike Read" wrote:

> I have a system currently runs in house only.
> The system is for enforcement officers where they are creating and working
> on case files.
> When they create a new case, the insert statement has logic that will go o
ut
> and find the last case number used and then increment that number by 1 and
> assign this number as the case number.
> I am looking to extend this application to Tablet PC's so they can take th
e
> application out into the field. I have the database setup for merge
> replication and I am using the Windows Syncrozination Manager to syncroniz
e
> the database when they logged into the network.
> I would like to have the ability for them to create a new case while in th
e
> field and disconnected from the network.
> that is where the problem comes in, while they are disconnected they won't
> know what the last case number was, so I need to assign a temporary case
> number till they syncronize, during the syncrozination process assign
> permannet case numbers.
> Anybody had a situation like this or have any ideas how I could do this?
> Thanks
>
>

Answers needed for these multiple choice questions

Question 1) Can you use the functions nvl and decode as well as the CASE statement interchangeably?

A Only NVL with DECODE and vice versa
B Only Decode with CASE statement and vice versa.
C NVL with Decode or CASE statement as well as Decode and Case Statement interchangeably
D All three can be used interchangeably

Question 2) An inner join

A Is preferred if it meets business requirements
B Requires a (+) sign in the sql syntax for Oracle
C Should never be used on dimensional metrics
D A and B
E A and C only

Question 3) You can use a Sub-select in a query after

A The FROM clause
B The IN clause
C The '=' operator
D All of the above
E All of the above and more

Question 4) Many to Many relationships:

A Result in double-counting
B May be resolved in different ways but using a relationship table is always preferred.
C Should be avoided as far as possible for simplicity
D All of the above
E A and C only

Question 5) Views:

A Often minimize re-work during the development or integration phase
B Can be used to implement security
C Can be materialized in recent versions of Oracle
D None of the above
E All of the above

ThanksWhy do you want us to GIVE you these answers? Why don't you study and do the homework yourself?|||I sure did a sincere attempt as far as homework goes also with a mixed success. For example, when I tried this on my database , I found that Question 1) could be D, Question 3) could be E and so on. And however, I may not be the strongest in logical reasoning, and hence seeking suggestions because second opinions and suggestions are valuable as they are. Atleast I will have a chance to correct myself in the chance that I may be wrong in what I thought or found. It is possible.

Thanks