Difference between ADO and Query Analyzer result set

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Difference between ADO and Query Analyzer result set

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-04-18 : 09:32:37

Bob writes "We are using XP Pro machines, MDAC 2.8, talking to SQL Server 2000 on a Win 2000 SP4 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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources