Cannot access destination table 'table-name' Error – During SqlBulkCopy

Problem Description:

The above error cannot access destination table might occur when bulk copying data to Sql server table using SqlBulkCopy.

Possible Reasons:

1. If the destination table name contains spaces

2. If the destination table name contains - (hyphen) symbol

Example:

Using bulkCopy As SqlBulkCopy = _

New SqlBulkCopy(targetConnectionString, SqlBulkCopyOptions.TableLock)

bulkCopy.DestinationTableName = "abb po" '- This will Fail –Bcoz the table name contains space.

bulkCopy.DestinationTableName = "abb-po" '- This will Fail –Bcoz the table name contains space.

Try

' Write from the source to the destination.

bulkCopy.WriteToServer(OdbcDr)

Catch ex As Exception

Console.WriteLine(ex.Message)

End Try

End Using

Solution Description:

The table name should be preceded and followed by "[" and "]" respectively.

Example:

Using bulkCopy As SqlBulkCopy = _

New SqlBulkCopy(targetConnectionString, SqlBulkCopyOptions.TableLock)

bulkCopy.DestinationTableName = "[abb po]" 'Correct Code

bulkCopy.DestinationTableName = "[abb-po]" 'Correct Code

Try

' Write from the source to the destination.

bulkCopy.WriteToServer(OdbcDr)

Catch ex As Exception

Console.WriteLine(ex.Message)

End Try

End Using