Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2009
    Posts
    4

    Unanswered: CSV data file from SQL Server 2000 table - bcp in batch file

    Hi

    I need to extract some table data from SQL Server 2000 into a series of CSV files. I experimented with isql, osql and bcp and decided on bcp because it seemed the easiest way to obtain CSV data without trailing spaces.

    My problem is that I can run single bcp commands of this form:

    bcp "USE mydatabase SELECT Doc_ScanTime, Doc_AccountNo, Doc_ID, Doc_IndexUser, Doc_Initial, Doc_Surname FROM mytable WHERE handle_class='20' AND Object_isDeleted='0' AND Doc_ID LIKE '1%'" queryout bcp_output_1.txt -c -t, -T

    on the Windows command line and they return the results I expect (although an error 5701 is displayed and a warning issued that the database context was changed, presumably as the result of a USE in my query). As soon as I put a number of them together into a batch file and run it from the command prompt, however, everything looks exactly the same except that I get no data back (0 rows copied).

    There seem to plenty of examples out there of bcp being used in batch files - what am I doing wrong?

    Thanks and regards

    --Jim.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not sure about the second part Jim.Maybe solving the first part will help....
    Code:
    bcp "SELECT Doc_ScanTime, Doc_AccountNo, Doc_ID, Doc_IndexUser, Doc_Initial, Doc_Surname FROM mydatabase..mytable WHERE handle_class='20' AND Object_isDeleted='0' AND Doc_ID LIKE '1%'" queryout bcp_output_1.txt -c -t, -T
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2009
    Posts
    4
    That certainly eliminates the warnings (many thanks!), but doesn't help the batch file problem, which still gives no results, even if only a single bcp command is put into the batch file.

    Regards

    -- Jim.

  4. #4
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    maybe a permissions problem

    Consider adding these BCP Parameters

    [-S server_name[\instance_name]] [-U login_id] [-P password]
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  5. #5
    Join Date
    Mar 2009
    Posts
    4
    Thanks for the suggestion - tried it, but it made no difference. Runs fine as a single command at the prompt, but returns no data when run in a batch file.

    Regards

    -- Jim.

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    USE mydatabase; SELECT Doc_ScanTime, Doc_AccountNo, Doc...

    this works with a -Q"command1;command2" via OSQL, I assume the same applies to BCP.
    Last edited by PMASchmed; 03-06-09 at 10:31.

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    escape your % character in the "like".

    try %%, DOS thinks it's a variable.

    echo your BCP command and you will see.

  8. #8
    Join Date
    Mar 2009
    Posts
    4
    You got it. Sorted! Now works perfectly!

    Many thanks and regards

    -- Jim.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •