C# SqlBulkCopy 无法访问表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8894071/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SqlBulkCopy cannot access table
提问by SND
After reading in an excel-sheet (to transferTable), I want to add that data to a new table (destinationTable) using SqlBulkCopy, but I'm getting the error:
在读取 excel 表（到 transferTable）后，我想使用 SqlBulkCopy 将该数据添加到新表（destinationTable），但出现错误：
Cannot access destination table 'test'
I've tried using the default tablename and using square brackets, but that didn't work.
我试过使用默认表名并使用方括号，但这没有用。
Any suggestions?
有什么建议？
private void writeToDBButton_Click(object sender, EventArgs e) {
MakeTable();
destinationTable.TableName = "test";
testDBDataSet.Tables.Add("test");
// Connects to the sql-server using Connection.cs
SqlConnection connection = Connection.GetConnection();
using (connection) {
connection.Open();
// Uses SqlBulkCopy to copy the data from our transferTable to the destinationTable
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection)) {
bulkCopy.DestinationTableName = destinationTable.TableName;
try {
// Write from the source to the destination.
bulkCopy.WriteToServer(transferTable);
this.dataGridView2.DataSource = destinationTable;
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
}
connection.Close();
}
}
}
private void saveDBButton_Click(object sender, EventArgs e) {
this.Validate();
this.usersBindingSource.EndEdit();
this.tableAdapterManager.UpdateAll(this.testDBDataSet);
}
private void MakeTable() {
for (int counter = 0; counter < columns; counter++) {
DataColumn dummy = new DataColumn();
dummy.DataType = System.Type.GetType("System.Double");
destinationTable.Columns.Add(dummy);
}
}
采纳答案by Tjaart van der Walt
My issue was a bit different, turns out my table name was a reserved keyword in SQL so I had to do the following:
我的问题有点不同，原来我的表名是 SQL 中的保留关键字，所以我必须执行以下操作：
bulkCopy.DestinationTableName = $"{schema}.[{tableName}]";
Where schemais the target schema and tableNamethe target table name
schema目标模式和tableName目标表名在哪里
From the documentation
从文档
DestinationTableName is a three-part name [database].[owningschema].[name]. You can qualify the table name with its database and owning schema if you choose. However, if the table name uses an underscore ("_") or any other special characters, you must escape the name using surrounding brackets as in ([database].[owningschema].[name_01])
DestinationTableName 是一个由三部分组成的名称 [database].[owningschema].[name]。如果您愿意，您可以使用其数据库和拥有的架构来限定表名。但是，如果表名使用下划线 ("_") 或任何其他特殊字符，则必须使用括号对名称进行转义，如 ([database].[owningschema].[name_01])
回答by Carl Bergquist
It seems that the user who executes this code don't have proper access to the database. * Check so that the user got access. * Check the connectionstring your using to connect to the database.
执行此代码的用户似乎没有对数据库的正确访问权限。* 检查以便用户获得访问权限。* 检查用于连接到数据库的连接字符串。
回答by user132936
I recently ran into this same error and came across this post while googling for an answer. I was able to solve the problem by giving the user that is executing the bulk copy command insert and select permissions on the destination table. Originally I had only granted insert permission to the user and got the 'Cannot access destination table' error.
我最近遇到了同样的错误，并在谷歌搜索答案时看到了这篇文章。我能够通过为执行批量复制命令的用户提供对目标表的插入和选择权限来解决该问题。最初我只授予用户插入权限并收到“无法访问目标表”错误。
回答by Fosna
Check that user that connects to db has
检查连接到 db 的用户是否具有
GRANT ALTER ON [dbo].[TABLE_XXX] TO [appuser]
as suggested in answer by Jhilden on MSDN forum.
正如 Jhilden 在MSDN 论坛上的回答所建议的那样。
回答by Guillaume Martin
In my case, it's not a permission problem, but a special char in the table name problem ( parenthesis and & ).
就我而言，这不是权限问题，而是表名问题（括号和 & ）中的特殊字符。
Hope this helps
希望这可以帮助
回答by Andrij Ferents
Bulkcopy expects the table to exists in the database. Also you should have access to this database or table.
Bulkcopy 期望该表存在于数据库中。此外，您应该有权访问此数据库或表。
回答by Nick Brown
Interestingly, this also happens if you have a table name which is purely numeric. Start the table name with one or more alpha characters and it works just fine.
有趣的是，如果您有一个纯数字的表名，也会发生这种情况。用一个或多个字母字符开始表名，它工作得很好。
回答by MarkF
Andrij Ferents answeris valid.
Andrij Ferents 的回答是有效的。
The destination table must exist before calling SQLBulkCopy. It is a common newbie mistake.
在调用之前目标表必须存在SQLBulkCopy。这是一个常见的新手错误。
回答by Majid
In my case, the problem was because of an existing Identity column
就我而言，问题是由于现有的 Identity 列
回答by Jan
I had the same issue stating
我有同样的问题说明
tablename-object not found or insufficient privileges.
未找到表名对象或权限不足。
It worked fine on my account but not on the end users account, where it gave this error. It turned out that if you run bulkcopy with SqlBulkCopyOptions.KeepIdentityas option, the connection user needs the Grant Alter right, if he doesn't, you will get this not very helpful error message.
它在我的帐户上运行良好，但在最终用户帐户上运行良好，因此出现此错误。事实证明，如果您使用SqlBulkCopyOptions.KeepIdentityas 选项运行 bulkcopy ，则连接用户需要 Grant Alter 权限，如果他不这样做，您将收到此不是很有帮助的错误消息。
options one has:
选项一有：
- remove Identity from the destination table
- grant Alter right on destination table for that user
- not use KeepIdentity
- 从目标表中删除标识
- 为该用户授予对目标表的更改权限
- 不使用 KeepIdentity
(this is an extension of Fosna's answerbut given the time it took me to identify the root cause I thought it might be worth to make this solution a bit more explicit).
（这是Fosna 答案的延伸，但考虑到我花了一些时间来确定根本原因，我认为让这个解决方案更明确一点可能是值得的）。
