SqlBulkcopy .net 4.0 cannot access destination table

ado.net c# sqlbulkcopy

Question

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);
                        }
                    }
                }
            }
        }
    }
1
0
3/2/2015 6:01:28 AM

Popular Answer

Write the Schema before the table Name

Change

bulkCopy.DestinationTableName = "testTable";

to

bulkCopy.DestinationTableName = "dbo.testTable";
1
10/16/2017 1:56:58 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow