CLR TVF Error: Msg 6260, Level 16, State 1, Line 1

Dear All, I always got this error in CLR TVF:

Msg 6260, Level 16, State 1, Line 1
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()

Here is my code:

using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Collections;

public static class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName = "rowfiller",DataAccess=DataAccessKind.Read,TableDefinition = "ActID int, ActName nvarchar(50), ActCreatorID int,ActDesp nvarchar(200),ActCreateDate datetime,ActModifyDate datetime, ActStartDate datetime, ActEndDate datetime, Status int, Cost int")]
public static IEnumerable Func_GetSchCatActivityIDTable(int CatActivityID)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
string sqlstring = "select * from Activity where CatActivityID=@.CatActivityID;";

connection.Open();
SqlCommand command = new SqlCommand(sqlstring, connection);
command.Parameters.AddWithValue("@.CatActivityID", CatActivityID);

return command.ExecuteReader(CommandBehavior.CloseConnection);

}
}

[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft Performance","CA1811:AvoidUncalledPrivateCode")]
public static void rowfiller(Object obj,
out SqlInt32 ActID,
out SqlString ActName,
out SqlInt32 ActCreatorID,
out SqlString ActDesp,
out SqlDateTime ActCreateDate,
out SqlDateTime ActModifyDate,
out SqlDateTime ActStartDate,
out SqlDateTime ActEndDate,
out SqlInt32 Status,
out SqlInt32 Cost,
)
{

SqlDataRecord row = (SqlDataRecord)obj;
int column = 0;

ActID = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
ActName = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++;
ActCreatorID = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
ActDesp = (row.IsDBNull(column)) ? SqlString.Null : new SqlString(row.GetString(column)); column++;
ActCreateDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActModifyDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActStartDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
ActEndDate = (row.IsDBNull(column)) ? SqlDateTime.Null : new SqlDateTime(row.GetDateTime(column)); column++;
Status = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
Cost = (row.IsDBNull(column)) ? SqlInt32.Null : new SqlInt32(row.GetInt32(column)); column++;
}

};

Can anyone tell me what I am doing wrong? Many thanks

.

You can not do data access from a Data Reader in a CLR TVF. Basically, after accessing the first row in the reader the data reader closes down.

Niels
|||Just a correction, as soon as you leave the TVF method the datareader closes down - you won't be able to get even the first row.

Niels
|||

Hi, Niels

Thanks for your responding. Do you have any idea how to get arround this problem, if I do need a table value by query other tables?

|||Use T-SQL!! T-SQL is much, much better than CLR for in-database data access anyway. From your code above I ca not see any reason per se to use CLR (however, there may be more stuff going on than what you show in your code).

Niels

CLR TVF -> Using Datareader...

