That brings up the next question. I am working backwords on this to stem the flow of data going into the varchar column until I can test and convert.
I typically use @isnum to check characters before an insert/update. Not a problem going into a varchar column. But I need to change this to a decimal and @isnnum allows the passing of other characters besides numbers and ".". Does anyone have a quick hack around this besides having to check all characters before a submit? I was thinking of @keep, but that may be confusing to the user. Thoughts? Oh... and thanks! _____ From: Robert Shubert [mailto:[email protected]] Sent: Tuesday, July 31, 2012 10:34 AM To: [email protected] Subject: RE: TeraScript-Talk: O.T. - MSSQL 2008 Changing VarChar to Decimal You should also check empty values. I'm not sure if you can set a numeric column = '' In any case, SQL Server does a number of automatic casts and conversions when it parses the query. Whether or not you have an issue will depend on several factors. It's good that you are aware of what problems may arise and if they don't then you can assume that the query structure is within tolerance of SQL Server's parser. Newer versions of SQL Server may also react differently. Robert From: WebDude [mailto:[email protected]] Sent: Tuesday, July 31, 2012 11:11 AM To: [email protected] Subject: RE: TeraScript-Talk: O.T. - MSSQL 2008 Changing VarChar to Decimal Hi Robert, Thanks for the response. I just tested this in my old version of Witango, quoting does not seem to be an issue. I created a taf that searches then allows an update to the field. I started with a varchar (9) when I built the taf. I then changed the column to decimal (8, 4) and tried the taf again and updated with no problems. I then converted the column back to varchar (9) and repeated the process. Still no issue. I even reloaded the table in odbc. I then converted back to decimal (8, 4) and deleted the column to be updated and dragged itback in. Yes, it went to qouted value = false, but I cannot seem to break it by changing data types back and forth. I there something I am missing? _____ From: Robert Shubert [mailto:[email protected]] Sent: Tuesday, July 31, 2012 9:14 AM To: [email protected] Subject: RE: TeraScript-Talk: O.T. - MSSQL 2008 Changing VarChar to Decimal As long as each value in the varchar can be cast to a decimal, you're fine. I would do these steps: 1) Add a column to table: temp_col numeric(10,4) 2) Execute: UPDATE table SET temp_col = CAST(varchar_col AS numeric(10,4)) 3) Verify data 4) Alter table to remove varchar_col 5) Alter table to rename temp_col to varchar_col Also note that this change will likely create some issues with your TAFs. Mostly around quoting. In the places where you write to this column (insert and update actions) you'll want to turn off quoting (set Quote Value to False). You may also have to re-drag the columns into the various db actions to update the column type in the TAFs. I can't be sure if this will be necessary, but it will be obvious in that you'll get an error as soon as you make the change. Robert From: WebDude [mailto:[email protected]] Sent: Tuesday, July 31, 2012 7:52 AM To: [email protected] Subject: TeraScript-Talk: O.T. - MSSQL 2008 Changing VarChar to Decimal Hey Folks, Sorry for the off topic question. I have 2 columns in the DB that need to be converted from a VarChar to Decimal. I have noticed in the past that using Alter Table will throw an error if data is going to be truncated or cannot be changed. There are probably 250,000 records and of course I am leery about doing this so I don't mux the data. I am planning to copy some of the data to another table to experiment on before doing the big change. I assume NULL will stay NULL as other changes have been like this. I have changed numeric data before with no problems but this the first time converting from a VarChar. There are around 5000 records that I will need to "fix" in order to do this before moving forward. Does anyone see any problems with doing this this way? Thanks for any feedback... _____ To unsubscribe from this list, please send an email to [email protected] with "unsubscribe terascript-talk" in the body. _____ To unsubscribe from this list, please send an email to [email protected] with "unsubscribe terascript-talk" in the body. _____ To unsubscribe from this list, please send an email to [email protected] with "unsubscribe terascript-talk" in the body. _____ To unsubscribe from this list, please send an email to [email protected] with "unsubscribe terascript-talk" in the body. ---------------------------------------- To unsubscribe from this list, please send an email to [email protected] with "unsubscribe terascript-talk" in the body.
