Post reply

Using bcp to export table into .CSV file in TSQL... what in the world am I doing wrong?

  • February 3, 2017 at 6:30 am

    #319086

    So, I've been trying to use bcp to export some DB size info in a SQL Server agent job. This was actually set up by my predecessor, but he left a long time ago and there have been many changes to the DBs since he left with no one to maintain the jobs, etc. So, I'm trying to get everything squared away. 

    Anyway, the whole job works except for the bcp, and I cannot for the life of me figure out what's wrong. I've looked at so many examples and I've seemingly mimicked the syntax exactly, but I'm still getting errors. Below is the bcp I'm using.....


    EXEC sp_configure 'show advanced options', 1;
    reconfigure;
    EXEC sp_configure'xp_cmdshell', 1;
    reconfigure;

    EXEC master.sys.xp_cmdshell bcp MONITORING.dbo.SIZEOUT out D:\DBSizes.csv -c -t, -r \n -T 

    EXEC sp_configure 'xp_cmdshell', 0;
    reconfigure;
    EXEC sp_configure 'show advanced options', 0;
    reconfigure;


    When I run this, I get "Incorrect syntax near MONITORING.dbo.SIZEOUT" That is a valid object. I have tried putting the entire bcp command, including the arguments, in single quotes. No go. I put the object i'm exporting from in double and single quotes (two separate attempts)... no go. I've put the file to export to in both single and double quotes. No go. I've done all of that in combination, no go. I've even changed to using a query so that the syntax is

    EXEC master.sys.xp_cmdshell bcp "SELECT * FROM MONITORING.dbo.SIZEOUT" queryout 'D:\DBSizes.csv' -c -t, -r \n -T 

    as well as 

    EXEC master.sys.xp_cmdshell bcp "SELECT * FROM MONITORING.dbo.SIZEOUT" queryout D:\DBSizes.csv -c -t, -r \n -T 

    Still getting an error. 

    I've also used both EXEC master..xp_cmdshell and EXEC master.dbo.xp_cmdshell. Neither worked. 

    I've tried running it without reconfiguring show advanced options/xp_cmdshell in TSQL, and instead turned SQL CMD Mode on via SSMS. Nothing. 

    So, I feel like I'm going crazy, because clearly I'm doing something wrong, but I just can't see what. Please help. What stupid mistake am I making??

    Thanks!

  • February 3, 2017 at 6:39 am

    #1926572

    whatever is passed to xp_cmdshell needs to be wrapped in single quotes!
    EXEC master.sys.xp_cmdshell 'bcp "SELECT * FROM MONITORING.dbo.SIZEOUT" queryout D:\DBSizes.csv -c -t, -r \n -T '

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 3, 2017 at 7:18 am

    #1926585

    You know, in my frustration, I tried that, but it was just giving me the argument syntax for BCP. However, now that I'm trying it again, it's at least outputting an error:
    "SQLState = S1000, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
    NULL"

    So, I must have done something wrong in my frustration when I originally wrapped it all in single quotes. I'll do some research on that error. It's progress, at least! Thank you!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply