SQLServer SQL conversion of data (data converted to numeric type varchar Error)

  One day, the Executive 

  SELECT * FROM XXX_ORIGINAL_20031205 
  where msgid = 62010388000012 

  Statement, the SQL Server Error report: "Data will be converted to numeric type varchar was an error." 

  What does this mean? » 

  Msgid this field is the type: varchar (30). 

  Environment: 

  Database server: Microsoft SQL Server 2000 and 7.0; 

  The database server patches: Microsoft SQL Server 2000 ServicePack1; 

  Analysis: 

  SQL Server is not suddenly can not automatically be converted to figures from the string, but only on the field of numerical problems, and also in this field has been actually stored on a string. 

  You see, my implementation of the SQL statement is no problem, and can automatically switch: 

  SELECT * FROM XXXX_ORIGINAL_20031205 
  where recordid = 62010388000012 

  recordid this field is the type: varchar (30). 

  This is why you can » 

  为什么» 

  This is because the real value msgid field is similar to the string "12051113280101053509", thanks to your request with SQL in order to provide us with a string of figures 62010388000012 this match, so SQLServer default to so many "12051113280101053509"统统first converted to digital, again with 62010388000012 match. 

  (First of all this involves a question of efficiency, conversion msgid become so many figures, the number of matches Zaigen you will be a waste of how much ah) 

  Of course, but this time SQLServer to come, because "12051113280101053509" exchanged for a number of really too great, out of range, so you can see the report SQLServer As a result, "the data converted to numeric type varchar was an error," he refers to the The historical data "12051113280101053509" varchar (30) to a numeric will not do, rather than to convey your SQL script parameters 62010388000012 conversion failed. 

  Let us look at another form of error, is even more clear: 

  We have implemented 

  SELECT * FROM XXXX_ORIGINAL_20031205 
  where msgid = 120 

  Will get the wrong order: 

  varchar value'12050003010101026986 'conversion overflow out of the int.    The whole exceeds the maximum value. 

  This error is not very clear that the SQLServer help you in the implementation of SQL behind an order made by the things » 

  He tried to help you take the initiative to record in this field into your SQL specified in the order of that type. 

  My suggestions: 

  Very often, we too lazy to look at a field in the end what type is char, or tinyint, or bool, or varchar, we will not write a number, so clever SQL Server itself to the judgement as to what. 

  However, the first, SQL Server convert your script is not in numerical order, but has been converted to the historical data that you specify the type, time will increase the implementation of the second, easy conversion error. 

  Therefore, SQLServer must not let their judgement, help you to automatically convert, as will reduce the efficiency and increase the risk of error.    Explicit you can tell your SQL Server data types, it will be sure to do so. 

Share and Enjoy: These icons link to social bookmarking sites where readers can share and discover new web pages.
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Google
  • DZone
  • Netvouz
  • NewsVine
  • Technorati

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

AddThis Social Bookmark Button

Tags: