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

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

Solution 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.

Solution 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.

Solution 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.

Solution 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).

View more solutions

Comments

[SND]

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

[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

[user3183411]

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

[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.

[Saeid Ostad]

according to Microsoft docs, the bulk copy needs Select and Insert permission.