|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Inconsistent dat paremeter in queryother SQL 2000. The query contains a date paremeter @Date... Me.SqlSelectCommand1.Parameters.AddRange(New System.Data.SqlClient.SqlParameter() {New System.Data.SqlClient.SqlParameter("@Date", System.Data.SqlDbType.DateTime, 8, "Date")}) The code was written using VS2003 (.Net 1.1) and is now executing on .Net 2.0. SQL Profiler displays the generated query for SQL 2000 as... exec sp_executesql N'SELECT FunctionID, Name, Date, Description FROM vwTicketedFunctions WHERE (Date >= @Date) AND (OnSaleDate <= @Date) ORDER BY StartTime', N'@Date datetime', @Date = 'Oct 26 2007 12:00:00:000AM' and the generated query for SQL 2005 as ... exec sp_executesql N'SELECT FunctionID, Name, Date, Description FROM vwTicketedFunctions WHERE (Date >= @Date) AND (OnSaleDate <= @Date) ORDER BY StartTime',N'@Date datetime',@Date=''2007-10-26 00:00:00:000'' Note that the date paremeter formats are different. My problem is that the SQL 2000 query parses Ok but the SQL 2005 generates the error: Line 2: Incorrect syntax near '2007'. Note also that regional setting is English(Australia). Mike,
Net 1.1 and Net 2.0 are not completely compatible to its other, did you have Net 1.1 as well installed? Cor "Cor Ligthert[MVP]" wrote: No. Only .Net 2 is installed.> Mike, > > Net 1.1 and Net 2.0 are not completely compatible to its other, did you have > Net 1.1 as well installed? > > Cor > Hi Mike
Your problem has nothing to do with .net. You get two different formatting because probably you use two different sql profilers or two different regional settings or something like that. ..net always send parameters as *data*, not text and thus the profiler output format doesn't matter at all - that's one of the benefits of using parametrised queries. -- Show quoteMiha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "HMike" <MikeH@community.nospam> wrote in message news:D5395EE7-7A05-48CA-80AA-236DC3C0112B@microsoft.com... >I am using the same code to query different databases, one SQL 2005 and the > other SQL 2000. The query contains a date paremeter @Date... > > Me.SqlSelectCommand1.Parameters.AddRange(New > System.Data.SqlClient.SqlParameter() {New > System.Data.SqlClient.SqlParameter("@Date", > System.Data.SqlDbType.DateTime, > 8, "Date")}) > > The code was written using VS2003 (.Net 1.1) and is now executing on .Net > 2.0. > > SQL Profiler displays the generated query for SQL 2000 as... > > exec sp_executesql N'SELECT FunctionID, Name, Date, Description FROM > vwTicketedFunctions WHERE (Date >= @Date) AND (OnSaleDate <= @Date) ORDER > BY > StartTime', N'@Date datetime', @Date = 'Oct 26 2007 12:00:00:000AM' > > and the generated query for SQL 2005 as ... > > exec sp_executesql N'SELECT FunctionID, Name, Date, Description FROM > vwTicketedFunctions WHERE (Date >= @Date) AND > (OnSaleDate <= @Date) ORDER BY StartTime',N'@Date > datetime',@Date=''2007-10-26 00:00:00:000'' > > Note that the date paremeter formats are different. My problem is that the > SQL 2000 query parses Ok but the SQL 2005 generates the error: Line 2: > Incorrect syntax near '2007'. > > Note also that regional setting is English(Australia). > > And what is the reason that it goes wrong?
At least I know that the way a parameter is added to the parametercollection is changed between 1.1 and 2.0. If that is not the solution is possible, however now you are so sure that it is not the problem, what is than the reason? Cor Because:
a) he is speaking about problems running the query (he got from profiler) in sql client tool b) one can't get invalid formatting of data when the value is passed as a parameter c) when using parametrisation the value, not formated text, is used -- Show quoteMiha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "Cor Ligthert[MVP]" <notmyfirstn***@planet.nl> wrote in message news:EEFABD85-AB68-4470-8D1F-FCD1F356D4EA@microsoft.com... > And what is the reason that it goes wrong? > > At least I know that the way a parameter is added to the > parametercollection is changed between 1.1 and 2.0. If that is not the > solution is possible, however now you are so sure that it is not the > problem, what is than the reason? > > Cor Yeh,
In my idea is the question is why there is a different when running on SQL 2005 with Net 2.0 a difference in the used values of the parameters instead of SQL 2000 with Net 1.1. @Date = 'Oct 26 2007 12:00:00:000AM' @Date=''2007-10-26 00:00:00:000'' Cor This is a formatting issue of the sql client tool.
I don't know what tools OP is using and how or where are they used. I wouldn't bother about it. -- Show quoteMiha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "Cor Ligthert[MVP]" <notmyfirstn***@planet.nl> wrote in message news:1C790871-2D7E-4B35-9B18-B32BD7862E34@microsoft.com... > Yeh, > > In my idea is the question is why there is a different when running on SQL > 2005 with Net 2.0 a difference in the used values of the parameters > instead of SQL 2000 with Net 1.1. > > @Date = 'Oct 26 2007 12:00:00:000AM' > > @Date=''2007-10-26 00:00:00:000'' > > Cor  |
|||||||||||||||||||||||
Other interesting topics