sqlbulkcopy无法读取表 [英] sqlbulkcopy failed to read table

问题描述

我正在从txt文件中执行sqlbulkcopy读取。我得到的错误是无法访问目标表'marketdetails。

连接字符串是否有任何改变但我不这么认为，因为连接正在打开。

请帮助我

 DataTable dt = new DataTable（）; 
 string line = null; 
 int i = 0; 
 
使用（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项）
 {
 dt.Columns.Add（new DataColumn（））; 
} 
 i ++; 
} 
 DataRow row = dt.NewRow（）; 
 row.ItemArray = data; 
 dt.Rows.Add（row）; 
} 
} 
} 
 
 SqlConnection con = new SqlConnection（@Data Sou rce =。\ 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）; 
 

解决方案

错误告诉我的是两件事之一。您拼写错误的表名称（或者至少与数据库中的名称不匹配）或连接架构/用户没有对marketdetails表的读/插入访问权限。



大多数标准的表格都在'market_details'之类的单词之间带有'_'。



编辑：我发现这里谈论批量复制的权限

所以看起来你需要使用ALTER Table或者需要使用约束和触发器的提示。



************************************************ **********************

运行中的bcp最低限度要求目标表上的SELECT / INSERT权限。此外，如果满足以下任何条件，则需要ALTER TABLE权限：



存在约束并且未指定CHECK_CONSTRAINTS提示。禁用约束是默认行为。要明确启用约束，请将-h选项与CHECK_CONSTRAINTS提示一起使用。



触发器存在且未指定FIRE_TRIGGER提示。默认情况下，不会触发触发器。要显式触发触发器，请将-h选项与FIRE_TRIGGERS提示一起使用。



使用-E选项从数据文件中导入标识值。

我想，在连接字符串中没有目录（数据库名称）信息请检查字符串。



试试这样

 copy.DestinationTableName =    dbo.DataBaseName  marketdetails; 

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);

解决方案

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.

I guess, In the connection string there is no catalog ( database name ) information Please check the string.

Try like this

copy.DestinationTableName ="dbo.DataBaseName.marketdetails";

这篇关于sqlbulkcopy无法读取表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！