更新一个事务中的模式和行，SQL Server 2005

Question

我目前正在更新允许用户指定其表之一的模式的一部分的遗留系统。用户可以通过此界面创建和删除表中的列。这个遗留系统使用ADO 2.8，并且使用SQL Server 2005作为它的数据库（你甚至不想知道在这个野兽现代化尝试之前它使用了什么数据库......但是我离题了）=）

在这个相同的编辑过程中，用户可以定义（和更改）可存储在这些用户创建字段中的有效值列表（如果用户想限制字段中的内容）。

当用户更改字段的有效条目列表时，如果他们删除了其中一个有效值，则允许他们选择一个新的“有效值”来映射任何具有此（当前无效）值的行它，以便他们现在再次拥有有效的价值。

在浏览旧代码时，我注意到它非常容易让系统进入无效状态，因为上面提到的更改不是在一个事务中完成的（所以如果其他人在上述过程的中途出现上面并做出自己的改变......好吧，你可以想象可能导致的问题）。

问题是，我一直试图让它们在单个事务下更新，但是每当代码到达它改变表的模式的部分时，所有其他更改（更新行中的值，无论是在模式发生变化的表格中，它们甚至可以是完全不相关的表格），直到交易中的这一点似乎都被无声地丢弃了。我没有收到任何错误消息，表明它们已被删除，并且当我在最后提交事务时没有提出错误...但是当我查看应该在事务中更新的表时，只有新列在那儿。没有任何模式更改被保存。

在网上寻找答案迄今已被证明是浪费了几个小时......所以我转而寻求帮助。有没有人曾试图通过ADO执行事务，它既更新表的架构，也更新表中的行（无论是同一个表还是其他表）？不允许吗？有没有任何文件可以在这种情况下有所帮助？

编辑：

好吧，我做了跟踪，这些命令被送到数据库（括号中解释）

（我不知道这里发生了什么，看起来像它的创建临时存储过程...？）

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保存用户生成的字段定义信息）表

exec sp_cursoropen @p1 output,N'SELECT * FROM CustomFieldDefs ORDER BY Sequence',@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5 
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,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 

（这似乎是在那里我进入修改数据的定义，我经过每更新发生在定义自定义字段本身的任何变化）

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 

（这是我的代码删除通过接口被删除之前，这些储蓄开始] ...它也是唯一的事情，据我可以告诉这个交易过程中实际发生的）

ALTER TABLE CustomizableTable DROP COLUMN _weveknown; 

（现在，如果任何定义被改变的方式使用r创建的列的属性需要更改，或者列上的索引需要添加/删除，在此完成，并为给定列中尚未赋值的任何行赋予默认值...值得注意的是，据我所知，这一切都不当存储过程完成实际情况。）

 
go 
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'CustomizableTable') AND name = '__asdf' 
go 
ALTER TABLE CustomizableTable ALTER COLUMN __asdf VarChar(50) NULL 
go 
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomizableTable]') AND name = N'idx___asdf') CREATE NONCLUSTERED INDEX idx___asdf ON CustomizableTable ( 
__asdf ASC) WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF); 
go 
select * from IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomizableTable]') AND name = N'idx___asdf') CREATE NONCLUSTERED INDEX idx___asdf ON 
CustomizableTable (__asdf ASC) WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF); 
go 
UPDATE CustomizableTable SET [__asdf] = '' WHERE [__asdf] IS NULL 
go 
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'CustomizableTable') AND name = '_give' 
go 
ALTER TABLE CustomizableTable ALTER COLUMN _give Bit NULL 
go 
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomizableTable]') AND name = N'idx__give') DROP INDEX idx__give ON CustomizableTable WITH (ONLINE = OFF); 
go 
UPDATE CustomizableTable SET [_give] = 0 WHERE [_give] IS NULL 
go 
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'CustomizableTable') AND name = '_up' 
go 
ALTER TABLE CustomizableTable ALTER COLUMN _up Int NULL 
go 
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomizableTable]') AND name = N'idx__up') DROP INDEX idx__up ON CustomizableTable WITH (ONLINE = OFF); 
go 
UPDATE CustomizableTable SET [_up] = 0 WHERE [_up] IS NULL 
go 
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'CustomizableTable') AND name = '_Testy' 
go 
ALTER TABLE CustomizableTable ADD _Testy VarChar(50) NULL 
go 
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomizableTable]') AND name = N'idx__Testy') DROP INDEX idx__Testy ON CustomizableTable WITH (ONLINE = OFF); 
go 
UPDATE CustomizableTable SET [_Testy] = '' WHERE [_Testy] IS NULL 
go 
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'CustomizableTable') AND name = '_you' 
go 
ALTER TABLE CustomizableTable ALTER COLUMN _you VarChar(250) NULL 
go 
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomizableTable]') AND name = N'idx__you') DROP INDEX idx__you ON CustomizableTable WITH (ONLINE = OFF); 
go 
UPDATE CustomizableTable SET [_you] = '' WHERE [_you] IS NULL 
go 
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'CustomizableTable') AND name = '_never' 
go 
ALTER TABLE CustomizableTable ALTER COLUMN _never DateTime NULL 
go 
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomizableTable]') AND name = N'idx__never') DROP INDEX idx__never ON CustomizableTable WITH (ONLINE = OFF); 
go 
UPDATE CustomizableTable SET [_never] = '1/1/1900' WHERE [_never] IS NULL 
go 
SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(N'CustomizableTable') AND name = '_gonna' 
go 
ALTER TABLE CustomizableTable ALTER COLUMN _gonna Money NULL 
go 
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[CustomizableTable]') AND name = N'idx__gonna') DROP INDEX idx__gonna ON CustomizableTable WITH (ONLINE = OFF); 
go 
UPDATE CustomizableTable SET [_gonna] = 0 WHERE [_gonna] IS NULL 
go 

（完成交易......？）

 
exec sp_cursorclose 180150003 
go 

毕竟广告如上所述，只发生列的删除。事务之前和之后的所有内容似乎都被忽略，并且SQL跟踪中没有消息表明事务期间发生了错误。

Answer 1

代码使用服务器端游标，这就是这些调用的用途。第一组调用是准备/打开游标。然后从光标中提取行。最后关闭游标。这些sprocs类似于OPEN CURSOR，FETCH NEXT，CLOSE CURSOR T-SQL语句。

我不得不仔细看看（我会这样做），但是我的猜测是服务器端游标，封装事务和DDL有一些问题。

一些其他问题：

1. 你的意思在这种情况下，使用服务器端游标？
2. ADO命令是否都使用相同的活动连接？

更新：

我不能完全肯定发生了什么事。

看起来您正在使用服务器端游标，因此您可以使用Recordset.Update（）将更改推送回服务器，此外还执行生成的SQL语句以更改模式并更新动态表中的数据S）。使用相同的连接，在一个明确的事务中。

我不确定光标操作会对事务的其他部分产生什么影响，反之亦然，说实话，我很惊讶这不起作用。

我不知道它会发生多大的变化，但我建议远离服务器端游标并为您的表更新构建UPDATE语句。

对不起，我没有更多的帮助。

BTW-我发现了sp_cursor呼叫的以下信息：

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

Answer 2

您描述的行为是允许的。代码如何改变架构？动态构建SQL并通过ADO命令执行？还是使用ADOX？

如果您有权访问数据库服务器，请尝试在测试您概述的方案时运行SQL事件探查器跟踪。查看跟踪是否记录任何错误/回滚。