We wanted to change the datatype of Project_Task.Estimated_Effort and Project_Task.Actual_Effort from int to double/float by modifying them in the SQL Management Studio but it won't let us do that so we ended up running the ff. alter commands: IF EXISTS (SELECT 1 FROM SYS.COLUMNS SC JOIN SYS.TABLES TB ON SC.OBJECT_ID = TB.OBJECT_ID AND TB.[NAME] = 'PROJECT_TASK' AND SC.[NAME] = 'ESTIMATED_EFFORT') ALTER TABLE [dbo].[PROJECT_TASK] ALTER COLUMN [ESTIMATED_EFFORT] [float] NULL GO IF EXISTS (SELECT 1 FROM SYS.COLUMNS SC JOIN SYS.TABLES TB ON SC.OBJECT_ID = TB.OBJECT_ID AND TB.[NAME] = 'PROJECT_TASK' AND SC.[NAME] = 'ACTUAL_EFFORT') ALTER TABLE [dbo].[PROJECT_TASK] ALTER COLUMN [ACTUAL_EFFORT] [float] NULL GO
Then because we're actually using the soap service to save/retrieve data, we also modified the corresponding spPROJECT_TASKS_Update Stored Procedure and the related classes in SqlCode.cs to make use of float instead of the float variable. After doing this set of actions the save process became screwed up - i.e., some of the passed values don't get saved in the database.
Are we missing something that should be done so changing the datatype will work for us?
Also, what would be the process in case the column we want to alter is in the _CSTM table?
Our 5.0 build already converts these to fields to floats, so your best bet is to simply upgrade.
You have to change the base table, the audit table, the stored procdure and recompile the views. Lastly, you need to update the SqlProcs.cs file and modify the code-behind files.
Unfortunately we've made quite a number of customizations and we may not be able to roll all of them over if we do an upgrade.
Can you point me to what needs to be changed in the audit table? Will it be a straightforward alter statement directed at the concerned columns?
Also do you have a list of all the views and code-behind files that are affected?
Then still get the 5.0 version and extract what you need. You will find the alter table commands in the SQL Scripts \ Tables folder. You will find an updated stored procedure in the SQL Scripts \ Procedures folder. You will need to recompile the views after altering the table and you will need to regenerate the SqlProcs.cs file after updating the procedure. Lastly, you will need to update the code-behind to return the float values instead of integer values.
I found the alter table script and the updated sp but pardon my ignorance on some of the things. For instance, why do I need to recompile the views when they don't seem to have anything to do with data type definition (i.e., I didn't add or remove fields, just changed the type). Is there a tool for regenerating the SqlProcs.cs file as I am manually editing it? Lastly, by code-behind you mean the aspx pages? I am actually at lost as to where to update it because the web page is now showing the values as float but when I edit them I get an error when I try to save the values back because it seems the textboxes are still bound as integer.