none
problem getting BCP to work

    Question

  • Hi Guys,
    I've been trying to get BCP to work in order to export some data to a CSV file, and I'm not getting too far. The query I am using is

    "U:\>bcp TEST.dbo.REQUEST QUERYOUT c:/sqlFile.csv -T -c"

    but this throws up the error

    "SQLState = 08001, NativeError = 2
    Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2].
    SQLState = 08001, NativeError = 2
    Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
    SQLState = S1T00, NativeError = 0
    Error = [Microsoft][SQL Native Client]Login timeout expired"

    I've tried various different options and switches but none work, so I assume there must be something wrong with my install. I am using SQL Express 2005, and the table I am looking to export is very simple with just 10 lines.

    Can anybody shed some light on what may be causing these errors?
    Friday, July 17, 2009 2:28 PM

Answers

  • iGGt,

    You have QUERYOUT specified, but I believe you just want OUT. QUERYOUT denotes you are using a query to copy data from. BCP Information

    Swap QUERYOUT to just OUT and let me know!

    hope this helps,
    -h
    • Proposed as answer by h0xff Tuesday, July 21, 2009 10:05 AM
    • Marked as answer by iGGt Tuesday, September 08, 2009 8:58 PM
    Friday, July 17, 2009 4:48 PM

All replies

  • iGGt,

    SQL Server 2005 Express has remote connections turned off by default. To enable the remote connections, see HERE. (scroll to the MORE INFORMATION heading)

    hope this helps,
    -h
    Friday, July 17, 2009 2:42 PM
  • cheers,

    that was really useful, unfortunately it still doesn't work. I enabled TCP/IP and named Pipes, confirmed the Browser service was running, and would have updated the firewall except my system couldn't find firewall.cpl (probably due to it being windows 2000).
    The error message has changed slightly though.

    "SQLState = 08001, NativeError = 53
    Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53].
    SQLState = 08001, NativeError = 53
    Error = [Microsoft][SQL Native Client]An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.
    SQLState = S1T00, NativeError = 0
    Error = [Microsoft][SQL Native Client]Login timeout expired"

    Any more ideas??
    Friday, July 17, 2009 3:18 PM
  • iGGt,

    I noticed that you aren't specifying a server to connect to, what is the name of the server and the name of the instance where sql server is running? Try this:

    bcp TEST.dbo.REQUEST QUERYOUT c:/sqlFile.csv -S Server_Name\Instance_Name -T -c

    hope this helps,
    -h
    Friday, July 17, 2009 3:23 PM
  • Cheers,

    I was actually using "bcp OU_TEST.dbo.REQUEST QUERYOUT c:/sqlFile.csv -S PC00570/SQLEXPRESS -T -c" (I obviously copied the wrong one at the start).

    So I tried changing the / to a \ and I now get

    "SQLState = 37000, NativeError = 102
    Error = [Microsoft][SQL Native Client][SQL Server]Incorrect syntax near 'OU_TEST"

    At least the error message is getting shorter!!

    Friday, July 17, 2009 3:29 PM
  • iGGt,

    You have QUERYOUT specified, but I believe you just want OUT. QUERYOUT denotes you are using a query to copy data from. BCP Information

    Swap QUERYOUT to just OUT and let me know!

    hope this helps,
    -h
    • Proposed as answer by h0xff Tuesday, July 21, 2009 10:05 AM
    • Marked as answer by iGGt Tuesday, September 08, 2009 8:58 PM
    Friday, July 17, 2009 4:48 PM
  • You're a star, I tried that and it works, I have a csv file. I just need to tweak it a little to get the desired settings, but other than that it works,


    Thanks again.


    Ian
    Monday, July 20, 2009 9:48 AM
  • I now have another problem.

    I am running bcp fine, it tells me that it has exported 10 lines, but when I open the file, it only has 2 lines.

    The query I have is

    bcp "SELECT <long list of fields> FROM TEST.dbo.Jobs WHERE TEST.dbo.Jobs.uploaded = '1'" QUERYOUT c:\Jobs.csv -S PC00570\SQLEXPRESS -T -c -t, -r\n


    The result on the command line says

    "Starting copy...

    10 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total     : 1      Average : (10000.00 rows per sec.)"

    But when I open the file it only contains the first 2 rows. Can anyone think what might be causing it? I tried running the query as a select * From ... but got the same result. I also tried running the select part of the statement on it's own to verify it was getting all 10 rows, which it is.
    Monday, July 20, 2009 2:13 PM
  • OK, Panic over, one of the last fields was a text field, and for some reason it started with ''abcde etc etc. and those '' were confusing things.
    Monday, July 20, 2009 2:21 PM
  • iGGt,

    Great, glad we could get everything working!

    -h
    Tuesday, July 21, 2009 10:05 AM
  • You rock. I forgot to change the name of the server from my dev env to the client's production. Thanks.
    Thursday, January 26, 2012 4:29 AM