possibly merge join bug?

i'm merge joining 2 data sources, one is oracle and the other is excel...the problem is in the oracle source, it's a sql statement like:

select hdr.div_ord_no, hdr.mtr_no, hdr.prod_cd
from qctrl_div_ord_header hdr,
(select max(sub.eff_dt_from) min_eff_dt_from, div_ord_no
from qctrl_div_ord_header sub
group by div_ord_no
) tmp
where hdr.eff_dt_from = tmp.min_eff_dt_from
and hdr.div_ord_no = tmp.div_ord_no

having that sql statement, merging will come out with 0 rows

however, having a simple query like:

select hdr.div_ord_no, hdr.mtr_no, hdr.prod_cd
from qctrl_div_ord_header hdr

merging will come out with 2 rows

you may think that the data in the first sql statement is not there for the merge, which causing the 0 rows, however, the data is there, i'm only joining by one column and definitely the data is there, the merge result should be 2 rows for both query statements

i believe this is a problem with SSIS, anyway around this?

Are the inputs to the MERGE JOIN sorted? I is a requirement that they are for MERGE JOIN to work correctly.

Note that setting IsSorted=Yes on the input does not mean that the data gets sorted for you!

-Jamie

|||yes, the input are sorted, everything should be setup correctly, hence, i got the merge to run and work as expected with the simple query

Possible Corrupt Table

I have 1 table in a 200+ table database
The database is Merge Synchornised and has been working fine for 2
years +
The same database is at several customers and the DB is fully
relational

I have a table which creates client timeout errors whenever an insert
or update is issued

The table has foreign keys and primary key and links parent to
children tables so if I need to recreate the table I will also need
advice on the best way to do this to keep the integrity of the
database

I wasn't sure the table was the problem so I deleted all publications
and disbled the server from being a distributor

I cannot find any error logs with any clues so can only assume the is
the first corruption I have ever seen on SQL 2K (SP3)

I have defragmented the drive, reindexed the tables, shrunk databases
(Plenty of space available)

Please advise any course of action you think may help me.

Regards Paul Goldney[posted and mailed, please reply in news]

paul goldney (paulg@.wizardit.co.uk) writes:
> I have a table which creates client timeout errors whenever an insert
> or update is issued

There is very little information to work from in your post.

If you really suspect corruption, run DBCC CHECKTABLE on the table.

However, I would suggest that there two other possibilities which
are much more likely:

1) There are triggers on the table, and which are poorly implemented
and takes long time to execute.
2) There is a blocking issue. The latter can be investigated by
running sp_who while waiting for the INSERT statement to complete.
If you see a non-zero value in the Blk column that column is blocking
the spid on that row.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp