Problem with profiling datetime

Hi,
SQL Server 2005 x64
I have a weird problem when analysing a trace log with DTA.
The problem is that the trace log include syntax error for datetime!!!
Ex. A date time parameter would appear like this in the trace window.
@.ClaimDate=''2006-03-27 00:00:00:000''
-- Note the two single quotes at the end and start...
Whe analysing, DTA give me this error
[Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2006'
I can't figue out how to change this so I can't profile any statement that
include date.
Please help!!!!
I will add a sample...since my initial post is not that clear...
That simple .NET code
SqlCommand cmd = new SqlCommand("select @.TestDate");
cmd.Parameters.AddWithValue("@.TestDate", DateTime.Now);
cmd.Connection = conn;
DateTime dt = (DateTime) cmd.ExecuteScalar();
The resulting trace log item is as follow
exec sp_executesql N'select @.TestDate',N'@.TestDate
datetime',@.TestDate=''2006-04-02 18:44:22:687''
Wich will fail when analyzed since there is a syntax error in the command
e.g. two single quote at the end and start of the date.
Please help!
"Martin Masse" wrote:

> Hi,
> SQL Server 2005 x64
> I have a weird problem when analysing a trace log with DTA.
> The problem is that the trace log include syntax error for datetime!!!
> Ex. A date time parameter would appear like this in the trace window.
> @.ClaimDate=''2006-03-27 00:00:00:000''
> -- Note the two single quotes at the end and start...
> Whe analysing, DTA give me this error
> [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near '2006'
> I can't figue out how to change this so I can't profile any statement that
> include date.
> Please help!!!!
>
>
>
|||Martin Masse (MartinMasse@.discussions.microsoft.com) writes:
> I will add a sample...since my initial post is not that clear...
> That simple .NET code
> SqlCommand cmd = new SqlCommand("select @.TestDate");
> cmd.Parameters.AddWithValue("@.TestDate", DateTime.Now);
> cmd.Connection = conn;
> DateTime dt = (DateTime) cmd.ExecuteScalar();
> The resulting trace log item is as follow
> exec sp_executesql N'select @.TestDate',N'@.TestDate
> datetime',@.TestDate=''2006-04-02 18:44:22:687''
> Wich will fail when analyzed since there is a syntax error in the command
> e.g. two single quote at the end and start of the date.
> Please help!
I don't really have a suggestion how to work around this, as I have not
worked much with the DTA.
However, it is obviously a bug somewhere, although I can't tell whether
it is in SQL Trace, the Profiler or in SqlClient. Nevertheless I took
the liberty to submit a bug for it on the MSDN Product Feedback Cetnre,
http://lab.msdn.microsoft.com/Produc...x?feedbackId=F
DBK48153
What I noticed was that if I changed DateTime.Now to "DateTime.Now",
that is a string, I got the correct output in Profiler.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Thanks Erland.
"Erland Sommarskog" wrote:

> Martin Masse (MartinMasse@.discussions.microsoft.com) writes:
> I don't really have a suggestion how to work around this, as I have not
> worked much with the DTA.
> However, it is obviously a bug somewhere, although I can't tell whether
> it is in SQL Trace, the Profiler or in SqlClient. Nevertheless I took
> the liberty to submit a bug for it on the MSDN Product Feedback Cetnre,
> http://lab.msdn.microsoft.com/Produc...x?feedbackId=F
> DBK48153
> What I noticed was that if I changed DateTime.Now to "DateTime.Now",
> that is a string, I got the correct output in Profiler.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pro...ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinf...ons/books.mspx
>