What do you think of a query that generates a
System.Data.SqlClient.SqlException when submitted via a application,
but when run through QueryAnalyzer or EnterpriseManager doesn't
generate a error? Here are 2 examples of the query:

SELECT table1.*,table2.field1,table2.field2,table2.field3 FROM
table1,table2 WHERE table1.field1 = table2.field1 AND
table1.field2='103' AND
table2.TStamp > '12/19/2005 12:20:14 PM' ORDER BY table2.TStamp

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '12'.

SELECT table1.*,table2.field1,table2.field2,table2.field3 FROM
table1,table2 WHERE table1.field1 = table2.field1 AND
table1.field2='103' AND
table2.TStamp > '12/8/2005 8:29:43 AM'ORDER BY table2.TStamp

System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '8'.

I believe the error is referencing the hour portion of the timestamp.
When I run these queries through QA/EM I don't get a result set ... so
maybe those apps just trap the exception and ignore it. Still, see
anything wrong with the TStamp?

> What do you think of a query that generates a
> System.Data.SqlClient.SqlException when submitted via a application,
> but when run through QueryAnalyzer or EnterpriseManager doesn't
> generate a error?

I think these are not the actual queries sent to SQL Server. You can verify
this with a Profiler trace. You will get these errors if you remove the
quotes around the datetime:

SELECT table1.*,table2.field1,table2.field2,table2.field3 FROM
table1,table2 WHERE table1.field1 = table2.field1 AND
table1.field2='103' AND
table2.TStamp > 12/19/2005 12:20:14 PM ORDER BY table2.TStamp

Also, this problem suggests that you are building a string within your
application and then executing it. You might instead consider using a
parameterized query and command parameters. This can improve performance,
security and provide cleaner code. Also, you won't need to worry about
datetime format issues or embedded quotes.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Johnny Ruin" <schafer.dave@gmail.com> wrote in message
news:1135089495.642849.50150@z14g2000cwz.googlegro ups.com...
> What do you think of a query that generates a
> System.Data.SqlClient.SqlException when submitted via a application,
> but when run through QueryAnalyzer or EnterpriseManager doesn't
> generate a error? Here are 2 examples of the query:
>
> SELECT table1.*,table2.field1,table2.field2,table2.field3 FROM
> table1,table2 WHERE table1.field1 = table2.field1 AND
> table1.field2='103' AND
> table2.TStamp > '12/19/2005 12:20:14 PM' ORDER BY table2.TStamp
>
> System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '12'.
>
> SELECT table1.*,table2.field1,table2.field2,table2.field3 FROM
> table1,table2 WHERE table1.field1 = table2.field1 AND
> table1.field2='103' AND
> table2.TStamp > '12/8/2005 8:29:43 AM'ORDER BY table2.TStamp
>
> System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '8'.
>
> I believe the error is referencing the hour portion of the timestamp.
> When I run these queries through QA/EM I don't get a result set ... so
> maybe those apps just trap the exception and ignore it. Still, see
> anything wrong with the TStamp?
>

There is no space between the time and ORDER BY

'12/8/2005 8:29:43 AM'ORDER BY table2.TStamp

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Johnny Ruin" <schafer.dave@gmail.com> wrote in message
news:1135089495.642849.50150@z14g2000cwz.googlegro ups.com...
> What do you think of a query that generates a
> System.Data.SqlClient.SqlException when submitted via a application,
> but when run through QueryAnalyzer or EnterpriseManager doesn't
> generate a error? Here are 2 examples of the query:
>
> SELECT table1.*,table2.field1,table2.field2,table2.field3 FROM
> table1,table2 WHERE table1.field1 = table2.field1 AND
> table1.field2='103' AND
> table2.TStamp > '12/19/2005 12:20:14 PM' ORDER BY table2.TStamp
>
> System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '12'.
>
> SELECT table1.*,table2.field1,table2.field2,table2.field3 FROM
> table1,table2 WHERE table1.field1 = table2.field1 AND
> table1.field2='103' AND
> table2.TStamp > '12/8/2005 8:29:43 AM'ORDER BY table2.TStamp
>
> System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '8'.
>
> I believe the error is referencing the hour portion of the timestamp.
> When I run these queries through QA/EM I don't get a result set ... so
> maybe those apps just trap the exception and ignore it. Still, see
> anything wrong with the TStamp?
>

On 20 Dec 2005 06:38:15 -0800, Johnny Ruin wrote:

>What do you think of a query that generates a
>System.Data.SqlClient.SqlException when submitted via a application,
>but when run through QueryAnalyzer or EnterpriseManager doesn't
>generate a error? Here are 2 examples of the query:
>
>SELECT table1.*,table2.field1,table2.field2,table2.field3 FROM
>table1,table2 WHERE table1.field1 = table2.field1 AND
>table1.field2='103' AND
>table2.TStamp > '12/19/2005 12:20:14 PM' ORDER BY table2.TStamp
>
>System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '12'.
>
>SELECT table1.*,table2.field1,table2.field2,table2.field3 FROM
>table1,table2 WHERE table1.field1 = table2.field1 AND
>table1.field2='103' AND
>table2.TStamp > '12/8/2005 8:29:43 AM'ORDER BY table2.TStamp
>
>System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '8'.
>
>I believe the error is referencing the hour portion of the timestamp.
>When I run these queries through QA/EM I don't get a result set ... so
>maybe those apps just trap the exception and ignore it. Still, see
>anything wrong with the TStamp?

Hi Johnny,

In addition to the replies Dan and Mike posted, let me add that you
should also stop using ambiguous date formats. The following three
formats are the only three formats GUARANTEED to be unambiguous:

* yyyymmdd - note: no dashes, slashes, dots, or other punctuation.
* yyyy-mm-ddThh:mm:ss - note: date compontents seperated by dashes, time
components seperated by colons, time in 24-hour clock and an uppercase T
in the middle.
* yyyy-mm-ddThh:mm:ss.mmm - same as above, but with milliseconds added
(seperated from seconds by a decimal point).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Hello everyone,

Thank you for your responses. My apologies for such a late reply.

Dan, you were right. Those weren't the queries that were being sent to
the server. I put the profiler to work and found the queries that I'm
being sent had a '#' wrapping the date. I had known about that issue
and put a line in to replace the #'s with quotes. I had misplaced my
pstrQuery->Replace call so that my logfile showed the single quotes
even though the # were being sent in .... so silly. Sorry I
bothered the group with it.

Mike, that missing space issue was my typo in presenting the question.
I was editing the original query so things would be closer to the
simplest case.

Hugo, thanks for that info. I found some stuff about that in the
"Writing International Transact-SQL Statements". Unfortunately this
string is the same string the user sees. I'm basically a pass
through, so it'd be special code to snag it and change. Seems to be
working, but if I have more date issues I'll revisit that element.

Thanks again for everyone's comments, and again, apologies for late
reply and foolish post. It just never pays to be in hurry does it?