context connection in CLR table valued functions

22 Jun 2006 9:15 PM

Sascha

I have to filter cells of a table, i.e. replacing individual table cells with
nulls. In a stored procedure I could use the following code snippet, where
the filtering is done by SendThisRow() and
BuildSqlDataRecordFromFilteredIDataRecord().

      using(SqlConnection connection = new SqlConnection("context
connection=true"))
      {
         connection.Open();
         SqlCommand command = new SqlCommand("SELECT x,y FROM MyTable",
connection);
         SqlDataReader reader = command.ExecuteReader();

    foreach (IDataRecord row in reader)
    {
        if (SendThisRow(row))
        {
            SqlContext.Pipe.Send( BuildSqlDataRecordFromFilteredIDataRecord(row))
        }
    }
      }

Is there a way to do the same with a table valued function? As far as I know
the context connection can only be used in the init method of the tvf. But

with that I have to store the whole result table in (managed) memory. I've
tried to use the Enumerator returned by SqlDataReader to enumerate the
table's rows. But with that I always get the folling error as expected

Msg 6260, Level 16, State 1, Line 2
An error occurred while getting new row from user defined Table Valued
Function :
System.InvalidOperationException: Invalid attempt to FieldCount when reader
is closed.
System.InvalidOperationException:
   at System.Data.SqlClient.SqlDataReaderSmi.get_FieldCount()
   at System.Data.Common.DbEnumerator.BuildSchemaInfo()
   at System.Data.Common.DbEnumerator.MoveNext()
   at MyTVF.RowSetEnumerator.MoveNext()

Any idea how to stream rows return by a SqlDataReader on the context
connection by a table valued function?

Thanks,

Sascha

23 Jun 2006 6:48 PM

Steven Hemingray [MSFT]

Unfortunately it is not possible to access the Context Connection from
within a TVF's FillRow method, as you have found. You can however use an
external, non-context connection if that would work in your scenario and it
may still provide an improvement over using the SqlContext.Pipe.Send method.
Adam Machanic had a similar scenario where he encountered this same
limitation in his blog post here:
http://sqljunkies.com/WebLog/amachanic/archive/2006/02/28/18309.aspx, and
using the external connection proved faster than Pipe.Send.

There is also a MS Connect Product Feedback item on this issue here
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126638

Supporting these streaming, data-reading TVFs is definitely something under
consideration for future releases.

Steven

Show quote

"Sascha" <Sas***@discussions.microsoft.com> wrote in message
news:38DEA264-526E-40C1-BD6F-BB70423921F0@microsoft.com...
>I have to filter cells of a table, i.e. replacing individual table cells
>with
> nulls. In a stored procedure I could use the following code snippet, where
> the filtering is done by SendThisRow() and
> BuildSqlDataRecordFromFilteredIDataRecord().
>
>      using(SqlConnection connection = new SqlConnection("context
> connection=true"))
>      {
>         connection.Open();
>         SqlCommand command = new SqlCommand("SELECT x,y FROM MyTable",
> connection);
>         SqlDataReader reader = command.ExecuteReader();
>
> foreach (IDataRecord row in reader)
> {
> if (SendThisRow(row))
> {
> SqlContext.Pipe.Send( BuildSqlDataRecordFromFilteredIDataRecord(row))
> }
> }
>      }
>
>
> Is there a way to do the same with a table valued function? As far as I
> know
> the context connection can only be used in the init method of the tvf. But
>
> with that I have to store the whole result table in (managed) memory. I've
> tried to use the Enumerator returned by SqlDataReader to enumerate the
> table's rows. But with that I always get the folling error as expected
>
> Msg 6260, Level 16, State 1, Line 2
> An error occurred while getting new row from user defined Table Valued
> Function :
> System.InvalidOperationException: Invalid attempt to FieldCount when
> reader
> is closed.
> System.InvalidOperationException:
>   at System.Data.SqlClient.SqlDataReaderSmi.get_FieldCount()
>   at System.Data.Common.DbEnumerator.BuildSchemaInfo()
>   at System.Data.Common.DbEnumerator.MoveNext()
>   at MyTVF.RowSetEnumerator.MoveNext()
>
> Any idea how to stream rows return by a SqlDataReader on the context
> connection by a table valued function?
>
> Thanks,
>
> Sascha