Update schema and rows in one transaction, SQL Server 2005 -

i'm updating legacy system allows users dictate part of schema of 1 of tables. users can create , remove columns table through interface. legacy system using ado 2.8, , using sql server 2005 database (you don't want know database using before attempt modernize beast began... digress. =) )

in same editing process, users can define (and change) list of valid values can stored in these user created fields (if user wants limit can in field).

when user changes list of valid entries field, if remove 1 of valid values, allowed choose new "valid value" map rows have (now invalid) value in it, have valid value again.

in looking through old code, noticed extremely vulnerable putting system invalid state, because changes mentioned above not done within transaction (so if else came along halfway through process mentioned above , made own changes... well, can imagine problems might cause).

the problem is, i've been trying them update under single transaction, whenever code gets part changes schema of table, of other changes (updating values in rows, in table schema changed or not... can unrelated tables even) made point in transaction appear silently dropped. receive no error message indicating dropped, , when commit transaction @ end no error raised... when go in tables supposed updated in transaction, new columns there. none of non-schema changes made saved.

looking on net answers has, far, proved waste of couple hours... turn here help. has ever tried perform transaction through ado both updates schema of table , updates rows in tables (be same table, or others)? not allowed? there documentation out there helpful in situation?

edit:

okay, did trace, , these commands sent database (explanations in parenthesis)

