SQLDataReader and Stored Procedure Problem

  • Thread starter Thread starter bsheeres
  • Start date Start date
B

bsheeres

Hi,

SQL2005 and .Net 2.0

I’m experiencing some strange behavior between a SQLDataReader and a
Stored Procedure. I’m populating the DR and DataTable like this:

oDR = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
oDT1.Load(oDR)

Everything works fine until I add a “where” clause to the select
statement in the SP at which point the DR and DT report that no
records are returned. Now I know what you’re thinking “Well duh, your
where clause is eliminating the records”. However the SP, when run in
the EM (SQL Server Management Studio) shows there are results. Both
the EM and the SqlCommand are calling the SP using the same parameters
as verified using a trace.

Below are a few variations of the select statement. What is
interesting is that in all cases the SP returned values in the EM, but
only in the last case did the DR recognize results.

/*
EM Works – DR does not
*/
select ca.SYS_CITY as GROUP_NAME, s.*
from #sales2 s
inner join CUSTOMER_ADDRESSES ca
on s.COMPANY_CODE = ca.COMPANY_CODE
and s.GROUP_FIELD = ca.ADDRESS_CODE
where ca.CUSTOMER_NUMBER = @CustomerNumber
order by s.COMPANY_CODE, s.GROUP_FIELD, s.SA_PERIOD

/*
EM Works – DR does not
For Debugging force the parameter to be a known good value
*/
set @CustomerNumber = ‘300162’

select ca.SYS_CITY as GROUP_NAME, s.*
from #sales2 s
inner join CUSTOMER_ADDRESSES ca
on s.COMPANY_CODE = ca.COMPANY_CODE
and s.GROUP_FIELD = ca.ADDRESS_CODE
where ca.CUSTOMER_NUMBER = @CustomerNumber
order by s.COMPANY_CODE, s.GROUP_FIELD, s.SA_PERIOD

/*
EM Works so does the DR
Hard code the value in the where clause
*/
select ca.SYS_CITY as GROUP_NAME, s.*
from #sales2 s
inner join CUSTOMER_ADDRESSES ca
on s.COMPANY_CODE = ca.COMPANY_CODE
and s.GROUP_FIELD = ca.ADDRESS_CODE
where ca.CUSTOMER_NUMBER = ‘300162’
order by s.COMPANY_CODE, s.GROUP_FIELD, s.SA_PERIOD


Also, yes, SET NOCOUNT ON in included in the procedure.

Any ideas? What am I missing here?

Thanks.
 
I suggest you to take a look what SQL Server Profiler says...

--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi,

SQL2005 and .Net 2.0

I’m experiencing some strange behavior between a SQLDataReader and a
Stored Procedure. I’m populating the DR and DataTable like this:

oDR = oCmd.ExecuteReader(CommandBehavior.CloseConnection)
oDT1.Load(oDR)

Everything works fine until I add a “where” clause to the select
statement in the SP at which point the DR and DT report that no
records are returned. Now I know what you’re thinking “Well duh, your
where clause is eliminating the records”. However the SP, when run in
the EM (SQL Server Management Studio) shows there are results. Both
the EM and the SqlCommand are calling the SP using the same parameters
as verified using a trace.

Below are a few variations of the select statement. What is
interesting is that in all cases the SP returned values in the EM, but
only in the last case did the DR recognize results.

/*
EM Works – DR does not
*/
select ca.SYS_CITY as GROUP_NAME, s.*
from #sales2 s
inner join CUSTOMER_ADDRESSES ca
on s.COMPANY_CODE = ca.COMPANY_CODE
and s.GROUP_FIELD = ca.ADDRESS_CODE
where ca.CUSTOMER_NUMBER = @CustomerNumber
order by s.COMPANY_CODE, s.GROUP_FIELD, s.SA_PERIOD

/*
EM Works – DR does not
For Debugging force the parameter to be a known good value
*/
set @CustomerNumber = ‘300162’

select ca.SYS_CITY as GROUP_NAME, s.*
from #sales2 s
inner join CUSTOMER_ADDRESSES ca
on s.COMPANY_CODE = ca.COMPANY_CODE
and s.GROUP_FIELD = ca.ADDRESS_CODE
where ca.CUSTOMER_NUMBER = @CustomerNumber
order by s.COMPANY_CODE, s.GROUP_FIELD, s.SA_PERIOD

/*
EM Works so does the DR
Hard code the value in the where clause
*/
select ca.SYS_CITY as GROUP_NAME, s.*
from #sales2 s
inner join CUSTOMER_ADDRESSES ca
on s.COMPANY_CODE = ca.COMPANY_CODE
and s.GROUP_FIELD = ca.ADDRESS_CODE
where ca.CUSTOMER_NUMBER = ‘300162’
order by s.COMPANY_CODE, s.GROUP_FIELD, s.SA_PERIOD


Also, yes, SET NOCOUNT ON in included in the procedure.

Any ideas? What am I missing here?

Thanks.
 
Back
Top