Creating/distributing SQL database updates for client databases

[TheQuestioner]

Dear SQL Server Overlords,

I'm part of a development team that uses MSDE 2000 to manage the database for our software product. As with any software, we periodically update the front-end and back-end of our product to all of our clients.

My problem is that I'm trying to find an easy way of updating all the views, stored procs, udt's, table structures of each of our client's databases so that it is synch with our master version.

Currently I'm using a range of third party products that compare the last released database to the master one, and then generate the appropriate TSQL DROP AND CREATE SQL statements.

However, if I then try and run this script on my client's MSDE server (using a third-party query analyser), I keep running into problems. Some of the views and stored procedures cannot be created, as the views that they are based on don't yet exist(as they are created afterwards in the script).

So, my questions are:-

1) Rather than manually trying to find a way of ordering the script so that the initial views/stored procedures are created first, is there a way of "bulk creating/adding" all of the views and stored procedures whilst temporarily suppressing any checks on underlying table/view/stored procedure existence?

2) Force each view/stored procedure to "rebuild/refresh" itself so that any internal structure changes have been updated and saved.

Thanks.

 

[SQLSister]

For Number 1, I don't know of a way. What I did when moving things to production was first runthe product to get all table changes and create that script. Then I ran the 3rd party product to create all views, then all functions, then finally all stored procs. I then ran the scripts on staging and found which ones were still in the wrong order and moved them on the script. Then ran on production.

For the second, it would probably be a bad idea to recreate allthe objects in your database wen you don;t need to. It would lock donw your production adatabase from changes for a long time and you still would need to recreate the objects in the correct order unless you went into single user mode and dropped all fk constraints ran the script and then recreated the constraints. All in all I think it is a poor idea.

Finally, be very careful of dropping and recreating tables, using alter table is a better idea. It is both faster and you will not have to recreate the data.

"NOTHING is more important in a database than integrity." ESquared

 

[TheQuestioner]

Thanks SQLSister, unfortunately the things you have mentioned are already the way that I currently do things.

Is there some type of TSQL command that allows me to "switch off" any attempts of SQL Server at checking for existences of underlying tables, stored procs and/or views, for each view that is created (or for a whole batch).

This way I can drop all of the views, then bulk-create new views in one go, and then re-enable the "switch-on" existence checker thingy.

 

[jrbarnett]

When I'm doing this sort of thing, I write out on paper the steps that need to change, then put them in order so that all prerequisites (such as your views that need to be created) are done ahead of the changes to existing objects.

By the way, MSDE 2000 does include a built in SQL client - its called osql, and its a command line tool, exactly the same as the one from the full blown SQL Server. True its not a fully graphical system, and you need to know your SQL commands to move around, but if all you need to do is run a pre prepared script, then its more than enough.
For more information on osql, see:
How to manage the SQL Server Desktop Engine (MSDE 2000) or SQL Server 2005 Express Edition by using the osql utility

John

 

[RiverGuy]

If the main issue is getting the correct order of object changes, then you might need to look at your development process a little closer. It's good practice to keep copies of your scripts wherein you make changes to the database. Give them useful names and date/time stamps. That way, you can compile one big script at the end and the ordering is easy.

So if you add a table on Monday, and create a view off of it on Tuesday, you will end up with two scripts you have saved. When it comes time to push these to production, you can go back and get the scripts you need and easily put them in the correct order by your file name or date modified.

That's not to say that I'm against pushing out a brand new rebuilt database. One thing I've been wanting to test but haven't had the chance to yet is to push out an entire new database each time for major revision changes. The database would have a slightly different name, a process would copy the data from the old database, and the connection settings in the application would be pointed to the new database. This way it's easier to roll back to the old database if needed.

 

[TheQuestioner]

Thanks jrbarnet and RiverGuy. I used to implement a manual tracking document that detailed which objects were changed, what was changed, and when they were changed. I would then create the appropriate TSQL statements.

Unfortunately, most of my clients now no longer have the same database version (due to various reasons outside my sphere of influence), which means that I would have to write a script for each client or check for the version of each object before applying the appropriate changes to get them to the latest version. This is too impractical.

I am aware that there is a special stored procedure that provides you with a list of all dependent objects for each object itself. I suppose I could use this order the script.

From what you are all telling me though, it seems as though there is no "bulk view creation with no underlying object checking" feature within SQL Server. Is that so?

 

[SQLSister]

Don't rely on sp_depends, it may not be accurate. I found this one out the hard way.

Can't think of a way to do what you want. Not to say there isn't one, I just don't know what the command might be.

"NOTHING is more important in a database than integrity." ESquared

 

[TheQuestioner]

Thanks SQLSister for pointing that out, you've saved me from some potential wasted effort