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.
CREATE TYPE FooType AS TABLE ( Dt DATETIME );
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)) { cn.Open(); // 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(); cmd.Parameters.Add(p); // 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.
This is Excelent, good artical
ReplyDeleteBut 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
http://entlib.codeplex.com/discussions/215754