Error: 17883 - The scheduler 0 appears to be hung...

Environment:
Clustered SQL Server 2K sp3a (single instance)
Product version: 8.00.760
Problem:
I end up in error 17883.
I am aware that the solution is to apply this patch:
821277 MS03-031:Security Patch for SQL Server 2000 Service
Pack 3.
I am also aware that after you install this cumulative
patch you will end up in this problem:
826161 FIX: You are prompted for password after you change
a standard SQL Server login.
After reading this one I found that there are 2
possibility to solve this problem, one if you have already
installed the security patch from MS03-031 and another one
if you do NOT have installed this patch.
Question:
Do I only need to apply this last one (is it also
cumulative?) or do I need to first apply MS03-031 and then
later the FIX 826161?
Please advise.
Kind regards.
You will need to follow the instructions in the section "If you have not
installed the security patch for Microsoft Security Bulletin
MS03-031,Q826161..." . This will include the
fix for the Enterprise Manager issue.
Additionally, this will not fix the cause of the 17883. This will only
provide additional diagnostic information for Microsoft Support should a
17883 occur which was not available in previous builds. If you believe you
are seeing performance issues at the time the 17883's are being recorded, I
urge you to open a case with Microsoft Support for assistance.
Regards,
Farooq Mahmud [MS SQL Support]
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Thank you so much for your answer.
I still don't understand why, when you read MS KB article
810885,under resolution, you find the reference for MS03-
031 Cumulative Security Patch for SQL Server.
What does this cumulative patch solve?
I was sure that it will solve many problems, most of them
security related, but also the 17883 problem.
If this cumulative patch does not solve the 17883 problem
so I don't need to install any patch, right?
Am I wrong?
Please advise.

Error writing data to same destination in single data flow

I am getting the following error running a data flow that splits the input data into multiple streams and writes the results of each stream to the same destination table:

"This operation conflicts with another pending operation on this transaction. The operation failed."

The flow starts with a single source table with one row per student and multiple scores for that student. It does a few lookups and then splits the stream (using Multicast) in several layers, ultimately generating 25 destinations (one for each score to be recorded), all going to the same table (like a fact table). This all is running under a transaction at the package level, which is distributed to a separate machine.

Apparently, I cannot have all of these streams inserting data into the same table at one time. I don't understand why not. In an OLTP system, many transactions are inserting records into the same table at once. Why can't I do that within the same transaction?

I suppose I can use a UnionAll to join them back together before writing to a single destination, but that seems like an unnecessary waste and clutters the flow. Can anyone offer a different solution or a reason why this fails in the first place?

Thanks in advance.

What type of load are you using? Fast load with table lock? Certainly you will need to take off table lock, and maybe drop the fast load too.

An OLTP system is not doing a bulk load, it is doing nice and slow atomic transactions.

I also don't see what is wrong with the Union All transform. If you can get the current design working, then I think you should also try the union, and let us know which is faster, as that would be the real reason to choose one over the other, not good looks!

Re-reading this I see mention of a distributed transaction which matches the error better. Try using speparate connections for each destination. If the only reason for using the transaction is to ensure integrity accross the multiple destinations, then this would make me think of union even more as a distributed transaction can add a fair overhead.

|||

Thanks for the reply and the suggestions. Sorry for the delay in a follow-up, the automated alert for replies didn't work for me and I didn't know there was a reply.

I have since rewritten it to use the UnionAll task and it works. It's not so much an issue of how it looks, rather the trouble required to build it and maintain it. The current version of that task does not make it easy to distinguish which input is which so you can set the appropriate columns correctly. And if you should need to disconnect one stream from the UnionAll and reconnect it, the relative position of that stream moves and gets renamed. It's just a hassle to get right. Elegance in appearance is often times a clue to an elegant technical solution. Not always, but I'm a believer in a good "looking" solution.

You have a good suggestion about turning off the table lock and maybe the fast load option. I hadn't thought of that before I rewrote it. If I get a chance, I'll try the original approach and see if I can get it working.

The need for a transaction is to make sure all the data is loaded or none of it. If some part of it should fail, I want to rerun the entire package to load all the data again. This data flow isn't the only one in the package.

Thanks again.

Error writing data to same destination in single data flow

I am getting the following error running a data flow that splits the input data into multiple streams and writes the results of each stream to the same destination table:

"This operation conflicts with another pending operation on this transaction. The operation failed."

The flow starts with a single source table with one row per student and multiple scores for that student. It does a few lookups and then splits the stream (using Multicast) in several layers, ultimately generating 25 destinations (one for each score to be recorded), all going to the same table (like a fact table). This all is running under a transaction at the package level, which is distributed to a separate machine.

Apparently, I cannot have all of these streams inserting data into the same table at one time. I don't understand why not. In an OLTP system, many transactions are inserting records into the same table at once. Why can't I do that within the same transaction?

I suppose I can use a UnionAll to join them back together before writing to a single destination, but that seems like an unnecessary waste and clutters the flow. Can anyone offer a different solution or a reason why this fails in the first place?

Thanks in advance.

What type of load are you using? Fast load with table lock? Certainly you will need to take off table lock, and maybe drop the fast load too.

An OLTP system is not doing a bulk load, it is doing nice and slow atomic transactions.

I also don't see what is wrong with the Union All transform. If you can get the current design working, then I think you should also try the union, and let us know which is faster, as that would be the real reason to choose one over the other, not good looks!

Re-reading this I see mention of a distributed transaction which matches the error better. Try using speparate connections for each destination. If the only reason for using the transaction is to ensure integrity accross the multiple destinations, then this would make me think of union even more as a distributed transaction can add a fair overhead.

|||

Thanks for the reply and the suggestions. Sorry for the delay in a follow-up, the automated alert for replies didn't work for me and I didn't know there was a reply.

I have since rewritten it to use the UnionAll task and it works. It's not so much an issue of how it looks, rather the trouble required to build it and maintain it. The current version of that task does not make it easy to distinguish which input is which so you can set the appropriate columns correctly. And if you should need to disconnect one stream from the UnionAll and reconnect it, the relative position of that stream moves and gets renamed. It's just a hassle to get right. Elegance in appearance is often times a clue to an elegant technical solution. Not always, but I'm a believer in a good "looking" solution.

You have a good suggestion about turning off the table lock and maybe the fast load option. I hadn't thought of that before I rewrote it. If I get a chance, I'll try the original approach and see if I can get it working.

The need for a transaction is to make sure all the data is loaded or none of it. If some part of it should fail, I want to rerun the entire package to load all the data again. This data flow isn't the only one in the package.

Thanks again.