Hi
I'm trying to create a CLR-TVF which should do some stuff (in my sample it's
just getting the syscolumns name column for the database _ODS).
I’ve got this error:
An error occurred while getting new row from user defined Table Valued
Function :
System.InvalidOperationException: Data access is not allowed in this
context. Either the context is a function or method not marked with
DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain
data from FillRow method of a Table Valued Function, or is a UDT validation
method.
Question: Is it really not possible to make a sql-query within the C#-Part
of the CLR? I know, I could easily do it using a StoredProc…but I need a T
VF.
Thanks for your help…
Here is the code:
[Microsoft.SqlServer.Server.SqlFunction
(FillRowMethodName = "FillRow2",
DataAccess = DataAccessKind.Read,
TableDefinition = "fld_colname NVARCHAR(4000)" )]
public static IEnumerable LoadSysColumns(string str2)
{
return str2;
}
public static void FillRow2(object row,
out string str2)
{
// creating a connection using the current sqlserver-context
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.CommandText = "select NAME from _ODS.dbo.SYSCOLUMNS";
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
sqlDataReader.Read();
str2 = sqlDataReader.GetValue(0).ToString();
}
}"Dominic" <Dominic@.discussions.microsoft.com> wrote in message
news:7638A0AE-0976-4DAC-8512-93FB84C048A4@.microsoft.com...
> Hi
> I'm trying to create a CLR-TVF which should do some stuff (in my sample
> it's
> just getting the syscolumns name column for the database _ODS).
> I've got this error:
> An error occurred while getting new row from user defined Table Valued
> Function :
> System.InvalidOperationException: Data access is not allowed in this
> context. Either the context is a function or method not marked with
> DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain
> data from FillRow method of a Table Valued Function, or is a UDT
> validation
> method.
> Question: Is it really not possible to make a sql-query within the C#-Part
> of the CLR? I know, I could easily do it using a StoredProc.but I need a
> TVF.
> Thanks for your help.
> Here is the code:
> [Microsoft.SqlServer.Server.SqlFunction
> (FillRowMethodName = "FillRow2",
> DataAccess = DataAccessKind.Read,
> TableDefinition = "fld_colname NVARCHAR(4000)" )]
> public static IEnumerable LoadSysColumns(string str2)
> {
> return str2;
> }
> public static void FillRow2(object row,
> out string str2)
> {
> // creating a connection using the current sqlserver-context
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> connection.Open();
> SqlCommand sqlCommand = connection.CreateCommand();
> sqlCommand.CommandText = "select NAME from
> _ODS.dbo.SYSCOLUMNS";
> SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
> sqlDataReader.Read();
> str2 = sqlDataReader.GetValue(0).ToString();
> }
> }
>
Any data access should be in the LoadSysColumns method, not in the
FillRowMethod. In LoadSysColumns just fill a List<string> and return that.
SQL will enumerate it and pass each member to your FillRowMethod, EG:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction
(FillRowMethodName = "FillRow2",
DataAccess = DataAccessKind.None,
SystemDataAccess = SystemDataAccessKind.Read,
TableDefinition = "fld_colname NVARCHAR(4000)")]
public static IEnumerable LoadSysColumns()
{
List<string> names = new List<string>();
using (SqlConnection connection = new SqlConnection("context
connection=true"))
{
connection.Open();
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.CommandText = "select NAME from dbo.SYSCOLUMNS";
using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
while (sqlDataReader.Read())
{
names.Add(sqlDataReader.GetValue(0).ToString());
}
}
return names;
}
public static void FillRow2(object row, out string str2)
{
str2 = (string)row;
}
};
David|||Thanks David...works fine!
"David Browne" wrote:

> "Dominic" <Dominic@.discussions.microsoft.com> wrote in message
> news:7638A0AE-0976-4DAC-8512-93FB84C048A4@.microsoft.com...
> Any data access should be in the LoadSysColumns method, not in the
> FillRowMethod. In LoadSysColumns just fill a List<string> and return that
.
> SQL will enumerate it and pass each member to your FillRowMethod, EG:
>
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
> using System.Collections;
> using System.Collections.Generic;
> public partial class UserDefinedFunctions
> {
> [Microsoft.SqlServer.Server.SqlFunction
> (FillRowMethodName = "FillRow2",
> DataAccess = DataAccessKind.None,
> SystemDataAccess = SystemDataAccessKind.Read,
> TableDefinition = "fld_colname NVARCHAR(4000)")]
> public static IEnumerable LoadSysColumns()
> {
> List<string> names = new List<string>();
> using (SqlConnection connection = new SqlConnection("context
> connection=true"))
> {
> connection.Open();
> SqlCommand sqlCommand = connection.CreateCommand();
> sqlCommand.CommandText = "select NAME from dbo.SYSCOLUMNS";
> using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader())
> while (sqlDataReader.Read())
> {
> names.Add(sqlDataReader.GetValue(0).ToString());
> }
> }
> return names;
> }
> public static void FillRow2(object row, out string str2)
> {
> str2 = (string)row;
> }
> };
>
> David
>
>

CLR Table Value Function Insert Into Table Variable

I have a simple clr tvf that splits a string and returns a two column table. When I try and insert the results of this tvf into another table variable I get the error below. Can anyone help me on this one? What a huge letdown if clr tvf cannot be insert into table variables. Inserting into a temp table works fine.

-- BTW I am on the September CTP with VS 2005 RC

Thanks,
Adam

Error Message

Msg 8624, Level 16, State 1, Line 2

Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.

Below is the SQL I am using to test

declare @.t table(a int, b nvarchar(128))
insert into @.t
select * from dbo.Split('Hello,GoodBye', ',', 1)

Function Definition

CREATE FUNCTION [dbo].[Split](@.value [nvarchar](4000), @.seperator [nvarchar](32) = N',', @.removeEmptyEntries [bit] = 1)
RETURNS TABLE ([Position] [int] NULL,[Value] [nvarchar](4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Sit.Sql.Cdw].[Sit.Sql.Cdw.Split].[InitMethod]
GO

Source Code

using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;

namespace Sit.Sql.Cdw
{
public class Split
{
public struct Splitter
{
public int pos;
public String value;
}

[SqlFunction(FillRowMethodName= "FillRow", Name="Split", TableDefinition="Position int, Value nvarchar(1000)")]
public static IEnumerable InitMethod(String value, String seperator, bool removeEmptyEntries)
{
Splitter[] sVals;
String[] vals = value.Split( new string[1] { seperator }, (removeEmptyEntries) ?
StringSplitOptions.RemoveEmptyEntries : StringSplitOptions.None);
sVals = new Splitter[vals.Length];
for(int i = 0; i < vals.Length; i++)
{
sVals.pos = i + 1;
sVals.value = vals;
}
return sVals;
}

public static void FillRow(Object obj, out SqlInt32 Position, out SqlChars Value)
{
Splitter s = (Splitter)obj;
Position = new SqlInt32(s.pos);
Value = new SqlChars(s.value);
}
}
}

I just realized my mistake after posting...
I originally called the function with default parameters dbo.Split('Hello,Goodbye', default, default) which produces the error. Substituting the default parameter with an actual value runs fine.

CLR memory usage

Hi All -

We have some CLR sprocs and tvf's we run in a batch job that recently have been getting the out of memory issue. I want to increase the amount of memory allocated to the CLR using the -g startup switch but i want to make an intelligent decision on how much to allocate. What are some of the best ways that you have found to estimate how much to give the CLR?

Thanks,

Cameron

The first step in this process is to look at your CLR sprocs and TVFs and look at how they allocate memory. Are they doing things like filling datasets with potentially large amounts of data? Are they using readers, but constructing lots of objects that grow in proportion to the query size against the database? If so, there may be a fundemental design problem that needs to be addressed. If that isn't the case, then looking at how memory is used in your CLR sprocs and TVFs should begin to help you understand what new memory limits to apply. Only by understanding where and when and how much memory is allocated for your worst case workload can you begin to understand how to change the limits.