using BCP to export stored procedure result in SQL Server 2008

sql bcp xp-cmdshell
Heba Gomaah picture Heba Gomaah · Jun 19, 2012 · Viewed 37.9k times · Source

Heyy,

I'm trying to use BCP to export a SP result to a text file using this query:

EXEC xp_cmdshell 'bcp "exec asmary..usp_Contract_SelectByEmpId -1,1" queryout "C:\test.txt" -w -C OEM -t$ -T -r ~ -S heba\HEBADREAMNET '

The output of this query is telling this error:

Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'where'.

even thought I'm sure that the stored procedure "usp_Contract_SelectByEmpId" is working correctly.

Anyone faced that kind of error before?

Answer

Alex Iurovetski picture Alex Iurovetski · Oct 25, 2012

  1. As Lynn suggested, check your stored procedure. It looks like the issue is within that.

  2. Ensure any plain SELECT works (e.g., C: drive is database server's local drive, not necessarily your own local drive).

  3. If the first two items work fine, then add SET FMTONLY OFF as follows:

EXEC xp_cmdshell 'bcp "set fmtonly off exec asmary..usp_Contract_SelectByEmpId -1,1" queryout "C:\test.txt" -w -C OEM -t$ -T -r ~ -S heba\HEBADREAMNET'

I have to admit that when I tried similar on my computer it failed with 'Function sequence error', and I found that it is related to a SQL Server 2008 bug fixed in 2011.

Please note also that even without SET FMTONLY OFF everything works with BCP library (odbcbcp.dll/odbcbcp.lib). So, you can have much more generic ODBC-wide bcp solution if you write your own wrapper executable (for instance, in C or C++).

I also found the following at http://msdn.microsoft.com/en-us/library/ms162802.aspx

The query can reference a stored procedure as long as all tables referenced inside the stored procedure exist prior to executing the bcp statement. For example, if the stored procedure generates a temp table, the bcp statement fails because the temp table is available only at run time and not at statement execution time. In this case, consider inserting the results of the stored procedure into a table and then use bcp to copy the data from the table into a data file.

Please see also my later separate reply - I think the whole concept of using stored procedure for BCP/queryout is wrong.

Related questions

xp_cmdshell Native Error 208, BCP in SQL Server 2008 R2

I've been trying to work on taking the result of a large and multiply-joined SELECT statement, and email the query result as a CVS file. I have the query correct and the emailing down, but I'm having trouble automating the …

Read More
Unable to open BCP host data-file

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. If I execute from CMD prompt it works …

Read More
Use bcp to import csv file to sql 2005 or 2008

I have a csv file and i need to import it to a table in sql 2005 or 2008. The column names and count in the csv are different from the table column names and count. The csv is splitted by a …

Read More