Thursday, January 14, 2010

Bug in SQL Profiler when working Table-Valued Parameters and DATETIME

Just provided a new bug note at Microsoft® SQL Server Connect:
Profiler shows DATETIME for TVP as DATETIME2

If you work with table-valued types and table-valued parameters provided by a front-end application you get an invalid SQL Profiler output for DATETIME columns. Profiler shows DATETIME column inserts as DATETIME2 values (like '2010-01-14 18:38:09.2100000'). The problem with this output are the nano-second digits which cannot be converted to DATETIME.

For anybody who is interested, here a short description how to reproduce.

Create a new table-valued type in SQL Server.

And a little C# application that uses this table-valued type by executing an SqlCommand.
class TestEnumerator : IEnumerable {
   public IEnumerator GetEnumerator() {
      // create meta data for the table-valued parameter
      SqlMetaData[] meta = new SqlMetaData[] {
         new SqlMetaData("Dt", SqlDbType.DateTime)

      // create a single record to be returned as enumerator
      SqlDataRecord record = new SqlDataRecord(meta);
      record.SetDateTime(0, DateTime.Now);
      // return the record as enumerator value
      yield return record;

   IEnumerator IEnumerable.GetEnumerator() {
      return this.GetEnumerator();
class Program {
   static void Main(string[] args) {
      string cnStr = Properties.Settings.Default.SandboxConnectionString;

      // connect to the database and create a new sql command that simply 
      // returns the count of rows within a table-valued parameter
      using (SqlConnection cn = new SqlConnection(cnStr))
      using (SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM @t", cn)) {

         // create and configure the table-valued parameter
         SqlParameter p = new SqlParameter("@t", SqlDbType.Structured);
         // specify the table-valued type
         p.TypeName = "FooType";
         p.Value = new TestEnumerator();

         // execute the command
         object result = cmd.ExecuteScalar();

Now start SQL Profiler and enable the "RPC:Completed" event. Start the C# application and you will get the following trace output:
declare @p3 dbo.FooType
insert into @p3 values('2010-01-14 18:38:09.2100000')

exec sp_executesql N'SELECT COUNT(*) FROM @t',N'@t FooType READONLY',@t=@p3

The error occurs if you try to run this SQL in SSMS for debugging.
Msg 241, Level 16, State 1, Line 7
Conversion failed when converting date and/or time from character string.

1 comment:

  1. This is Excelent, good artical
    But i am getting Error
    ERROR: No mapping exists from DbType 30 to a known SqlDbType.
    in my code
    My VB Code:
    dbCommand = objdbFactory.GetStoredProcCommand("AddSqc")
    objdbFactory.AddInParameter(dbCommand, "@SqcTable", SqlDbType.Structured, dsFileName) '' @SqcTableis User Defined Table

    so I did research on web then I found solution
    You can also fine more information about Table valued parameters in Enterprise Liberary