Applying new Version Updates

[hvisionexpo]

I have a client-server app that used SQL Server 2000. We do have updates to the program from time to time and have over 200 installs.
To apply DB updates we use to embed the SQL statements into the app and it became too overwhelming.

So we creating one SQL file that contains all the SQL needed to update to a version that we invloked from our application if it was not updated. When we invoke that we don't know if the script finished or not.

Now the dilemma is that we may have some clients that are using very old version of the application and it may need to apply several older SQL file updates before applying the latest one and there is no way of knowing if the previous script finished or not before starting the next SQL update.

e.g. i have version3.sql that i need to apply before i apply version4.sql if the user is using version2

if the user is using version3.sql I only need to apply version4.sql.

I know what version they are running by checking the version in their DB.

Is there a better way of applying DB updates? I do not want to have any user intervention.

THANKS

 

[mrdenny]

Before making each schema change, look to see if the change has already been made. For example if you are adding a column to a table as part of your upgrade you can check the INFORMATION_SCHEMA.columns view to see if the column exists. If it doesn't add it via the ALTER database command. If it does, don't to anything and move on the next item.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[hvisionexpo]

Thanks for the reply .

This solution sounds very cumbersome.
I may have 100's of schema changes and 1000's of insert, update, delete statements. To check for each one before applying would be a lot of work unless there is some automated way of generating the script.

 

[mrdenny]

While yes it will be time consumming to write all the code, checking to see if the change has already been made is the only way to ensure that you aren't trying to make the change again.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com