使用SQL Server标识列访问错误 [英] Access Bug with SQL Server identity columns
问题描述
我不知道之前是否报告过这个问题，但是它似乎是一个带有Access的
错误。
如果我创建了两个表两者都有一个标识列然后在table1上创建一个
插入触发器，将相关记录插入到table2中，现在
在table1上创建一个表格，在table2上有一个子表单。将记录插入
主要形式的内容，一切都很好，每个主要的
记录自动获取子记录，到目前为止身份
列是不错的，顺序没有间隙，这对于测试下一位有利于
（连续性中的漏洞可能不会显示为
问题很容易）
现在截断table2（注意：使用truncate table使身份种子
重置）
现在在表单中输入一条新记录，当它保存时，它将跳回
到记录1，实际上它认为（根据导航栏）它是在
最后一条记录，但数据来自id为1的记录。再来一次
它会跳到记录2等等。
这里发生的事情是，Access似乎从服务器检索了
@@ IDENTITY，其中包含了插入的$ 2
的table2的ID触发。如果table2的id不存在于table1中，
则主表单保留在刚刚插入的记录中（奇怪的是，我预计它会全部#deleted在我身上。
只有当触发器将ID插入子表中时才会出现问题
在主表中也作为ID存在，所以你可以看到你可能
从来没有遇到过这个问题。
我不能说内部的Access是什么但看起来它正在使用
@@ IDENTITY当修复此问题时，它将使用SCOPE_IDENTITY（）。
所以我有一个错误，可能的原因和修复，谁做的我发送给那个
我不想向我收取70英镑的特权吗？
< blockquote> ADP或MDB和ODBC /其他什么？
ADP或MDB和ODBC /其他什么？
< br>
2005年8月5日03:25:29 -0700，Trevor Best
< ;去********** @ besty.org.uk>写道：
尝试使用SQL Server 2005的测试版重现。然后可能
MSFT会感兴趣。我很确定有一种方法可以让b $ b参与测试计划。
OTOH这可能是严格的Access错误，而不是SQL Server。
您的桌子是否为1：M关系？这就是经常使用的子格式最多的
。
-Tom。
我不知道我不知道之前是否已经报告了这个问题，但它似乎是Access的一个错误。
如果我创建两个带有标识列的表，那么创建一个
插入触发器在table1上将相关记录插入到table2中，现在在table1上创建一个表格，在table2上创建一个子表单。将记录插入到主要表单的内容中，一切都很好，每个主要的记录自动获取一个子记录，到目前为止，身份
列很好，顺序没有间隙，这有利于测试下一位（顺序中的间隙孔可能不会轻易显示
问题）
现在截断table2（注意：使用truncate表，以便身份种子重置）
现在在表单中输入一条新记录，当它保存时，它会跳回来记录1，实际上它认为（根据导航栏）它在最后一条记录上，但数据来自id为1的记录。再次它将跳转到记录2等等。 />
这里发生的事情是Access似乎从服务器检索
@@ IDENTITY，其中包含触发器插入的table2的ID。如果table2的id不存在于table1中，那么主表单就会停留在刚插入的记录中（奇怪的是，我预测它会全部#deleted在我身上）。
问题只有在触发器将ID插入子表中时才会出现，该子表也作为主表中的ID存在，因此您可以看到您可能从未遇到过该问题。 />
我不能说内部的Access是什么，但它似乎正在使用
@@ IDENTITY，当它修复它时，它将使用SCOPE_IDENTITY（）。
所以我有一个错误，可能的原因和解决方法，我将它发送给谁
我不想向我收取70英镑的特权？
I don''t know if this has been reported before but it appears to be a
bug with Access.
If I create two tables both with an identity column then create an
insert trigger on table1 that inserts a related record into table2, now
create a form on table1 with a subform on table2. Insert records into
the main form to your heart''s content and everything''s fine, each main
record automatically gets a child record and so far the identity
columns are nice and sequential with no gaps, which is good for testing
the next bit (gaping holes in the sequentialness might not show up the
problem easily)
Now truncate table2 (note: use truncate table so that the identity seed
is reset)
Now enter a new record into the form, when it saves, it will jump back
to record 1, well actually it thinks (according to the nav bar) it''s on
the last record but the data is from the record with the id of 1. do it
again it will jump to record 2, etc.
What''s happening here is that Access appears to be retrieving
@@IDENTITY from the server and that contains the ID of table2 that was
inserted by the trigger. If the id of table2 doesn''t exist in table1,
then the main form stays at the record just inserted (strange, I
predicted it would go all #deleted on me).
The problem only arises if the trigger inserts an ID into the sub table
that also exists as an ID in the main table so you can see you might
never come across the problem.
I can''t say what Access does internally but it appears it''s using
@@IDENTITY when a fix for this would be for it to use SCOPE_IDENTITY().
So I have a bug, probable cause and a fix, who do I send that to that
doesn''t want to charge me ￡70 for the privilege?
ADP or MDB and ODBC/whatever?
ADP or MDB and ODBC/whatever?
On 5 Aug 2005 03:25:29 -0700, "Trevor Best"
<go**********@besty.org.uk> wrote:
Try to reproduce using the beta version of SQL Server 2005. Then maybe
MSFT would be interested. I''m pretty sure there is a way to
participate in the beta program.
OTOH this may be strictly an Access bug, and not a SQL Server one.
Were your tables in a 1:M relation? That''s what subforms are most
often used for.
-Tom.
I don''t know if this has been reported before but it appears to be a
bug with Access.
If I create two tables both with an identity column then create an
insert trigger on table1 that inserts a related record into table2, now
create a form on table1 with a subform on table2. Insert records into
the main form to your heart''s content and everything''s fine, each main
record automatically gets a child record and so far the identity
columns are nice and sequential with no gaps, which is good for testing
the next bit (gaping holes in the sequentialness might not show up the
problem easily)
Now truncate table2 (note: use truncate table so that the identity seed
is reset)
Now enter a new record into the form, when it saves, it will jump back
to record 1, well actually it thinks (according to the nav bar) it''s on
the last record but the data is from the record with the id of 1. do it
again it will jump to record 2, etc.
What''s happening here is that Access appears to be retrieving
@@IDENTITY from the server and that contains the ID of table2 that was
inserted by the trigger. If the id of table2 doesn''t exist in table1,
then the main form stays at the record just inserted (strange, I
predicted it would go all #deleted on me).
The problem only arises if the trigger inserts an ID into the sub table
that also exists as an ID in the main table so you can see you might
never come across the problem.
I can''t say what Access does internally but it appears it''s using
@@IDENTITY when a fix for this would be for it to use SCOPE_IDENTITY().
So I have a bug, probable cause and a fix, who do I send that to that
doesn''t want to charge me ￡70 for the privilege?
这篇关于使用SQL Server标识列访问错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
