使用不同连接的传递查询和链接表查询 [英] Pass-through queries and linked table queries using different connections

问题描述

_{后续问题的后续步骤：强制SET IDENTITY_INSERT从MS Access更快地生效}

我正在从MS Access后端升级到SQL Server后端。许多表具有自动编号主键。我试图填充他们如下（伪代码）：

  ExecutePassThruSET IDENTITY_INSERT dbo.Accounts ON
 db.ExecuteINSERT INTO sql_Accounts SELECT * FROM mdb_Accounts
 ExecutePassThruSET IDENTITY_INSERT dbo.Accounts OFF
  

$ b b

在上面的代码中， sql_Accounts 是连接到SQL Server中的Accounts表和 mdb_Accounts 是连接到mdb中的Accounts表的链接表。此错误会失败，

$ IDATITY_INSERT设置为OFF时，

无法在表'Accounts'中的identity列中插入显式值。

我启动SQL Server Profiler来尝试找出问题，问题是我的示例中的每行代码可能使用或可能不使用不同的服务器进程ID（SPID）。

是否有某种方法可以确保它们都使用相同的连接？

p>

解决方案

可能有一个更优雅的解决方案，但这是一次性转换，它不值得太深的潜水尝试做复杂的事情

1. 在SQL Server上创建一个不包含标识但是具有相同模式的登台表。 li>
   
2. 将数据加载到该表中


3. 将您的身份完全插入SQL Server端。

_{Follow-on to an earlier question: Force SET IDENTITY_INSERT to take effect faster from MS Access}

I'm in the process of upsizing from an MS Access backend to a SQL Server backend. Many of the tables have autonumber primary keys. I'm trying to populate them as follows (pseudocode):

ExecutePassThru "SET IDENTITY_INSERT dbo.Accounts ON"
db.Execute "INSERT INTO sql_Accounts SELECT * FROM mdb_Accounts"
ExecutePassThru "SET IDENTITY_INSERT dbo.Accounts OFF"

In the above code, sql_Accounts is a linked table connected to the Accounts table in SQL Server and mdb_Accounts is a linked table connected to the Accounts table in the mdb. This fails with the error,

Cannot insert explicit value for identity column in table 'Accounts' when IDENTITY_INSERT is set to OFF.

I fired up SQL Server Profiler to try to figure out the problem and the issue is that each line of code in my sample may or may not use a different Server Process ID (SPID). In other words, they are using different connections to the backend.

Is there some way to ensure they all use the same connection?

解决方案

There may be a more elegant solution, however this a one time conversion and it's not worth too deep of dive trying to do complex things with connection reuse.

1. Create a staging table on the SQL Server that does not contain an identity but is otherwise of the same schema.
2. Load your data to that table
3. Do your identity insert entirely on the SQL Server side.

这篇关于使用不同连接的传递查询和链接表查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！