We have seen that when we use a sql server 2008 profiler trace to trace RPC calls coming from an ADO.Net application and if the ADO.Net applications calls a stored procedure which accepts the parameters of datatype “datetime” which has a precision of 3.333ms, the same will be shown in the sql server 2008 profiler trace with precision of 3.3333333.

 

Consider the following stored procedure defined in the database

 

CREATE PROCEDURE [dbo].[MyProc] @var datetime

AS

Begin

 

declare @a datetime

set @a = @var

select @a

 

end

 

Consider a following console application for running the demo

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace ConsoleApp1

{
    class MyData
    {
        public void GetByDbType()
        {

SqlConnection cn = new SqlConnection("Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI");
            cn.Open();
            SqlCommand cmd = new SqlCommand("dbo.myproc", cn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlParameter p1 = new SqlParameter("@var",DbType.DateTime);
            DateTime d = DateTime.Now;
                  p1.Value = d;          Console.WriteLine(d.ToUniversalTime().ToString());

cmd.Parameters.Add(p1);
cmd.ExecuteNonQuery();
cn.Close();
Console.ReadLine();

        }

    }

 }

 

When you run a profiler trace on sql server 2008 instance while running the above console application the RPC Completed events are traced as

 

exec dbo.myproc @var='2009-07-18 04:09:28.1670000'

 

However when the same application is run against sql server 2005 instance and the sql server 2005 profiler show the following results

 

exec dbo.myproc @var=’’2009-07-18 04:08:50:037’’

 

So we observe that in sql server 2008 profiler, zeros are appended to the datetime values which should otherwise show a precision of 3.333ms as in case of sql 2005 profiler. The above behavior of the profiler appears to be caused due to the changes in the code of the sql server 2008 to accommodate the new datetime2 datatype in sql server 2008 which has higher precision.

 

However due to the above behavior of the profiler, when we are using profiler to replay the workload in DTA , the execution of the stored procedure can fail since the profiler sends the zero padded parameter to the Parser however the parser checks the datatype of the parameter as “datetime” and does not accept additional 0s.Hence due to the above behavior of the profiler the Replay of the workload from profiler in DTA cannot be used successfully.

 

Reference
=======

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/6d455ce9-d699-46b0-9b1f-04cc8a3279ea

 

 

Parikshit
SE, Microsoft Sql Server

 

 

Reviewed By

 

Sourabh Agrawal
Technical Lead, Microsoft Sql Server