CLR first time use due to unused variable

I have a situation where a completely unused variable causes an error. Deleting an unused variable declaration resolves the error. After cleaning, the error remains cleared (even if the unused variable is restored) until SQL Server restarts, after which it returns.

I defined a CLR function and a UDF table value. I can call the CLR using this test code:

Declare @name_does_not_matter Table (any_column_name int);

Select dbo.MapTesseractUris(
    N'<a href="tesseract:2">map this uri</a>',
    N'localhost:6313');

This results in an error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "MapTesseractUris": 
System.Data.SqlClient.SqlException: 
System.Data.SqlClient.SqlException: 
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlCommand.SetUpSmiRequest(SqlInternalConnectionSmi innerConnection)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderSmi(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at Tesseract.Database.TesseractUri.MapPageIdsToUrls(IEnumerable`1 pageIds, String forDomain)
   at Tesseract.Database.TesseractUri.MapTesseractUris(String html, String forDomain)

The error occurs at the point in my code where the CLR calls UDF. Calling UDF does not directly result in an error.

However, if I instead call the CLR using this code, I get the correct expected result:

Select dbo.MapTesseractUris(
    N'<a href="tesseract:2">map this uri</a>',
    N'localhost:6313');

, . Declare . .

, SQL- .

, @name_does_not_matter . , (Declare @name_does_not_matter int ).

?

, "" SQL SQL Server?

Tesseract.Database.TesseractUri.MapPageIdsToUrls:

private static IDictionary<int, string> MapPageIdsToUrls(
    IEnumerable<int> pageIds,
    string forDomain)
{
    using (var db = new SqlConnection("context connection=true")) {
        db.Open();

        using (var cmd = db.CreateCommand()) {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = @"
                Select [Id], [Url]
                From [dbo].[ResolvePageUrls](@pageIds, @domain)";

            var p = cmd.Parameters.AddWithValue(
                "@pageIds",
                CreateDataRecords(pageIds));

            p.SqlDbType = SqlDbType.Structured;
            p.TypeName = "dbo.IntTable";

            cmd.Parameters
                .Add("@domain", SqlDbType.NVarChar, 252)
                .Value = forDomain;

            using (var reader = cmd.ExecuteReader()) {
                return reader.OfType<IDataRecord>()
                    .Select(row => Tuple.Create(
                        row.GetInt32(0),
                        row.GetValue(1) as string))
                    .ToDictionary(t => t.Item1, t => t.Item2);
            }
        }
    }
}

private static IEnumerable<SqlDataRecord> CreateDataRecords(
    IEnumerable<int> ids)
{
    var metaData = new[] { new SqlMetaData("Value", SqlDbType.Int) };
    var record = new SqlDataRecord(metaData);
    foreach (var id in ids.Distinct()) {
        record.SetInt32(0, id);
        yield return record;
    }
}
+3

All Articles