(i don't know what's happening here, looks it's creating temporary stored procedure...?)

 declare @p1 int set @p1=180150003 declare @p3 int set @p3=2 declare @p4 int set @p4=4 declare @p5 int set @p5=-1 

(retreiving table holds definition information user-generated fields)

 exec sp_cursoropen @p1 output,n'select * customfielddefs order sequence',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5 go 

(i think code iterating through list of them here, grabbing current information)

 exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursorfetch 180150003,1025,1,1 go exec sp_cursorfetch 180150003,1028,1,1 go exec sp_cursorfetch 180150003,32,1,1 go 

(this appears i'm entering modified data definitions, go through each , update changes occurred in definitions custom fields themselves)

 exec sp_cursor 180150003,33,1,n'[customfielddefs]',@sequence=1,@description='asdf',@format='u|',@islookup=1,@length=50,@properties='u|',@required=1,@title='__asdf',@type='',@_version=1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursor 180150003,33,1,n'[customfielddefs]',@sequence=2,@description='give',@format='y',@islookup=0,@length=0,@properties='',@required=0,@title='_give',@type='b',@_version=1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursor 180150003,33,1,n'[customfielddefs]',@sequence=3,@description='up',@format='###-##-####',@islookup=0,@length=0,@properties='',@required=0,@title='_up',@type='n',@_version=1 go  exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursor 180150003,33,1,n'[customfielddefs]',@sequence=4,@description='testy',@format='',@islookup=0,@length=50,@properties='',@required=0,@title='_testy',@type='',@_version=1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursor 180150003,33,1,n'[customfielddefs]',@sequence=5,@description='you',@format='u|',@islookup=0,@length=250,@properties='u|',@required=0,@title='_you',@type='',@_version=1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursor 180150003,33,1,n'[customfielddefs]',@sequence=6,@description='never',@format='mm/dd/yyyy',@islookup=0,@length=0,@properties='',@required=0,@title='_never',@type='d',@_version=1 go exec sp_cursorfetch 180150003,32,1,1 go exec sp_cursor 180150003,33,1,n'[customfielddefs]',@sequence=7,@description='gonna',@format='###-###-####',@islookup=0,@length=0,@properties='',@required=0,@title='_gonna',@type='c',@_version=1 go exec sp_cursorfetch 180150003,32,1,1 go 

(this code removes deleted through interface before saving began]... thing far can tell happens during transaction)

 alter table customizabletable drop column _weveknown; 

(now if of definitions altered in such way user-created column's properties need changed or indexes on columns need added/removed, done here, along giving default value rows didn't have value yet given column... note that, far can tell, none of happens when stored procedure finishes.)

 go select * sys.columns object_id = object_id(n'customizabletable') , name = '__asdf' go alter table customizabletable alter column __asdf varchar(50) null go if exists (select * sys.indexes object_id = object_id(n'[dbo].[customizabletable]') , name = n'idx___asdf') create nonclustered index idx___asdf on customizabletable (  __asdf asc) (pad_index  = off, sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off); go select * if exists (select * sys.indexes object_id = object_id(n'[dbo].[customizabletable]') , name = n'idx___asdf') create nonclustered index idx___asdf on  customizabletable ( __asdf asc) (pad_index  = off, sort_in_tempdb = off, drop_existing = off, ignore_dup_key = off, online = off); go update customizabletable set [__asdf] = '' [__asdf] null go select * sys.columns object_id = object_id(n'customizabletable') , name = '_give' go alter table customizabletable alter column _give bit null go if exists (select * sys.indexes object_id = object_id(n'[dbo].[customizabletable]') , name = n'idx__give') drop index idx__give on customizabletable ( online = off ); go update customizabletable set [_give] = 0 [_give] null go select * sys.columns object_id = object_id(n'customizabletable') , name = '_up' go alter table customizabletable alter column _up int null go if exists (select * sys.indexes object_id = object_id(n'[dbo].[customizabletable]') , name = n'idx__up') drop index idx__up on customizabletable ( online = off ); go update customizabletable set [_up] = 0 [_up] null go select * sys.columns object_id = object_id(n'customizabletable') , name = '_testy' go alter table customizabletable add _testy varchar(50) null go if exists (select * sys.indexes object_id = object_id(n'[dbo].[customizabletable]') , name = n'idx__testy') drop index idx__testy on customizabletable ( online = off ); go update customizabletable set [_testy] = '' [_testy] null go select * sys.columns object_id = object_id(n'customizabletable') , name = '_you' go alter table customizabletable alter column _you varchar(250) null go if exists (select * sys.indexes object_id = object_id(n'[dbo].[customizabletable]') , name = n'idx__you') drop index idx__you on customizabletable ( online = off ); go update customizabletable set [_you] = '' [_you] null go select * sys.columns object_id = object_id(n'customizabletable') , name = '_never' go alter table customizabletable alter column _never datetime null go if exists (select * sys.indexes object_id = object_id(n'[dbo].[customizabletable]') , name = n'idx__never') drop index idx__never on customizabletable ( online = off ); go update customizabletable set [_never] = '1/1/1900' [_never] null go select * sys.columns object_id = object_id(n'customizabletable') , name = '_gonna' go alter table customizabletable alter column _gonna money null go if exists (select * sys.indexes object_id = object_id(n'[dbo].[customizabletable]') , name = n'idx__gonna') drop index idx__gonna on customizabletable ( online = off ); go update customizabletable set [_gonna] = 0 [_gonna] null go 

(closing transaction...?)

 exec sp_cursorclose 180150003 go 

after ado above, deletion of column occurs. before , after in transaction appears ignored, , there no messages in sql trace indicate went wrong during transaction.

the code using server-side cursor, that's calls for. first set of calls preparing/opening cursor. fetching rows cursor. closing cursor. sprocs analogous open cursor, fetch next, close cursor t-sql statements.

i'd have take closer (which will), guess there going on server-side cursor, encapsulating transaction, , ddl.

some more questions:

1. are meaning use server-side cursors in case?
2. are ado commands using same active connection?

update:

i'm not sure what's going on.

it looks you're using server-side cursors can use recordset.update() push changes server, in addition executing generated sql statements alter schema , update data in dynamic table(s). using same connection, inside explicit transaction.

i'm not sure effect cursor operations have on rest of transaction, or vice-versa, , honest i'm surprised isn't working.

i don't know how large of change be, recommend moving away server-side cursors , building update statements table updates.

sorry couldn't of more help.

btw- found following information on sp_cursor calls:

http://jtds.sourceforge.net/apicursors.html