We help IT Professionals succeed at work.

Need to add new field to SQL Server table for ASP web application

JMS1965
JMS1965 asked
on
Hello, Experts -

I have a SQL Server database in use for an ASP.NET web application.  This app has a few users and is in testing mode.

I need to add a new field to two tables in the deployed database, and was wondering what the best method is to do this?  I can bring the database down from the server and make the change, then re-deploy it, but am curious if there is a way to do this programmatically?

I'm thinking the answer might be in Stored Procedures, but I haven't used these and don't know how to write an SP or how to run it.

Any suggestions, examples, etc. would be most appreciated -- thank you!
Comment
Watch Question

Garry GlendownConsulting and Network/Security Specialist

Commented:
Usually, something like this ought to work:

alter table TABLENAME add (fieldname fieldtype [options[, ..]]);
You can also install MS SQL Management studio and manage your tables manually
http://www.microsoft.com/download/en/details.aspx?id=7593

Commented:
and for running Garry-G suggestion you will need to connect to your database using Microsoft management studio
http://www.microsoft.com/download/en/details.aspx?id=7593

Commented:
Just remember one important thing:
If there is at least one row in the table you must add your field with NULL allow, then run a querry (something like Update myTable set myNewField = somevalue). Only after that you can cancel NULL allow for this field.
otherwise you can act according to Garry-G's advice.
Top Expert 2011

Commented:
the best way to do this is usually to create a set of scripts to be applied to the database ....

you then run these scripts as standalone DOS/CMD files invoking the BCP or SQLCMD utilities to apply your changes to the database....

that way the scripts can be placed in between appropriate other required DB maintenance tasks ...

ie.. start with a full database backup....
      ... deal with any replication / linked server issues
      ... make any "text" backups of existing table data for audit/results checking purposes
     x ... apply database changes schema
     x... apply database changes data
     x.... TEST TEST TEST
      .... apply any performance db scripts (e.g. redo stats  etc ... may have been done be for the testing...)
      .... re-apply replication/linked server issues ...
      ..... backup database

    make avaliable for general users

depending on the scope of the change the steps involved (x) can be quite complex , especially if data transformation is involved.

your dba should develop these scripts along side your development process so that they can be tested in the same manner as your actuall process code, for deployment....

equal care needs to be spent on them,,,

Author

Commented:
Thanks for all the info!  

I am a one-person company, there is no dba so I need to learn this.  I already have SQL Management Studio and understand this approach, but I want to add the new fields to a db that is already on a commercial hosting server.  

So, I gather I can create an SP such as this:

ALTER PROCEDURE dbo.StoredProcedure1
AS
      ALTER TABLE Appointments
      ADD LabelID int

      RETURN

Next question ... after uploading this to the server, how do I run it (one-time)? I have an admin folder in the site directory, where I can put an aspx page that only I can get to, but what do I code in the page-behind to execute this SQL statement?
Commented:
If you have got SQL MS then use it.. This way is much easier and won't make a mistake (regarding your experience in SQL). Connect to DB, Open Tables, Right click on table you want to change. Then Edit and  right click on field list - insert new field,.. etc. After all changes you must save (not close).

Anyway, before doing changes you must be sure you won't break the business logic and sql inside rules, triggers, stored procedure, etc.

The best you can do is to create a new DB, Copy your working DB to the new instance. Copy your application to a new location. Change application's config (connectionString) to the new DB and now you can do all changes you wish. If you make a mistake you always can start once again from scratch.
Top Expert 2011

Commented:
@mrRany

Just remember one important thing:
If there is at least one row in the table you must add your field with NULL allow, then run a querry (something like Update myTable set myNewField = somevalue). Only after that you can cancel NULL allow for this field.
otherwise you can act according to Garry-G's advice.


that is not correct , you do not have to initially define a new column as nullable... you can define a column as not null with a default.

Commented:
Yes for an empty table otherwise you must set each row's new field to its value that is not possible as I know. I guess you got enough info to make a decision. Good luck.

Author

Commented:
OK, I can use SQL Management Studio.  I'll shut down the site, copy the database to my local machine, make the changes, then re-upload the modified DB.

I was thinking there might be a way to accomplish this while the DB was on the commercial server, but if this is the best way that's fine.

Thanks!
Commented:
you can!!! just connect to it using the management studio and do your changes. you don't have to download the database to your machine

Author

Commented:
Eyal -

Thank you!  While you were posting this clarification, I was working with my web host who helped me figure out how to do just that!

I see how it all works together now, and can easily make the modifications I need now.

Thanks!

Author

Commented:
Thanks to all for helping me understand this process and the proper use of available techniques