Post reply

BCP without XP_CMDSHELL

  • February 19, 2008 at 3:47 am

    #71169

    Hi,

    I have written an SP which will export the data from sql server to text file. I have used BCP with xp_cmdshell. But in my production server, we don't have rights to run the XP_CMDSHELL command.

    Is there any way to execute the bcp without xp_cmdshell command ??

    Any other method to export the data from sql server to text file???

  • February 19, 2008 at 3:51 am

    #779647

    If the text file already exists, OPENROWSET.

    Or you could give DTS a try.

    N 56°04'39.16"
    E 12°55'05.25"

  • February 19, 2008 at 3:19 pm

    #779972

    There's a command line version of bcp. See BOL.

    There is no "i" in team, but idiot has two.

  • February 19, 2008 at 8:41 pm

    #780052

    If you have a "friendly" DBA, (s)he will be able to setup a proxy user on a job that anyone could run at any time.

    Good lesson here, though... most DBA's do not allow the adhoc usage of anything that requires "SA" privs including xp_CmdShell and the sp_OA* procs. Always check with the DBA before writing such code. It's their box and they typically have the right to refuse any code they believe to be "dangerous to the data or the server".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • February 20, 2008 at 2:57 pm

    #780419

    [font="Arial"]

    Hello,

    We use the command line version of bcp from within a .bat to export data from the 'working' tables. Runs quick and works reliably. Since you are in a .bat you can also direct the output of the program and produce any log files to help you trouble shoot if something occurs.

    Here's a sample of a bcp command line:

    rem create output file for invalid data

    bcp %DATABASE%.dbo.workingTable01 out -c -e -r\r -S %SERVERNAME% -T -e %LOGS%\workingTable01.log

    I hope this helps.

    Regards,

    Terry

    [/font]

Viewing 5 posts - 1 through 4 (of 4 total)

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