Difference between ADO and Query Analyzer results

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

We are using XP Pro machines, MDAC 2.8, talking to SQL Server 2000 on a Win
2000 server.
We have a Stored Procedure that is called from a COM+ object using
an ADO connnection object and Command object with parameters.
It returns about 194 rows and works as desired.

Here is what the query in question looks like:

SELECT TOP 100 PERCENT budget_info.budget_year,
budget_info.budget_phase_description,
objective.description AS description,
pep.budget_item_id,
FROM budget_info
INNER JOIN dbo.fn_pep_oe_demo_om(@budget_id) pep ON budget_info.budget_id
= pep.budget_id
LEFT OUTER JOIN objective ON pep.capital_objective_id =
objective.objective_id
WHERE (@budget_id IS NULL or budget_info.budget_id = ISNULL(@budget_id, 0))
AND budget_info.budget_year = @budget_year
AND budget_info.budget_phase_id = @budget_phase_id
AND (NOT objective.code IN ('PF', 'RE', 'RAD'))
AND class_code in ('MM', 'ER')
FOR XML AUTO, ELEMENTS

I realize the SELECT TOP 100 PERCENT is bizarre, I was handed this problem,
it is not my code.

Here is the ADO code:


Dim oStrm As ADODB.Stream
Dim oCmd As ADODB.Command

Set oCmd = New ADODB.Command
If psConn = "" Then
oCmd.ActiveConnection = GetConnectionString()
Else
oCmd.ActiveConnection = psConn
End If

oCmd.CommandTimeout = 300
oCmd.CommandText = psSP
oCmd.CommandType = adCmdStoredProc

collectParams oCmd, params
' collectParams does this for each first dimension array element:
' oCmd.Parameters.Append oCmd.CreateParameter(params(i)(0),
params(i)(1), adParamInput, params(i)(2), v)

' Create the output stream to stream the results into.
Set oStrm = New ADODB.Stream
oStrm.Open

' Set command's output stream to the output stream just opened.
oCmd.Properties("Output Stream") = oStrm

' Execute the command, thus filling the output stream.
oCmd.Execute , , adExecuteStream
' Position the output stream back to the beginning of the stream.
oStrm.Position = 0

Set RunSPReturnStream = oStrm

Set oCmd.ActiveConnection = Nothing



The weird part is that when I pull this query into Query Analyzer and
run it, I get a much smaller result set of 44 rows.
In playing around with it, we were able to get the query to return the
same result set as ADO by making a single change to one of the where
conditions.
The where condition always did bother me, but not so much in the way it
turned out.

SELECT TOP 100 PERCENT budget_info.budget_year,
budget_info.budget_phase_description,
objective.description AS description,
pep.budget_item_id,
FROM budget_info
INNER JOIN dbo.fn_pep_oe_demo_om(@budget_id) pep ON budget_info.budget_id
= pep.budget_id
LEFT OUTER JOIN objective ON pep.capital_objective_id =
objective.objective_id
WHERE (@budget_id IS NULL or budget_info.budget_id = ISNULL(@budget_id, 0))
AND budget_info.budget_year = @budget_year
AND budget_info.budget_phase_id = @budget_phase_id

===> AND (NOT IsNull(objective.code,0) IN ('PF', 'RE', 'RAD'))

AND class_code in ('MM', 'ER')
FOR XML AUTO, ELEMENTS


I am curious why there is a difference between the ADO and Query Analyzer
results
with the same query????

Thanks

.