Problem with select every TOP 1 records from different group in a

Hi Expert,
I came across a situation where the query result returns all non-NULL
records even I use TOP 1 statement. Here is the SQL statement:
SELECT CONTRACT_NUMBER, STEP, STATUS_END_DATE, CIS_PK
FROM dbo.[VIEW1] T1
WHERE (CONTRACT_NUMBER = 'S07-123A' OR
CONTRACT_NUMBER = 'S07-127A' OR
CONTRACT_NUMBER = 'S07-129A') AND (STEP =
(SELECT TOP 1 STEP
FROM dbo.[VIEW2] T2
WHERE T2.CIS_PK = T1.CIS_PK AND
T2.STATUS_END_DATE IS NOT NULL
ORDER BY STEP ASC))
For the above SQL statement, "CIS_PK is key". "STEP" is unique within each
CONTRACT_NUMBER group.
The incorrect result that I got is:
CONTRACT_NUMBER STEP DATE
CIS_PK
S05-137A 4 8/11/2006
728
S05-137A 7 10/1/2006
731
I am expecting the right answer, which is:
S05-137A 4 8/11/2006
728
Thanks,
-adam
I just figured it out by myself. The where clause comparison
T2.CIS_PK=T1.CIS_PK should be T2.CONTRACT_NUMBER = T1.CONTRACT_NUMBER.
Thanks anyway.
-adam
"adam" wrote:

> Hi Expert,
> I came across a situation where the query result returns all non-NULL
> records even I use TOP 1 statement. Here is the SQL statement:
> SELECT CONTRACT_NUMBER, STEP, STATUS_END_DATE, CIS_PK
> FROM dbo.[VIEW1] T1
> WHERE (CONTRACT_NUMBER = 'S07-123A' OR
> CONTRACT_NUMBER = 'S07-127A' OR
> CONTRACT_NUMBER = 'S07-129A') AND (STEP =
> (SELECT TOP 1 STEP
> FROM dbo.[VIEW2] T2
> WHERE T2.CIS_PK = T1.CIS_PK AND
> T2.STATUS_END_DATE IS NOT NULL
> ORDER BY STEP ASC))
> For the above SQL statement, "CIS_PK is key". "STEP" is unique within each
> CONTRACT_NUMBER group.
> The incorrect result that I got is:
> CONTRACT_NUMBER STEP DATE
> CIS_PK
> S05-137A 4 8/11/2006
> 728
> S05-137A 7 10/1/2006
> 731
> I am expecting the right answer, which is:
> S05-137A 4 8/11/2006
> 728
> Thanks,
> -adam

Problem with peer to peer replication across a VPN

We are experiencing some problems with peer to peer replication configuration across a VPN between a 32 bit and 64 bit SQL server 2005. We have been able to configure it but we notice that on successful completion of the configuration the 64 bit machine does not have a subscription for the 32 bit machine article. But both servers have articles created and only the 32 bit machine has a subscription. This is a bizarre experience because the configuration was successful.

We know the server windows account has administrative rights at both end and we can telnet each server successful and all the necessary ports are open.

Might feeling is that the firewall or VPN us acting up but I cannot prove it..

I need help or advice about how to resolve this…

I assume you configure the P2P using P2P wizard. When you go through the wizard, in the summary page, do you see replication indiciate it will create subscription from 64bit machine to 32 machine?

Peng