3

I am trying to use SqlBulkCopy in order to insert plenty of rows in short time. I get the error:"

Cannot access destination table myTable.

Here is my code:

string conn = ConfigurationManager.ConnectionString["myConnection"].ToString();
DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn("Col1", typeof(string)));
dt.Columns.Add(new DataColumn("Col2", typeof(int)));
for (int i=0;i<10000;i++)
{
   DataRow dr = dt.NewRow();
   dr["Col1"] = "Col1_" + i.toString();
   dr["Col2"] = i;
}
using (SqlBulkCopy bulkSql = new SqlBulkCopy(conn))
{
   bulkSql.BatchSize = 10000;
   bulkSql.BulkCopyTimeout = 10000;
   bulkSql.ColumnMappings.Clear();
   bulkSql.ColumnMappings.Add("Col1", "Col1");
   bulkSql.ColumnMappings.Add("Col2", "Col2");
   bulkSql.DestinationTableName = "[myTable]"
   bulkSql.WriteToServer(dt);
}
d219
  • 2,707
  • 5
  • 31
  • 36
L. Achilles
  • 123
  • 1
  • 2
  • 14
  • Can you explain what isn't working with your code? – mjwills Jul 10 '17 at 13:46
  • 5
    Possible duplicate of [SqlBulkCopy cannot access table](https://stackoverflow.com/questions/8894071/sqlbulkcopy-cannot-access-table) – mjwills Jul 10 '17 at 13:47
  • I have full grants on that table and I already inserted data via linqtosql, but now I want to do it with bulk – L. Achilles Jul 10 '17 at 13:56
  • I declared it in web.config, but sorry it's not that I don't want to show it, it's that I am not allowed to show it. Any fast way to check my connection string if it is ok? – L. Achilles Jul 10 '17 at 14:09
  • You can't help me if you can't see my connection string? It's highly unprofessional to publish code that is used to a client and seriously unnecessary to see my connection string, because simply I can check if my connection string is valid and as I already did it is. Bulk is a step after inserting data, which is after executing queries in database, which is after connecting to a database. Clearly from the comments above you haven't payed that much attention on my description and title. Thank you for your time! – L. Achilles Jul 10 '17 at 14:27
  • bulkSql.DestinationTableName = "myTable"; < – Icculus018 Jul 10 '17 at 14:39
  • 1
    does the table definitely exist on `[dbo]` schema? Might be worth being explicit about your schema. – tigerswithguitars Jul 10 '17 at 14:40
  • @kristech Unfortunately, I already tried without brackets but didn't work. – L. Achilles Jul 10 '17 at 14:46
  • 1
    @tigerswithguitars The table exists in a schema that I created [mySchema], so I've tried: bulkSql.DestinationTableName = "[mySchema].[UploadData]"; but still didn't work. – L. Achilles Jul 10 '17 at 14:46
  • Can you try with less records say 500, if that works then increment it by another 500 rows and so on. Table lock may cause this issue. – Partha Thakura Jul 10 '17 at 14:53
  • 1
    And the connection string includes the db name? If the connection string connects to `master`, you will need to include that on the `DestinationTableName` as well. – tigerswithguitars Jul 10 '17 at 14:54
  • Double check that the user in the connection has both `select` and `insert` permissions as well. – tigerswithguitars Jul 10 '17 at 14:55
  • My apologies for not responding at all, but I couldnt have access to the pc till now. – L. Achilles Jul 11 '17 at 06:45
  • @ParthaThakura I did tried that right now, but I got the same error. – L. Achilles Jul 11 '17 at 06:48
  • @tigerswithguitars I have SELECT, INSERT, UPDATE, ALTER grants. About your previous comment, I am not quite sure that I know how to check that. Can you explain it to me please? – L. Achilles Jul 11 '17 at 06:50
  • 2
    @L.Achilles does the connection string contain a reference to either `Database={YourDatabaseName}` or `Initial Catalog={YourDatabaseName}` if not then you will need to add the db to the destination as well, i.e. `YourDatabaseName.YourSchema.YourTable`. – tigerswithguitars Jul 11 '17 at 08:47
  • @tigerswithguitars I use Initial Catalog={MyDatabaseName} – L. Achilles Jul 11 '17 at 09:32

1 Answers1

2

I finally have found the answer. My table name is in [MyDatabase].[mySchema].[myTable]. So in web.config my connection string correctly had Initial Catalog=MyDatabase; but I also needed: bulkSql.DestinationTableName = "[mySchema].[myTable]"; in order to run it succesfully. Thank you all for your help!

L. Achilles
  • 123
  • 1
  • 2
  • 14
  • Awesome, glad it worked. Would really appreciate if you vote for my comments above to that effect thanks :) . – tigerswithguitars Jul 11 '17 at 10:30
  • @tigerswithguitars Oops sorry that I haven't done this already. I am quite new in stackoverflow community. Actually I just checked it and I need 15 points of reputation and I have 13. Sorry! – L. Achilles Jul 11 '17 at 10:51