SQL Server BCP命令不能将NVARCHAR（MAX）变量内容写入文件 [英] SQL Server BCP Command not Writing NVARCHAR(MAX) Variable Contents to File

问题描述

我遇到了SQL Server的 BCP 问题。我没有收到错误消息，但该命令也没有写入任何指定的路径。

这是我的代码：

在这种情况下，下面代码中没有考虑的唯一变量是 @MESSAGE 它是一个 NVARCHAR（MAX），并占用大部分空间，所以 VARCHAR（8000）不会太大。

  DECLARE @OUTPUT_TABLE VARCHAR（255） ='## temp'+ CONVERT（VARCHAR（12），CONVERT（INT，RAND（）* 1000000））
 DECLARE @RESULT INTEGER 
 DECLARE @OUTPUT_EXECUTION_COMMAND NVARCHAR（MAX）='CREATE TABLE [ + @OUTPUT_TABLE +']（CONTENT NVARCHAR（MAX））
 
 INSERT INTO ['+ @OUTPUT_TABLE +'] 
 SELECT @MESSAGE'
 
 EXECUTE SP_EXECUTESQL @ OUTPUT_EXECUTION_COMMAND，N'@MESSAGE NVARCHAR（MAX）'，@MESSAGE 
 
 DECLARE @OUTPUT_FILE_WRITE_COMMAND VARCHAR（1000）='BCPSELECT CONTENT FROM ['+ @OUTPUT_TABLE +']
 QUERYOUT'+ @FULL_PATH +'\'+ @FILE_NAME +'-w -T -S'+ @@ servername 
 
 PRINT @OUTPUT_FILE_WRITE_COMMAND 
 
 EXECUTE @RESULT = master.sys.xp_cmdshell @OUTPUT_FILE_WRITE_COMMAND，NO_OUTPUT 
 
 PRINT @RESULT 
 
 EXECUTE（'DROP TABLE ['+ @OUTPUT_TABLE +']'）
  

所以，令人沮丧的是我收到一个错误，但是我的文件没有被写入。
$ b

PRINT @OUTPUT_FILE_WRITE_COMMAND 的输出是：



  BCPSELECT CONTENT FROM [## temp878274]
 QUERYOUT\\TXPDC-FS01\Profiles\cofarmer\My Sandbox\THCIC\Q2_2014 \Burleson \PIPSUB2938718184092014251607.txt-w -T -S TXPDC-STKSQL01 
  

PRINT @RESULT 是： 1

我正在做什么错？

解决方案

看起来问题是在 @OUTPUT_FILE_WRITE_COMMAND （和错误的双引号）删除换行符为我工作：

  DECLARE @OUTPUT_FILE_WRITE_COMMAND VARCHAR（1000）='BCPSELECT CONTENT FROM ['+ @OUTPUT_TABLE +']QUERYOUT'+ @FULL_PATH +'\'+ @ FILE_NAME +'-w -T -S'+ @@ servername 
  

结果： 0，并创建了文件。

I'm having issues with SQL Server's BCP. I'm not getting an error message but the command is also not writing anything to the path specified.

Here is my code:

In this scenario, the only variable not accounted for in the code below is @MESSAGE which is a NVARCHAR(MAX) and will take up most of that space so VARCHAR(8000) won't be nearly big enough.

DECLARE @OUTPUT_TABLE VARCHAR(255) = '##temp' + CONVERT(VARCHAR(12), CONVERT(INT, RAND() * 1000000))
DECLARE @RESULT INTEGER
DECLARE @OUTPUT_EXECUTION_COMMAND NVARCHAR(MAX) = 'CREATE TABLE [' + @OUTPUT_TABLE + '] ( CONTENT NVARCHAR(MAX) )

                                                    INSERT INTO [' + @OUTPUT_TABLE + ']
                                                        SELECT @MESSAGE'

EXECUTE SP_EXECUTESQL @OUTPUT_EXECUTION_COMMAND, N'@MESSAGE NVARCHAR(MAX)', @MESSAGE

DECLARE @OUTPUT_FILE_WRITE_COMMAND VARCHAR(1000) = 'BCP "SELECT CONTENT FROM [' + @OUTPUT_TABLE + ']"
                                                      QUERYOUT "' + @FULL_PATH + '\' + @FILE_NAME + '" -w -T -S ' + @@servername 

PRINT @OUTPUT_FILE_WRITE_COMMAND

EXECUTE @RESULT = master.sys.xp_cmdshell @OUTPUT_FILE_WRITE_COMMAND, NO_OUTPUT

PRINT @RESULT

EXECUTE ( 'DROP TABLE [' + @OUTPUT_TABLE + ']' )

So, the frustrating part is that I'm receiving an error, yet my file is not being written.

The output of PRINT @OUTPUT_FILE_WRITE_COMMAND is:

BCP "SELECT CONTENT FROM [##temp878274]"
QUERYOUT "\\TXPDC-FS01\Profiles\cofarmer\My Sandbox\THCIC\Q2_2014\Burleson\PIPSUB2938718184092014251607.txt" -w -T -S TXPDC-STKSQL01

While the output of PRINT @RESULT is: 1

Yet nothing is being written. What am I doing wrong?

解决方案

Looks like the issue is the line break in the@OUTPUT_FILE_WRITE_COMMAND (and the erroneous double quote"). Removing the line break worked for me:

DECLARE @OUTPUT_FILE_WRITE_COMMAND VARCHAR(1000) = 'BCP "SELECT CONTENT FROM [' + @OUTPUT_TABLE + ']" QUERYOUT "' + @FULL_PATH + '\' + @FILE_NAME + '" -w -T -S ' + @@servername 

Result: 0 and the file was created.

这篇关于SQL Server BCP命令不能将NVARCHAR（MAX）变量内容写入文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！