BCP... queryout execution erroneously returns zero rows where data is know to exist

Question

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?

Are you able to run a profiler trace and examine the command passed in from BCP? — Ed Harper, Dec 15 '11 at 14:10
Just noticed your question is about SQL 2000 - see http://www.developer.com/db/article.php/3482216/Introduction-to-SQL-Profiler.htm — Ed Harper, Dec 15 '11 at 14:23
I ran it and the command looks good as shown above. However when I run it in BCP the duration is in the 940-950 range. The same query ran in query analyzer runs at 3496 for duration. Not sure how to interpret that other than one seems to be doing more than the other. I actually get the results expected from the one of longer duration. — user774512, Dec 15 '11 at 14:45
Can you edit the question to include your BCP command string? — Ed Harper, Dec 15 '11 at 14:51
I updated the query portion to include the entire BCP command string. This executes from a batch file on the SQL server and has two input parameters for the username and password as noted by %1 and %2. — user774512, Dec 15 '11 at 15:00
Is the command passed to BCP multi-line as shown here? If so, what happens if you change it to be all on one line? — Ed Harper, Dec 16 '11 at 08:26

score 0 · Answer 1 · answered Mar 06 '15 at 11:51

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:

CREATE VIEW [MyOutputView] as
select obj_id, loc_code, CONVERT(VARCHAR(20), create_date, 20) AS build_date,
...
...
and obj_id not like '1G0%'
GO

Then the bcp command becomes:

bcp "SELECT * FROM MyDb.dbo.MyOutputView" queryout z:\test.txt -U %1 -P %2 -S SQLSVR\VM_PROD -c

BCP... queryout execution erroneously returns zero rows where data is know to exist

1 Answers1