无法打开BCP主机数据文件 [英] Unable to open BCP host data-file

问题描述

下面是BCP语句的示例. 我不习惯使用BCP，因此非常感谢您的帮助和坦诚

Below is an example of the BCP Statement. I'm not accustomed to using BCP so your help and candor is greatly appreciated

我也在将其与格式文件一起使用.

I am using it with a format file as well.

如果我从CMD提示符下执行，则可以正常工作，但从SQL收到错误. BCP语句全部在一行上，并且SQL Server代理作为本地系统运行. SQL Server和脚本位于同一系统上.

If I execute from CMD prompt it works fine but from SQL I get the error. The BCP statement is all on one line and the SQL Server Agent is running as Local System. The SQL server, and script are on the same system.

我运行了exec master..xp_fixeddrives C，45589 E，423686

I ran exec master..xp_fixeddrives C,45589 E,423686

我尝试将结果输出到C和E

I've tried output to C and E with the same result

EXEC xp_cmdshell 'bcp "Select FILENAME, POLICYNUMBER, INSURED_DRAWER_100, POLICY_INFORMATION, DOCUMENTTYPE, DOCUMENTDATE, POLICYYEAR FROM data.dbo.max" queryout "E:\Storage\Export\Data\max.idx" -fmax-c.fmt -SSERVERNAME -T

这是格式文件rmax-c.fmt

Here is the format file rmax-c.fmt

10.0

7

1      SQLCHAR             0       255     "$#Y#$"          1     FILENAME                               
2      SQLCHAR             0       40      ""               2     POLICYNUMBER                                 
3      SQLCHAR             0       40      ""               3     INSURED_DRAWER_100                           
4      SQLCHAR             0       40      ""               4     POLICY_INFORMATION                           
5      SQLCHAR             0       40      ""               5     DOCUMENTTYPE                                 
6      SQLCHAR             0       40      ""               6     DOCUMENTDATE                                 
7      SQLCHAR             0       8       "\r\n"           7     POLICYYEAR    

由于本文中的格式化，该格式文件的最后一列被切除，但该文档日期以外的每一列均读取SQL_Latin1_General_CP1_CI_AS.

Due to formating in this post the last column of the format file is cut off but reads SQL_Latin1_General_CP1_CI_AS for each column other that documentdate.

推荐答案

首先，通过执行简单的'dir c:*.*';排除xp_cmdshell问题；

First, rule out an xp_cmdshell issue by doing a simple 'dir c:*.*';

查看我的博客有关使用BCP导出文件的信息.

Check out my blog on using BCP to export files.

我的系统出现问题，无法找到BCP.EXE的路径.

I had problems on my system in which I could not find the path to BCP.EXE.

要么更改其硬编码的PATH变量.

Either change the PATH variable of hard code it.

下面的示例可用于Adventure Works.

Example below works with Adventure Works.

-- BCP - Export query, pipe delimited format, trusted security, character format
DECLARE @bcp_cmd4 VARCHAR(1000);
DECLARE @exe_path4 VARCHAR(200) = 
    ' cd C:\Program Files\Microsoft SQL Server\100\Tools\Binn\ & ';
SET @bcp_cmd4 =  @exe_path4 + 
    ' BCP.EXE "SELECT FirstName, LastName FROM AdventureWorks2008R2.Sales.vSalesPerson" queryout ' +
    ' "C:\TEST\PEOPLE.TXT" -T -c -q -t0x7c -r\n';
PRINT @bcp_cmd4;
EXEC master..xp_cmdshell @bcp_cmd4;
GO

在将SQL Server 2012的路径更改为\ 110 \并将数据库名称更改为[AdventureWorks2012]之前，我收到以下错误.

Before changing the path to \110\ for SQL Server 2012 and the name of the database to [AdventureWorks2012], I received the following error.

进行更改后，该代码可从SSMS正常工作.该服务在NT AUTHORITY \ Local Service下运行. SQL Server代理已禁用.输出文件已创建.

After making the changes, the code works fine from SSMS. The service is running under NT AUTHORITY\Local Service. The SQL Server Agent is disabled. The output file was created.

这篇关于无法打开BCP主机数据文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！