BCP command using TSQL without xp_cmdshell

  • Hello,

    in sql server 2000 I used bcp to be run it in Tsql with xp_cmshell

    EXEC master..xp_cmdshell @SQLCmd.

    @SQLCmd is the bcp cmd.

     

    In SQL server 2005 xp_cmdshell is disabled (I have to enable with sp_configure), but there is another way to run bcp using TSQL to avoid enabling xp_cmdshell.

     

    Thank

  • You could schedule it as a OS task in sql agent. Other than that, I don't think there's a way to run an OS command without xp_cmdshell (unless you create an EXTERNAL_ACCESS CLR procedure to call it, but that strikes me as wrong)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • There is no another way to run bcp while xp_cmdshell is disabled.

    But you may consider Bulk Load TSQL command or CLR (DLLs written in VB or C# and stored in SQL Server) if you want just to load data from file system.

  • OPENROWSET with the 'BULK' provider or BULK INSERT are your other choices.


    * Noel

  • that works for importing data.

    for exporting data, you have to use BCP or SSIS.

    ---------------------------------------
    elsasoft.org

  • thank to everybody,

    bcp is used to export data, so to run bcp in TSQL I have to enable XP_cmdshell.

    SSIS in this project is not used.

    If anyone find another way let me know

  • there's one other option here but I've never tried it - apparently you can export directly from SQL to Excel using OPENROWSET:

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

     

    ---------------------------------------
    elsasoft.org

  • In order to export data to Excel using OPENROWSET you would need to have Excel installed in order to use the Microsoft Jet OLEDB 4.0 driver

  • as part of the same job enable xp_cmdshell just before the bcp and disable it again immediately afterwards

    ---------------------------------------------------------------------

  • george sibbald (5/2/2009)


    as part of the same job enable xp_cmdshell just before the bcp and disable it again immediately afterwards

    That would certainly work... I guess my question would be...

    If the job being run can do that, what's to prevent other users from doing the same? The answer is security. xp_CmdShell requires SA privs to be executed. Disabling xp_CmdShell will not prevent a user with SA privs from enabling it and non SA users can do neither.

    Enable xp_CmdShell, leave it enabled, and restrict all users but a "batch" user from having SA privs. There is no need to ever expose the password for that batch user either. It can all be done through callable jobs.

    --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)

  • Jeff Moden (5/2/2009)


    george sibbald (5/2/2009)


    as part of the same job enable xp_cmdshell just before the bcp and disable it again immediately afterwards

    Enable xp_CmdShell, leave it enabled, and restrict all users but a "batch" user from having SA privs. There is no need to ever expose the password for that batch user either. It can all be done through callable jobs.

    that would upset the heck out of most auditors unfortunately. Which is why I take advantage of my rights as a DBA with sysadmin to just enable it when I really need it, got to be some perks to being a DBA, which leads me onto........

    I would expect my windows account to have sysadmin rights, preferably as part of a windows group, so rather than the 'batch' id having sa rights I would link it to the xp_cmdshell proxy account, although again that mans xp_cmdshell being enabled. Not that I have ever had to actually do this

    ---------------------------------------------------------------------

  • george sibbald (5/2/2009)


    that would upset the heck out of most auditors unfortunately.

    Why? I would think that the lack of security setup by the DBA that allowed it's misuse would be more important because if you can misuse it, then you can misuse virtually anything because xp_CmdShell requires SA privs. If someone can misuse SA privs, then that's the real problem... not xp_CmdShell itself.

    --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)

  • Why? Because I have found (security) auditors require xp_cmdshell be disabled, and they also question any application user id with elevated privileges and may not allow it.

    I find it most secure that xp_cmdshell be disabled by default but It is still an option to enable it temporarily for the DBA (only) to use for legitimate means. If its me it will be legitimate purposes and not misuse, I can't speak for anyone else.

    ---------------------------------------------------------------------

  • Heh... it's a requirement on their part to make up for short comings in security by many DBA's. Instead, I'd rather challenge them to use it in an unauthorized manner. When they fail, they're forced to allow the exception.

    --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)

  • You can take your bcp command and put it in a .cmd file then use a sql agent job to call that .cmd file.

Viewing 15 posts - 1 through 14 (of 14 total)

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