我当前正在更新一个旧系统，该系统允许用户指定其表之一的部分架构。用户可以通过此界面在表中创建和删除列。这个旧系统使用的是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

(获取包含用户生成的字段的定义信息的表)

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;

(现在，如果更改了任何定义，以致需要更改用户创建的列的属性或者需要添加/删除列的索引，则可以在此处完成，同时为所有行提供默认值对于给定的列还没有值...请注意，据我所知，当存储过程完成时，实际上没有任何事情发生。)

`

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跟踪中没有消息表明事务期间发生了问题。

解决方案

回答

我们描述的行为是允许的。代码如何更改架构？快速生成SQL并通过ADO命令执行？还是使用ADOX？

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

回答

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

我必须仔细研究一下(我会做)，但是我猜想服务器端游标，封装事务和DDL会发生一些事情。

还有一些问题：

• 我们是在这种情况下使用服务器端游标吗？
• ADO命令是否都使用相同的活动连接？

更新：

我不确定到底发生了什么。

看起来我们正在使用服务器端游标，因此除了执行生成的SQL语句来更改架构和更新动态表中的数据外，还可以使用Recordset.Update()将更改推回服务器。在显式事务内使用相同的连接。

我不确定游标操作将对其余事务产生什么影响，反之亦然，老实说，我很惊讶这没有用。

我不知道更改会有多大，但是我建议我们远离服务器端游标，并为表更新构建UPDATE语句。

抱歉，我帮不上什么忙。

BTWI在sp_cursor调用中找到以下信息：

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