Prev: Need to locate jobs running past their next scheduled run time SQL 2005
Next: I'm a New bie to SQL SERVER 2005
From: yqever on 28 Sep 2009 00:50
TEMPLATES table has a field X_UPDATED whose type is datetime. I use the code
to update it in SQL Server 2008 but an exception occurs.


cmd = new OdbcCommand("UPDATE TEMPLATES SET X_UPDATED=?
WHERE FILENAME='" + fileName + "'", this._dbConn);

OdbcParameter param2 = cmd.Parameters.Add("X_UPDATED",
OdbcType.DateTime);

param2.Value = DateTime.Now; // Exception
cmd.ExecuteNonQuery();



The exception is : System.Data.Odbc.OdbcException: ERROR [22008]
[Microsoft][SQL Server Native Client 10.0]Datetime field overflow.
Fractional second precision exceeds the scale specified in the parameter
binding.

It looks like the OdbcType.DateTime doesn't match SQL Server 2008's
datetime. How should I change my code to update the datetime field in
sqlserver 2008? Thanks.

BTW, sql server 2005 doesn't throw this exception.


From: Erland Sommarskog on 28 Sep 2009 04:25
yqever (yqever(a)163.com) writes:
> TEMPLATES table has a field X_UPDATED whose type is datetime. I use the
> code to update it in SQL Server 2008 but an exception occurs.
>
>
> cmd = new OdbcCommand("UPDATE TEMPLATES SET X_UPDATED=?
> WHERE FILENAME='" + fileName + "'", this._dbConn);
>
> OdbcParameter param2 = cmd.Parameters.Add("X_UPDATED",
> OdbcType.DateTime);
>
> param2.Value = DateTime.Now; // Exception
> cmd.ExecuteNonQuery();
>
>
>
> The exception is : System.Data.Odbc.OdbcException: ERROR [22008]
> [Microsoft][SQL Server Native Client 10.0]Datetime field overflow.
> Fractional second precision exceeds the scale specified in the parameter
> binding.
>
> It looks like the OdbcType.DateTime doesn't match SQL Server 2008's
> datetime. How should I change my code to update the datetime field in
> sqlserver 2008? Thanks.
>
> BTW, sql server 2005 doesn't throw this exception.

Correct, they did some changes there. I ran into the same thing with
OLE DB. If memory serves the issue is that you cannot provide a value
with more than three decimals for the fractional value of a datetime
value in SQL Server, as datetime has a precision of 3.33 ms. So if
DateTime.Now includs microseonds you get this error. You would need
to strip these first.

Two other comments:

1) Why do you inline fileName in the query? You should make it a parameter
at all.

2) Why do you use Odbc Client to access SQL Server? OK, if your application
also can interact with other platforms, there maybe reason for it. But
if you access SQL Server only, you should use SqlClient instead.






--
Erland Sommarskog, SQL Server MVP, esquel(a)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
From: yqever on 28 Sep 2009 05:05
Thank you, Erland. I truncated the millisenconds of updateTime. No
cexeption occurs.

My code needs to work with MS Access, SQL Server and Oracle. So I can't use
SqlClient.


"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9C946A1377FC7Yazorman(a)127.0.0.1...
> yqever (yqever(a)163.com) writes:
>> TEMPLATES table has a field X_UPDATED whose type is datetime. I use the
>> code to update it in SQL Server 2008 but an exception occurs.
>>
>>
>> cmd = new OdbcCommand("UPDATE TEMPLATES SET X_UPDATED=?
>> WHERE FILENAME='" + fileName + "'", this._dbConn);
>>
>> OdbcParameter param2 = cmd.Parameters.Add("X_UPDATED",
>> OdbcType.DateTime);
>>
>> param2.Value = DateTime.Now; // Exception
>> cmd.ExecuteNonQuery();
>>
>>
>>
>> The exception is : System.Data.Odbc.OdbcException: ERROR [22008]
>> [Microsoft][SQL Server Native Client 10.0]Datetime field overflow.
>> Fractional second precision exceeds the scale specified in the parameter
>> binding.
>>
>> It looks like the OdbcType.DateTime doesn't match SQL Server 2008's
>> datetime. How should I change my code to update the datetime field in
>> sqlserver 2008? Thanks.
>>
>> BTW, sql server 2005 doesn't throw this exception.
>
> Correct, they did some changes there. I ran into the same thing with
> OLE DB. If memory serves the issue is that you cannot provide a value
> with more than three decimals for the fractional value of a datetime
> value in SQL Server, as datetime has a precision of 3.33 ms. So if
> DateTime.Now includs microseonds you get this error. You would need
> to strip these first.
>
> Two other comments:
>
> 1) Why do you inline fileName in the query? You should make it a parameter
> at all.
>
> 2) Why do you use Odbc Client to access SQL Server? OK, if your
> application
> also can interact with other platforms, there maybe reason for it. But
> if you access SQL Server only, you should use SqlClient instead.
>
>
>
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)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


From: Erland Sommarskog on 28 Sep 2009 17:32
yqever (yqever(a)163.com) writes:
> My code needs to work with MS Access, SQL Server and Oracle. So I can't
> use SqlClient.

OK. My experience is that OleDb client works better with SQL Server than
Odbc Client, but maybe it's the other way round with the other two.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Need to locate jobs running past their next scheduled run time SQL 2005
Next: I'm a New bie to SQL SERVER 2005