SqlBulkCopy cannot access table

Question

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:

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);
 }
}

• Fionnuala: You should add more relevant tags, such as c# and a database, eg sql-server, ms-access -- sql is a generic tag and access is almost meaningless.

Answer 1

My issue was a bit different, turns out my table name was a reserved keyword in SQL so I had to do the following:

bulkCopy.DestinationTableName = $"{schema}.[{tableName}]";

Where schema is the target schema and tableName the target table name

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])

• bluedot: Yep, this was the problem for me also. From msdn: 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>]).
• Tjaart van der Walt: thanks @bluedot I have updated my answer with you comment details
• SND: I decided to change the accepted answer to this one as it is more complete and references the official docs. Also, judging by the upvotes, it seems to be of more help in general than the previously accepted answer.

Answer 2

Check that user that connects to db has

GRANT ALTER ON [dbo].[TABLE_XXX] TO [appuser] 

as suggested in answer by Jhilden on MSDN forum.

• user3183411: This didn't solve my issue. I still can't access the tables even though the user is database owner

Answer 3

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.

• Saeid Ostad: according to Microsoft docs, the bulk copy needs Select and Insert permission.

Answer 4

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.

• SND: My connectionstring is: string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TestDB.mdf;User Instance=True;Integrated Security=True;"

Answer 5

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.KeepIdentity as option, the connection user needs the Grant Alter right, if he doesn't, you will get this not very helpful error message.

options one has:

• remove Identity from the destination table
• grant Alter right on destination table for that user
• not use KeepIdentity

(this is an extension of Fosna's answer but 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).

Answer 6

Bulkcopy expects the table to exists in the database. Also you should have access to this database or table.

Answer 7

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.

Answer 8

Andrij Ferents answer is valid.

The destination table must exist before calling SQLBulkCopy. It is a common newbie mistake.

Answer 9

In my case, it's not a permission problem, but a special char in the table name problem ( parenthesis and & ).

Hope this helps

Answer 10

In my case, the problem was because of an existing Identity column

Answer 11

I resolved this error by changing the temp table to #TmpTable, once I changed the name in DestinationTableName it worked!!

bulkcopy.DestinationTableName = "#TmpTable";

Previously I had the temp table as "#TmpTableUpd"