在SQL Server 2008中使用BCP导出存储过程的结果 [英] using BCP to export stored procedure result in SQL Server 2008
问题描述
嘿，
我正在尝试使用BCP通过以下查询将SP结果导出到文本文件中:
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:
错误= [Microsoft] [SQL Server Native Client 10.0] [SQL Server]关键字'where'附近的语法不正确.
Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near the keyword 'where'.
甚至以为我确定存储过程"usp_Contract_SelectByEmpId"可以正常工作.
even thought I'm sure that the stored procedure "usp_Contract_SelectByEmpId" is working correctly.
有人遇到过这样的错误吗?
Anyone faced that kind of error before?
推荐答案
-
按照Lynn的建议，检查您的存储过程.看来问题出在其中.
As Lynn suggested, check your stored procedure. It looks like the issue is within that.
确保任何普通的SELECT都能正常工作(例如C:驱动器是数据库服务器的本地驱动器，不一定是您自己的本地驱动器).
Ensure any plain SELECT works (e.g., C: drive is database server's local drive, not necessarily your own local drive).
如果前两项正常工作，则按如下所示添加SET FMTONLY OFF:
If the first two items work fine, then add SET FMTONLY OFF as follows:
EXEC xp_cmdshell'bcp将fmtonly设置为关闭exec asmary..usp_Contract_SelectByEmpId -1,1"查询出"C:\ test.txt" -w -C OEM -t $ -T -r〜-S heba \ HEBADREAMNET'
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'
我必须承认，当我在计算机上尝试类似操作时，它失败并显示功能序列错误"，并且我发现它与2011年修复的SQL Server 2008错误有关.
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.
还请注意，即使没有将SET FMTONLY OFF都关闭，所有内容都可以与BCP库一起使用(odbcbcp.dll/odbcbcp.lib).因此，如果您编写自己的包装可执行文件(例如，使用C或C ++)，则可以拥有更为通用的ODBC范围内的bcp解决方案.
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++).
我还在 http://msdn.microsoft.com/上找到了以下内容zh-CN/library/ms162802.aspx
只要执行bcp语句之前存在该存储过程内引用的所有表，该查询就可以引用该存储过程.例如，如果存储过程生成了一个临时表，则bcp语句将失败，因为该临时表仅在运行时可用，而在语句执行时不可用.在这种情况下，请考虑将存储过程的结果插入表中，然后使用bcp将表中的数据复制到数据文件中.
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.
也请参阅我以后的单独答复-我认为将存储过程用于BCP/查询输出的整个概念是错误的.
Please see also my later separate reply - I think the whole concept of using stored procedure for BCP/queryout is wrong.
这篇关于在SQL Server 2008中使用BCP导出存储过程的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
