"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!!!!
>
>
>
>
>
>

> 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!

"Erland Sommarskog" wrote:

> Martin Masse (MartinMa***@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/ProductFeedback/viewFeedback.aspx?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, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>