xp_cmdshell查询长度太大 [英] xp_cmdshell Query Length Too Large

问题描述

所有，我需要将大型SQL表中的数据集写入.txt文件.为此，我选择使用xp_cmdshell.我一直用来创建Data.txt文件的查询是

All, I need to write a data set from a large SQL table to a .txt file. To do this I have chosen to use xp_cmdshell. The query I have been using to create the Data.txt file is

declare @sql varchar(8000) 
select @sql = 'bcp "SELECT /*Lots of field names here*/ ' +
'FROM [SomeDatabase]..TableName WHERE /*Some Long Where Clause*/" ' + 
'queryout "M:\\SomeDir\\SomeOtherDirectory\\Data.txt" -c -t -T -S' + @@servername 
exec master..xp_cmdshell @sql

我遇到的问题是我正在使用的SELECT查询超出了命令行施加的1024个字符限制.为了解决这个问题，我决定尝试使用sqlcmd尝试从文件中执行我需要的SQL查询，以查询长度消除错误.我已经尝试过以下查询

the problem I am having is that the SELECT query I am using exceeds the 1024 character limit imposed by the command line. To get around this I have decide to try and use sqlcmd to attempt to execute the SQL Query I need from a file, elliminating the error with the query length. I have tried the following query

DECLARE @DatabaseName VARCHAR(255)
DECLARE @cmd VARCHAR(8000)
SET @DatabaseName = 'SomeDatabase' 
SET @CMD = 'SQLCMD -E -S (localhost) -d ' + @DBName + 
    'i "M:\\SomeDir\\SomeOtherDirectory\\tmpTestQuery.sql"' 
EXEC master..xp_cmdshell @CMD 

其中"tmpTestQuery.sql"保存了我要执行的长查询，但是出现以下错误

where 'tmpTestQuery.sql' holds the long query I want to execute, but I get the following errors

HResult 0x2AF9, Level 16, State 1
TCP Provider: No such host is known.
NULL
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or instance-
    specific error has occurred while establishing a connection to SQL Server. 
    Server is not found or not accessible. Check if instance name is correct and 
    if SQL Server is configured to allow remote connections. 
    For more information see SQL Server Books Online..
Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.
NULL

我启用了远程连接.

我想知道我在做错什么，并且在使用xp_cmdshell时是否还有其他解决查询长度问题的方法?

感谢您的时间.

注意.最终将通过C#调用此查询，因此计划是将很长的查询写入一个临时.txt文件，使用概述的方法执行该查询，并在完成后删除.

推荐答案

一种解决BCP限制的方法是将复杂查询包装在视图或存储过程中，然后让BCP命令查询该对象.

One way to get around the BCP limitation is to wrap the complex query in a view or stored procedure, then have the BCP command query that object.

由于localhost周围的括号，您的SQLCMD可能无法正常工作.试试:

Your SQLCMD may not work because of the brackets around localhost. Try:

...
SET @CMD = 'SQLCMD -E -S localhost -d ' + @DBName + 
...

这篇关于xp_cmdshell查询长度太大的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！