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

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. — Fionnuala, Jan 17 '12 at 12:37

Tjaart van der Walt · Accepted Answer · 2017-09-15T05:23:35.990

16

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

edited Sep 15 '17 at 05:23

answered Jun 21 '17 at 13:58

Tjaart van der Walt

5,149
2
30
50

2

Yep, this was the problem for me also. From msdn: DestinationTableName is a three-part 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 ([..]). – bluedot Sep 14 '17 at 19:19
thanks @bluedot I have updated my answer with you comment details – Tjaart van der Walt Sep 15 '17 at 05:23
1

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. – SND Dec 14 '18 at 09:06

score 6 · Answer 2 · answered Nov 20 '13 at 13:49

6

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.

answered Nov 20 '13 at 13:49

Fosna

2,050
3
22
23

This didn't solve my issue. I still can't access the tables even though the user is database owner – user3183411 Mar 07 '18 at 17:56

score 5 · Answer 3 · answered Feb 14 '12 at 20:31

5

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.

answered Feb 14 '12 at 20:31

user132936

104
4

according to [Microsoft docs](https://learn.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-ver15#permissions), the bulk copy needs Select and Insert permission. – Saeid Ostad May 24 '21 at 15:50

score 2 · Answer 4 · answered Jan 23 '20 at 14:36

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

score 2 · Answer 5 · answered Jan 17 '12 at 22:13

2

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.

answered Jan 17 '12 at 22:13

Carl Bergquist

3,894
2
25
42

My connectionstring is: string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\TestDB.mdf;User Instance=True;Integrated Security=True;"; – SND Jan 18 '12 at 08:58

score 1 · Answer 6 · answered Dec 07 '15 at 12:13

1

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

answered Dec 07 '15 at 12:13

Jack

29
3

score 1 · Answer 7 · answered Aug 09 '16 at 06:02

1

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.

answered Aug 09 '16 at 06:02

Nick Brown

11
1

score 1 · Answer 8 · edited Jun 21 '19 at 08:22

1

Andrij Ferents answer is valid.

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

edited Jun 21 '19 at 08:22

E_net4

27,810
13
101
139

answered Jun 21 '19 at 01:14

MarkF

123
1
10

score 0 · Answer 9 · answered Sep 21 '15 at 11:53

0

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

Hope this helps

answered Sep 21 '15 at 11:53

Guillaume Martin

103
7
18

score 0 · Answer 10 · answered Sep 25 '19 at 07:13

0

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

answered Sep 25 '19 at 07:13

Matt Qafouri

1,449
2
12
26

SqlBulkCopy cannot access table

10 Answers10

Linked