A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Saturday, May 29, 2010

TVP decimal values appears as integer in Profiler

This could be just me doing something wrong, but it sure looks like there's something weird going on with invoking TVPs (table valued parameters) from .NET code where there are decimal data types and how profiler is listening to the call. I have no idea honestly, but I wanted to get this example posted so I can enlist my mighty twitter-brethren. Assume you have the following user-defined table type in SQL Server 2008, R2 and a procedure that consumes the that type as a TVP
CREATE TYPE 
    dbo.TEST_TYPE AS TABLE
(
    some_rate decimal(5, 4) NULL
)
GO
CREATE PROCEDURE 
    dbo.TVPTest
(
    @tvp dbo.TEST_TYPE READONLY
) 
AS 
BEGIN
    SET NOCOUNT ON 
    SELECT 
        T.* 
    FROM 
        @tvp T
END
This section of C# shows an example of how to pass a dataset into the proc
/// <summary>
/// A minimal reproduction of the pain I do not want to have
/// </summary>
public static void TVPTest()
{
    string connectionString = @"Data Source=localhost;Initial Catalog=master;Integrated Security=True";
    System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString);
    System.Data.DataSet dataSet = null;
    System.Data.DataTable dataTable = null;

    decimal someRate = 0.0m;

    // Spin up our dataset
    dataSet = new DataSet("repro");
    dataTable = new DataTable("TEST_TYPE");
    dataTable.Columns.Add("some_rate", System.Type.GetType("System.Decimal"));

    // Add some values
    someRate = 0.1000m;
    dataTable.Rows.Add(new object[] { someRate });
    someRate = 0.0200m;
    dataTable.Rows.Add(new object[] { someRate });
    someRate = 0.0030m;
    dataTable.Rows.Add(new object[] { someRate });
    someRate = 0.0004m;
    dataTable.Rows.Add(new object[] { someRate });
    someRate = 0.0096m;
    dataTable.Rows.Add(new object[] { someRate });
    someRate = 1.0096m;
    dataTable.Rows.Add(new object[] { someRate });

    System.Data.SqlClient.SqlConnection connection = null;
    System.Data.DataSet messages = null;
    System.Data.SqlClient.SqlCommand command = null;
    System.Data.SqlClient.SqlDataReader dataReader = null;
    connection = new System.Data.SqlClient.SqlConnection(connectionString);
    try
    {
        connection.Open();

        command = new System.Data.SqlClient.SqlCommand("TVPTest");
        command.CommandType = System.Data.CommandType.StoredProcedure;
        command.Connection = connection;

        System.Data.SqlClient.SqlParameter tvp = command.Parameters.AddWithValue("@tvp", dataTable);
        tvp.SqlDbType = System.Data.SqlDbType.Structured;
        tvp.TypeName = "dbo.TEST_TYPE";

        dataReader = command.ExecuteReader();
        if (dataReader.HasRows)
        {
            messages = new System.Data.DataSet();
            messages.Tables.Add();
            messages.Tables[0].Load(dataReader);
            PPrint(messages);
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex);
    }
}

/// <summary>
/// Pretty print a dataset
/// </summary>
/// <param name="ds">dataset that should be rendered to console</param>
public static void PPrint(System.Data.DataSet ds)
{
    System.Data.DataTable dt = null;
    if (ds == null)
    {
        Console.WriteLine("Dataset is null, n00b");
        return;
    }

    for (int tableIndex = 0; tableIndex < ds.Tables.Count; tableIndex++)
    {
        dt = ds.Tables[tableIndex];

        // Determine header
        // Console.WriteLine(dt.Namespace);
        Console.WriteLine("==========================================");
        Console.WriteLine(dt.TableName);
        Console.WriteLine("==========================================");

        foreach (System.Data.DataColumn dc in dt.Columns)
        {
            System.Console.Write(string.Format("{0}\t", dc.ColumnName));
        }

        Console.WriteLine();

        foreach (System.Data.DataRow row in dt.Rows)
        {
            for (int columnIndex = 0; columnIndex < dt.Columns.Count; columnIndex++)
            {
                Console.Write(string.Format("{0}\t", row[columnIndex]));
            }

            Console.Write("\n");
        }

        Console.WriteLine("{0} rows in table", dt.Rows.Count);
        Console.WriteLine("\n\n");
    }
}
Results of running that are
==========================================
Table1
==========================================
some_rate
0.1000
0.0200
0.0030
0.0004
0.0096
1.0096
6 rows in table
Completely unremarkable. Where it gets interesting is when profiler is turned on. Doing a TSQL trace, the TextData that is captured says this statement is what SQL Server is doing when the call to command.ExecuteReader is made
declare @p1 dbo.TEST_TYPE
insert into @p1 values(0.1000)
insert into @p1 values(200)
insert into @p1 values(30)
insert into @p1 values(4)
insert into @p1 values(96)
insert into @p1 values(1.0096)

exec TVPTest @tvp=@p1
That statement, copy and pasted into SSMS will generate these errors
(1 row(s) affected)
Msg 8115, Level 16, State 8, Line 4
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.
Msg 8115, Level 16, State 8, Line 5
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.

(1 row(s) affected)
Msg 8115, Level 16, State 8, Line 7
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.

(1 row(s) affected)
The middle values which were all decimals when passed to passed to SQL Server show as integers values in SQL Profiler, at least as far as my profiler was concerned. What's really queer about it is that it didn't truncate the values which I'd have expected---as far as one can have expectations for abnormal behaviour. Instead, profiler just disregarded the decimal point. To generalize the behaviour, decimal values with leading-zeros may show up as integers in a SQL trace for calls to table valued parameters. I don't have a non-R2 image to test against and maybe I'm just not well-versed enough in the intracies of Profiler but it smells like a bug to me. As always, comments welcomed
SELECT
    SERVERPROPERTY('productversion') AS product_version
,   SERVERPROPERTY ('productlevel') AS product_level
,   SERVERPROPERTY ('edition') AS edition
product versionproduct leveledition
10.50.1600.1RTMDeveloper Edition (64-bit)

No comments: