I have written a program that in .net that should copy tables data from one server to another. However I am getting an error:
cannot access destination table "mytable"
Despite googling and looking everywhere I cannot find a solution to the error I am getting
Some posts mentions permissions and I have done the following:
GRANT SELECT, UPDATE, DELETE, INSERT TO bulkadmin
but still no success.
Am I missing the obvious?
Help is greatly appreciated.
EDIT
I bulk copy 3 databases with 1000 tables to 01 "target" database. I have simplified the code that I use and also tested with no luck.The intention is todo in Parallel ,but I want to get it working with a simple table first
private void TestBulkCopy(string sourceServer, string sourceDatabase, List<string> sourceTables)
{
string connectionStringSource = ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString;
string connectionStringTarget = ConfigurationManager.ConnectionStrings["TestDB"].ConnectionString;
string sqlGetDataFromSource = string.Format("SELECT * FROM {0}", "testTable");
using (var sourceConnection = new SqlConnection(connectionStringSource))
{
sourceConnection.Open();
using (var cmdSource = new SqlCommand(sqlGetDataFromSource, sourceConnection))
using (SqlDataReader readerSource = cmdSource.ExecuteReader())
{
using (var sqlTargetConnection = new SqlConnection(connectionStringTarget))
{
sqlTargetConnection.Open();
using (var bulkCopy = new SqlBulkCopy(sqlTargetConnection, SqlBulkCopyOptions.TableLock, null))
{
bulkCopy.DestinationTableName = "testTable";
bulkCopy.SqlRowsCopied += OnSqlRowsCopied;
bulkCopy.BatchSize = 2600;
bulkCopy.NotifyAfter = 50;
bulkCopy.BulkCopyTimeout = 60;
bulkCopy.WriteToServer(readerSource);
}
}
}
}
}
}
Write the Schema before the table Name
Change
bulkCopy.DestinationTableName = "testTable";
to
bulkCopy.DestinationTableName = "dbo.testTable";