|
|
Posted by Dan Guzman on 12/20/05 16:50
> 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.googlegroups.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?
>
[Back to original message]
|