approach to creating a procedure that contains dll , dml to upgrade a system

Hi.

I am between beginner and intermediate level of knowledge of sql server.
I am dealing with an sql server 2005 situation (oracle also but thats another
thread/story).

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:


write a single stored procedure to do all the work.
have it check for existence of table01_bak and table02_bak
delete them if they exist
recreate them

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)

start a transaction

generate the new primarky key values and insert them
update all the other tables that refer to the still existing primary keys to
point them to the newly generated ones
delete all the old rows using the original primary keys

commit a transaction

delete the table01_bak and table02_bak



thanks
Jeff

Jeff Kish

Re: approach to creating a procedure that contains dll , dml to upgrade a system

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: approach to creating a procedure that contains dll , dml to upgrade a system

On Tue, 15 May 2007 21:47:09 +0000 (UTC), Erland Sommarskog
wrote:

>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.
>
thanks much!
As problems arise, I'll investigate dynamic sql for a solution. I assume you
are talking about placing the alter table commands in dynamic sql then...
right?
Regards

Jeff Kish

Re: approach to creating a procedure that contains dll , dml to upgrade a system

Jeff Kish (jeff.kish@mro.com) writes:
> As problems arise, I'll investigate dynamic sql for a solution. I assume
> you are talking about placing the alter table commands in dynamic sql
> then... right?

No, rather it's the commands that follow that you may have to put in
dynamic SQL. But you will find out if you need to.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx