SqlDataReader and SqlDataAdapter in CLR stored procedure only return first row

Question

I have a CLR stored procedure running on SQL Server 2014. When I execute the following code, the data reader only returns the top row of the result set. The SQL, when ran by itself, returns more than one row. I have also tried filling a DataTable with the SqlDataAdapter, but still only get one row.

using (SqlConnection conn = new SqlConnection("context connection=true"))
using (SqlCommand cmd = new SqlCommand("SELECT * FROM some_table", conn))
{
    cmd.CommandType = CommandType.Text;
    conn.Open();

    SqlDataReader reader = cmd.ExecuteReader();

    if (reader.HasRows)
    {
        while (reader.Read())
            SqlContext.Pipe.Send(reader.GetInt32(0).ToString());
    }

    reader.Close();
    conn.Close();
}

Thank you for any help in advance. This truly has me baffled, as it is the simplest of things.

Your code looks right. I'm guessing when you made the example you removed the bug. One thing I'd say though is that you shouldn't need to check HasRows. — Paul Mendoza
– Paul Mendoza, Commented Nov 16, 2016 at 0:03
@EvilAugust I just tried this code on SQL Server 2014, .NET 4.5.2, and it works as you are expecting. Is the code you posted above the exact code that you are running? Are you sure that you are publishing to the same DB (and Instance) that you are running the SELECT statement in? Are you sure that you have published the most recent version of this code to the DB? — Solomon Rutzky
– Solomon Rutzky, Commented Nov 16, 2016 at 0:40
Only difference would be mine is most likely 4.5, but we are talking SqlDataReader here, so it shouldnt matter, but I'll give it a try compiled as 4.5.2. Another oddity is that the table doesn't have a primary key, and can't be given one. — Evil August
– Evil August, Commented Nov 16, 2016 at 1:05
@EvilAugust No, using .NET 4.5 shouldn't be an issue at all. And neither would the lack of a Primary Key. I even just tested again without a PK or clustered index and it works as expected. There is an option when calling SqlDataReader to pass in CommandBehavior.SingleRow, which I tried, but it still returned all rows. This is why I am suggesting to double-check that you did indeed publish this code, and that the code is in the same DB that you are in when you run the same SELECT *. Also, be sure to schema-qualify the table name, just to be sure. — Solomon Rutzky
– Solomon Rutzky, Commented Nov 16, 2016 at 3:58

Solomon Rutzky · Accepted Answer · 2016-11-17 03:17:32Z

1

There is nothing inherently wrong with this code. I ran it myself on SQL Server 2014, compiled against .NET Framework version 4.5.2 and it works as you are expecting it to. And just to be clear, for what you are doing here, the version of SQL Server and .NET Framework don't really matter.

I also tried passing in CommandBehavior.SingleRow to cmd.ExecuteReader() and that still returned all rows.

Since this code does work, here are some things to check:

Make sure that you are publishing to the same DB (and Instance) that you are running the SELECT statement in in SSMS.
Make sure that you have published the most recent version of this code to the DB.
Other external factors

Also, please create the SqlDataReader within a using() construct as it is a Disposable object.

UPDATE FROM O.P.:

The SQLCLR Stored Procedure is being called from a T-SQL Stored Procedure that had issued SET ROWCOUNT 1; prior to calling the SQLCLR Stored Procedure. Hence, only 1 row could be returned by any query. The 1 row problem was fixed by issuing SET ROWCOUNT 0; prior to calling the SQLCLR Stored Procedure.

Please note: it is generally preferred to use the TOP () clause instead of SET ROWCOUNT n;.

edited Nov 17, 2016 at 3:17

answered Nov 16, 2016 at 16:39

Solomon Rutzky

49.3k11 gold badges141 silver badges185 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Evil August Over a year ago

This falls under other external factors. As mentioned in my previous comment, the CLR was being called by a stored procedure that had the row count set to 1, and therefore was in that context. Setting row count back to 0 before calling the CLR fixed the problem. Thank you for your help.

Solomon Rutzky Over a year ago

@EvilAugust Understood. I just wanted this documented since the code was fine, and your finding confirms my advice to look elsewhere. I have updated my answer to include the specifics of what the external factor was.

Hugh · Accepted Answer · 2017-05-22 20:52:31Z

Beware! Microsoft have a bug here:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/83b23289-a24b-4f82-b81f-3e8ccf6d8001/is-this-serious-sqlclr-bug-present-in-sql-server-2012?forum=sqlnetfx

The SqlDataReader class actually briefly grabs a lock yet it should not because SQL CLR approved code should never take locks.

I submitted a sample to MS that proved this, a very simple loop using only approved SQL/CLR classes/methods would cause an AppDomain unload when an SSMS user hits cancel - totally catastrophic.

Here's the reference source, take a look and you'll find "lock" statements - illegal for SQL/CLR yet they approved this class!

https://referencesource.microsoft.com/#system.data/system/Data/SqlClient/SqlDataReader.cs

This says/implies its approved:

https://msdn.microsoft.com/en-us/library/ms131094.aspx

Yet this fails to include it on the most recent approved list:

https://learn.microsoft.com/en-us/sql/relational-databases/clr-integration/database-objects/supported-net-framework-libraries

(Oddly OracleClient is on the list !)

Collectives™ on Stack Overflow

SqlDataReader and SqlDataAdapter in CLR stored procedure only return first row

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related