xp_cmdshell Query Length Too Large
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
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
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
I have remote connections enabled.
I would like to know what I am doing wrong, and if there is another way around the problem I am having with the query length when using xp_cmdshell?
Thanks for your time.
Note. This query will eventually be called from C#, so the plan was to write the very long query to a temporary .txt file, execute it using the method outlined and delete when finished.
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.
Your SQLCMD may not work because of the brackets around localhost. Try:
... SET @CMD = 'SQLCMD -E -S localhost -d ' + @DBName + ...
You can insert desired data into global temp table (##temp_table) and then use it as source:
declare @sql varchar(8000) select @sql = 'bcp "SELECT * FROM ##temp_table" ' + 'queryout "M:\\SomeDir\\SomeOtherDirectory\\Data.txt" -c -t -T -S' + @@servername exec master..xp_cmdshell @sql