none
Severe Error in current command during DataReader.Read() RRS feed

  • Question

  •  

    Hello,

     

    I am getting a problem reading the results of a stored procedure with a DataReader. During the final attempt to call Read (when it should return false) I get a System.Data.SqlClient.SqlException (A severe error occurred on the current command.) The Stored Proc executes ok in  SQL Management Studio.

     

    Here is the Stack Trace:

     

    Stack Trace:

     
    [SqlException (0x80131904): A severe error occurred on the current command.  The results, if any, should be discarded.]
       System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +862234
       System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739110
       System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
       System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
       System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
       System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214
       System.Data.SqlClient.SqlDataReader.Read() +9
       DataAccess.DataModelBuilder`1.BuildAll(SqlDataReader reader) in C:\Clients\DataAccess\DataModelBuilder.cs:89
    ..... 


     

    Here is a sample of the code that I calling the Datareader.
     

    public List<Model> BuildAll(SqlDataReader reader)

    {

    List<Model> returnList = new List<Model>();

    while (reader.Read())

    {

    Model obj = new Model();

    //

    //  populate the Model obj

    //

    returnList.Add(obj);

    }

    return returnList;

    }

     

    Friday, November 2, 2007 10:07 AM

Answers

  • Have you tried any other workarounds? I would suggest loading the reader into a DataTable and then iterating through the DataTable as a quick workaround. If the error persists when you call the DataTable.Load() method - then your problem is definitely not in your local application code. You might then want to investigate your SQL Server a little more. If it does work, then something's wrong with your application's code. Refactor until you get it right, or leave what I have below.

    I'd do the following:

    public List<Model> BuildAll(SqlDataReader reader)

    {

    List<Model> returnList = new List<Model>();

    DataTable table = new DataTable();

    table.Load(reader);


    foreach(DataRow row in table.Rows)

    {

    Model obj = new Model();

    //

    //  populate the Model obj

    //

    returnList.Add(obj);

    }

    return returnList;

    }



    Tuesday, November 20, 2007 11:50 PM

All replies

  • Do you have PRINT statements inside of your stored procedure? If yes, try to comment them out. Aslo try to add SET NOCOUNT ON at the beginning of SP

    Friday, November 2, 2007 10:27 AM
    Moderator
  • here is my SQL PROC

     

    ALTER PROCEDURE [spr_GetContentByContent]

    @ProgrammeId uniqueidentifier

    AS

    BEGIN

    SET NOCOUNT ON

    SELECT [content_id]

    ,[content_title]

    ,[short_description]

    ,[long_description]

    ,[culture_code]

    ,[programme_id]

    ,[default_content_category_id]

    FROM [Content]

    WHERE programme_id = @ProgrammeId

    SET NOCOUNT OFF

    END

     

    Its nothing very special... Smile
    Friday, November 2, 2007 10:49 AM
  • Please check next KB articel. See if it helps

     

    http://support.microsoft.com/default.aspx?scid=kb;en-us;827366

    Monday, November 5, 2007 11:01 AM
    Moderator
  • I checked and I don't think thats it. My only param is a unique identifier, I am setting a size of 16 (which I think is correct) and I have used them successfully elsewhere.

     

    In addition to that sql server doesn't have any errors in the SqlServer error log. I expected to see a "Invalid buffer .." message, but there isn't one. If fact there is no error message in the log at all.

     

    I am creating a new instance of SqlCommand and SqlConnection before calling ExecuteReader as well.

     

    Further investigation has show that its not always the last row but sometimes it is part way through the results, so only 11 or 12 are returned (there should always be 23)

     

    Help please, I am very confused!

     

    Chris

     

    Monday, November 19, 2007 4:37 PM
  • If I don't return the fields short-description or long_description from the stored proc then it works.

    They are nvarchar(500) and nvarchar(4000) respectively.

     

    Monday, November 19, 2007 5:26 PM
  • Try to move those two fields to the end of the list of selection, like

     

    SELECT [content_id],

    [content_title],

    [culture_code],

    [programme_id],

    [default_content_category_id],

    [short_description],

    [long_description]

    FROM [Content] .....

     

    Does it work in this case?

     

    Tuesday, November 20, 2007 11:11 AM
    Moderator
  • No it didn't make a difference. I found that I have Full-Text catalog on this table, that I don't have on any others. Is there any known issues with Full-text?

     

    The only time i can get it to consistantly work is by using the following SQL:

     

    SELECT [content_id]

    ,[content_title]

    ,[culture_code]

    ,[programme_id]

    ,[default_content_category_id]

    ,LEFT([short_description],180) as [short_description]

    ,LEFT([long_description],0) as [long_description]

    FROM [Content]

    WHERE programme_id = @ProgrammeId

     

    1 more character and I get "severe errors", how can a single character cause that many problems?

     

    Is this some weird buffer problem?

     

    Tuesday, November 20, 2007 12:48 PM
  • Have you tried any other workarounds? I would suggest loading the reader into a DataTable and then iterating through the DataTable as a quick workaround. If the error persists when you call the DataTable.Load() method - then your problem is definitely not in your local application code. You might then want to investigate your SQL Server a little more. If it does work, then something's wrong with your application's code. Refactor until you get it right, or leave what I have below.

    I'd do the following:

    public List<Model> BuildAll(SqlDataReader reader)

    {

    List<Model> returnList = new List<Model>();

    DataTable table = new DataTable();

    table.Load(reader);


    foreach(DataRow row in table.Rows)

    {

    Model obj = new Model();

    //

    //  populate the Model obj

    //

    returnList.Add(obj);

    }

    return returnList;

    }



    Tuesday, November 20, 2007 11:50 PM