|
|
Posted by Erland Sommarskog on 05/15/07 21:47
Jeff Kish (jeff.kish@mro.com) 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@sommarskog.se
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
Navigation:
[Reply to this message]
|