BCP...查询输出执行错误地返回已知数据存在的零行
我编写了一个 BCP 流程，其中包含 queryout 作为选项。结果，查询被执行，结果被（或应该）写入指定的输出文件。正在使用的查询已在 SQL 查询分析器（使用 MS SQL 2000）中得到确认，以生成已知的结果集。
但是，当我使用 BCP 命令执行批处理文件时，它将返回零行（得到响应“复制了 0 行”）。但是，我可以接受此查询并在 BCP 进程之外（在查询分析器中）运行它并获取 42,745 行。我还可以创建一个视图并执行一个更简单的查询，并使用 BCP...queryout 选项使其工作。我使用的查询是连接两个表中的信息：
bcp "select obj_id, loc_code, CONVERT(VARCHAR(20), create_date, 20) AS build_date,
model_id, (len(build_string)/4) as feature_count, build_string
from my_db..builds a, my_db..models b
where a.model_id = b.model_id and obj_id like '_________C%' and obj_id not like '1G0%'" queryout z:\test.txt -U %1 -P %2 -S SQLSVR\VM_PROD -c
如您所见，该查询比“select * from my_db..builds”更复杂。本质上，如果我使用更复杂的查询创建一个视图，然后使用一个简单、直接的查询运行 bcp...queryout，如前所述，从视图中检索数据，那么它可以正常工作。我不明白为什么更复杂的查询在 BCP 命令中不起作用。返回结果之前是否超时，或者 BCP 不知道如何处理复杂的“连接式”查询？
I have written a BCP process with queryout as option. As a result the query is exectuted and the results are (or should be) written to the designated output file. The query that is being used has been confirmed in SQL Query Analyzer (using MS SQL 2000) to generate a known result set.
However, when I execute the batch file with the BCP command it will return zero rows (get the response "0 rows copied"). However, I can take this query and run it outside of the BCP process (in query analyzer) and get 42,745 rows. I can also create a view and execute a simpler query and have it work using the BCP...queryout option. The query I am using is joining information from two tables:
bcp "select obj_id, loc_code, CONVERT(VARCHAR(20), create_date, 20) AS build_date,
model_id, (len(build_string)/4) as feature_count, build_string
from my_db..builds a, my_db..models b
where a.model_id = b.model_id and obj_id like '_________C%' and obj_id not like '1G0%'" queryout z:\test.txt -U %1 -P %2 -S SQLSVR\VM_PROD -c
As you can see the query is more complex that "select * from my_db..builds". Essentially if I create a view using the more complex query and then run the bcp...queryout with a simple, straightforward query as noted to retrieve the data from the view it works fine. I can't figure out though why the more complex query doesn't work in the BCP command. Could it be timing out before returning results, or is it that BCP doesn't know how to handle a complex "join-style" query?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我发现，如果您创建要执行的查询的视图，然后从中进行选择，通常可以避免 bcp 实用程序出现问题。在这种情况下：
那么bcp命令就变成：
I find you can usually avoid issues with the bcp utility if you create a view of the query you want to execute and then select from that instead. In this case:
Then the bcp command becomes: