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
For more details, visit http://vijirajkumar.blogspot.com/2009/08/cannot-access-destination-table-error.html