sp_helptext output being dropped by ADO.NET ?

Question

Hi all,

I'm using the tool described here to clean up some cruft from our development DBs. I've encountered an odd issue with it and can't find the cause.

For certain stored procedures, the GetProcedureText method (l:47, Validator.cs) fails to return the proc text, causing the program to behave unexpectedly. When debugging and stepping into GetProcedureText it becomes clear that the SqlDataReader returned by the call to ExecuteReader on the SqlCommand does not contain a result set. The question is why..

Running exec sp_helptext <proc name> from SSMS does return the proc text as expected. The problem occurs with the same procs every time. Below is one of the procs exhibiting this issue, though the problem does seem to be with ADO.NET rather than SQL Server.

USE [IL_Party_DB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DeathCause_SelectByDeathCauseCode]
    @deathCauseCode [varchar](15)
AS
SELECT
    death_cause_code,
    description
FROM ilpr_Death_Cause
WHERE
    death_cause_code = @deathCauseCode

I'm using SQL Server 2005. Any ideas?

Answer 1

I ran into the same problem. In my case, it seemed to occur when there was an error in the object's SQL, such as a renamed column or table. It would return an empty result, then all subsequent objects would return an empty result. I had to create a new SqlConnection for each database object to get it to work properly. Not sure what the underlying cause is, but this worked for me...