CLR Table Function with null values

Good Night!

Please I'm trying to do a Clr function wiht null values but I have an error. What am I doing wrong?

My Clr is like this:

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

public partial class UserDefinedFunctions
{
    private class Function
    {
        public SqlInt16 First;
        public SqlString Second;
        public Function
        (
            SqlInt16 first,
            SqlString second
        )
        {
            First = first;
            Second = second;
        }
    }

    [SqlFunction
        (
            DataAccess = DataAccessKind.Read,
            SystemDataAccess = SystemDataAccessKind.Read,
            FillRowMethodName = "Fill",
            TableDefinition =
                "[first] [SMALLINT] NULL," + "\r\n" +
                "[second] [NVARCHAR](4000) NULL" + "\r\n"
        )
    ]

    public static IEnumerable function()
    {
        ArrayList resultSet = new ArrayList();
        SqlCommand Command = new SqlCommand();
        Command.CommandType = CommandType.Text;
        StringBuilder sb = new StringBuilder();
        sb.AppendLine("SELECT NULL, N'A';");
        Command.CommandText = sb.ToString();
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            Command.Connection = conn;
            conn.Open();
            using (SqlDataReader Read = Command.ExecuteReader())
            {
                while (Read.Read())
                {
                    SqlInt16 first = Read.GetSqlInt16(0);
                    SqlString second = Read.GetSqlString(1);
                    resultSet.Add
                    (
                        new Function
                        (
                            first,
                            second
                        )
                    );
                }
            }
        }
        return resultSet;
    }

    public static void Fill
    (
        object functionObj,
        out SqlInt16 first,
        out SqlString second
    )
    {
        Function function = (Function)functionObj;
        if (function.First.IsNull)
        {
            first = SqlInt16.Null;
        }
        else
        {
            first = function.First;
        }
        second = function.Second;
    }
};

I implement and execute it it in Sql like this:

CREATE ASSEMBLY
 [function]
FROM
 N'X:\Directory\function.dll'
WITH PERMISSION_SET = SAFE
GO

CREATE FUNCTION [dbo].[function]()
RETURNS TABLE
(
 [first] [SMALLINT] NULL,
 [second] [NVARCHAR](4000) NULL
)
AS EXTERNAL NAME [function].[UserDefinedFunctions].[function]
GO

SELECT
 *
FROM
 [dbo].[function]()
GO

And I have the next error message:

Mens. 6522, Nivel 16, Estado 1, Lnea 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "function":
System.InvalidCastException: La conversin especificada no es vlida.
System.InvalidCastException:
   en Microsoft.SqlServer.Server.ValueUtilsSmi.GetSqlInt16(SmiEventSink_Default sink, ITypedGettersV3 getters, Int32 ordinal, SmiMetaData metaData)
   en UserDefinedFunctions.function()

May anybody tell me what I'm doing wrong?

Thanks a lot for your valuable help. I'm trying to learn .Net in Sql and those are my basic steps.

Regards

Ja

June 23rd, 2015 1:15am

Did you run this through the debugger and see which statement was getting the error and what the variable values were at that time?

Cheers, Bob

Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2015 1:40pm

Good afternoon Bob!

Thanks a lot for your reply.

That is a CLR Function and I don't know how to debug that. The only thing I did was edit the test file and run from there and then I see the error, but I don't know how to debug step by estep from CLR into Visual Studio, because it doesn't send me to de .cs file to debug line by line.

If you can tell me how It'd be apreciated for me. I send all code if not.

Regards,

Jamesit0.

June 23rd, 2015 3:52pm

If you're learning .NET in SQL, how to debug it is a good thing to know, else you'll be spending a lot of time staring at lots of code and trying to figure out where the problem is. The more lines of code you write, the more impossible this becomes.

There's a lot of information with a simple web search ("SQLCLR and debugging") about how to debug SQLCLR code in Visual Studio. It's useful to have it running on an instance of SQL Server on your own machine, else there's some additional setup to do and high permissions you need to have. Once you have that, the two easiest ways I've found are: in VS Server Explorer, connect to the SQL Server on your machine, find browse to the Stored Procedures folder, right-click on your stored procedure and select "Step Into" or "Debug".

Depending on the version of VS and of SQL Server you have, this works. 

If all else fails, make a simple project (.NET library) with your code in it. Then under the Run menu (I think, don't have VS in front of me), there's a "Debug A Process" entry. On the dialog that appears, check "show processes from all users" and select the SQLSERVR.EXE  as the process to debug. Make sure .NET is checked as a debugging option for this process. Then set a breakpoint in your code in VS and run some SQL that calls your stored procedure from SQL Server Management Studio. It should stop at the breakpoint. If it doesn't, run the SSMS calling statement again (sometimes it takes running it twice for it to work).

And if that's too complex (it can be confusing because VS support for SQLCLR has "evolved" over releases), you can resort to "printf debugging" using SqlContext.Pipe.Send("any string you want") at various points in the code, even using formatted strings to list variable values. When you run your calling code in SQL Server Management Studio, these appear on the "messages" pane.

Hope this gets you started, Bob

 



Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2015 4:16pm

You need this in the SELECT statement:

     sb.AppendLine("SELECT cast(NULL AS smallint), N'A';");

Else the type of the NULL will not the one that GetSqlInt16 expects.

June 23rd, 2015 5:23pm

Good night!

Thanks a lot for both answers!

Erland's answer worked for me. Fantastic!

I'll study Bob answer on weekend.

Regards,

Jamesit0.

Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2015 9:13pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics