Re: approach to creating a procedure that contains dll , dml to upgrade a system
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 15 May 2007 21:47:09 +0000 (UTC)
Jeff Kish (jeff.kish@xxxxxxx) writes:
I need to assemble a process that an end user can use easily to update a
database. The update consists of some column lengthening, checks to make
sure new data will fit in columns after the ddl is executed, and then
generating new data (changing primary key column data and keeping all
the refering objects/rows in sync). I also don't want to have an error
leave things in a bad state.
I'm wondering if there is anything special to look out for, and if there
are any suggestions on approach. Here is my current approach:
One thing to remember is that SQL Server will first compile the
procedure according to the old table definition. It will certainly
recompile as you perform the ALTER TABLE statements. But you may
face the situation that the procedure does not compile with the old
definitions. This happens when you add new columns, and you seem to
only be changing existing columns, so you may get away with it.
In case you run into to it, the solution is to put troublesome statements
in dynamic SQL, or possibly an inner stored procedure.
copy table01 and table02 to table01_bak and table02_bak
calculate the new primary key values needed
make sure they will fit (I need to bail out and tell the user to make an
adjustment at this point if they will not)
If the new values don't fit, you should get an error provided that
ANSI_WARNINGS are on.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
- Follow-Ups:
- References:
- Prev by Date: Re: select 'message'; not appearing
- Next by Date: Re: Database Owner cannot connect
- Previous by thread: approach to creating a procedure that contains dll , dml to upgrade a system
- Next by thread: Re: approach to creating a procedure that contains dll , dml to upgrade a system
- Index(es):
Relevant Pages
|
