Click here to Skip to main content
15,886,836 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am performing sqlbulkcopy reading from txt file. error I am getting is "cannot access the destination table 'marketdetails".
Is there any thing to change in connection string but I don't think so because the connection is opening.
Please help me
DataTable dt = new DataTable();
           string line = null;
           int i = 0;

           using (StreamReader sr = File.OpenText("C:\\maq\\" + main1.text + "\\" + main1.text + ".txt))
           {
               while ((line = sr.ReadLine()) != null)
               {
                   string[] data = line.Split(',');
                   if (data.Length > 0)
                   {
                       if (i == 0)
                       {
                           foreach (var item in data)
                           {
                               dt.Columns.Add(new DataColumn());
                           }
                           i++;
                       }
                       DataRow row = dt.NewRow();
                       row.ItemArray = data;
                       dt.Rows.Add(row);
                   }
               }
           }

          SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=c:\maq\" + main1.text + @"\" + main1.text + ".mdf;Trusted_Connection=Yes;User Instance=True;");

               {
               con.Open();
               using (SqlBulkCopy copy = new SqlBulkCopy(con))
               {
                   copy.ColumnMappings.Add(0, 0);
                   copy.ColumnMappings.Add(1, 1);
                   copy.ColumnMappings.Add(2, 2);
                   copy.ColumnMappings.Add(3, 3);
                   copy.ColumnMappings.Add(4, 4);
                   copy.ColumnMappings.Add(5, 5);
                   copy.ColumnMappings.Add(6, 6);
                   copy.DestinationTableName ="marketdetails";
                   copy.WriteToServer(dt);
Posted
Updated 20-Jan-14 10:41am
v2

What the error tells me is one of 2 things. Either you spelled the name of your table wrong (or at least doesn't match the one in the database) OR the connecting schema/user doesn't have read/insert access to the marketdetails table.

Most standards have table names with '_' between words like 'market_details'.

Edited: I found this talking about permissions for bulk copy
So it looks like you either need ALTER Table or need to use hints for constraints and triggers.

**********************************************************************
A bcp in operation minimally requires SELECT/INSERT permissions on the target table. In addition, ALTER TABLE permission is required if any of the following is true:

Constraints exist and the CHECK_CONSTRAINTS hint is not specified. Disabling constraints is the default behavior. To enable constraints explicitly, use the -h option with the CHECK_CONSTRAINTS hint.

Triggers exist and the FIRE_TRIGGER hint is not specified. By default, triggers are not fired. To fire triggers explicitly, use the -h option with the FIRE_TRIGGERS hint.

You use the -E option to import identity values from a data file.
 
Share this answer
 
v2
Comments
hussain548 20-Jan-14 16:53pm    
the name of table is correct. Ya I agree format of table name is not standard.
In the other querry I am inserting data into the table but from the sqlbulkcopy it is giving me error.
bowlturner 20-Jan-14 16:57pm    
Then I would guess it's permissions somehow. It is also possible that the user you are connecting as doesn't have 'sqlbuldcopy' permissions.
hussain548 20-Jan-14 16:59pm    
Ya ok I appreciate your answer but how to get that permission. If you know please share your knowledge
bowlturner 20-Jan-14 17:08pm    
I added more my answer. Hope it helps.
I guess, In the connection string there is no catalog ( database name ) information Please check the string.

Try like this

C#
copy.DestinationTableName ="dbo.DataBaseName.marketdetails";
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900