1

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.

MoonKnight
  • 23,214
  • 40
  • 145
  • 277

2 Answers2

2

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 + 
...
Ed Harper
  • 21,127
  • 4
  • 54
  • 80
  • I have created a stored proceedure with the large select query. I am now running it using `declare @sql varchar(8000) select @sql = 'bcp "EXEC ispsCreateDrgData;" queryout "M:\\Nhcdc\\R14TData\\DRGData.txt" -c -t -T -S' + @@servername exec master..xp_cmdshell @sql` but it is not recognising my sp 'ispsCreateDrgData', what am I doing wrong here? – MoonKnight Jun 12 '12 at 11:11
  • @Killercam - bcp will connect to the default database for the login. Try fully qualifying the SP name (`dbname.schemaname.ispsCreateDrgData`), or if you're on SQL 2008, specify the database name at the command line with the `-d` flag. – Ed Harper Jun 12 '12 at 12:43
  • I am not running from the command line. I have attempted to fully qualify the name and this makes no difference. Do you have anymore ideas as to why I am getting this [typically cryptic SQL] error? Thanks again for your time. – MoonKnight Jun 12 '12 at 13:00
  • @Killercam - can you update the question with the error message and the command you're using? – Ed Harper Jun 12 '12 at 14:01
  • I have solved this issue by using a VIEW instead of a stored procedure. – MoonKnight Jun 12 '12 at 15:05
1

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
Michał Powaga
  • 22,561
  • 8
  • 51
  • 62
  • This is not an option due to the shear size of the data set, however, we have considered writing to a 'temporary' table (non-##TempTable), then deleting this afterwards. It remains a vaiable option. Thanks for your time... – MoonKnight Jun 12 '12 at 10:58