Post reply

What is an alternative for xp_cmdshell

  • December 6, 2015 at 10:15 pm

    #309971

    Dear All,

    I want to run the below query with out using xp_cmdshell,Please help on this

    /*

    I have inserted Abcd .zip file into the below table

    Create Table ZipRetrieve

    (

    ZipAsBinary Varbinary(mx)

    )

    */

    --To retrieve the zip file

    Declare @SQLcommand varchar(8000)

    set @SQLcommand = 'bcp "SELECT ZipAsBinary FROM MainDb.dbo.ZipRetrieve" queryout "C:\Files\Abcd.Zip" -T -n '

    exec xp_cmdshell @SQLcommand

    Expected output is :- Create a Abcd.Zip in the C:\Files

    Here xp_cmdshell is disabled ,Please let me know an alternative method for this

  • December 7, 2015 at 12:33 am

    #1844289

    One option: Write an SSIS Package to write the binary to disk using the Export Column transform inside a Data Flow Task.

    Another option: Write a SQLCLR Procedure or Function to write the binary to disk.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • December 7, 2015 at 12:54 am

    #1844293

    Thank you very much for your kind help.If possible Please post some samples.

  • December 7, 2015 at 12:59 am

    #1844294

    You're welcome. Bing can help. I specifically included the names of the SSIS Tasks and Components you would want to research. In .NET you can begin researching StreamWriter.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • December 7, 2015 at 2:32 am

    #1844302

    thank you so much.Let me check this

  • December 7, 2015 at 4:00 am

    #1844326

    You could make the call through SQL Agent and use Powershell at the command line. There are any number of examples of this.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • December 7, 2015 at 11:22 pm

    #1844550

    naufymc (12/6/2015)

    Here xp_cmdshell is disabled ,Please let me know an alternative method for this

    That's a shame. It's a powerful tool. They should learn that it can be used safely and without giving individuals privs to run it directly. It can all be easily done through stored procedures.

    Orlando is correct. If xp_CmdShell isn't allowed, then SSIS is probably your best bet... if they have it set up.

    As a bit of a sidebar, I can't imagine them allowing an Cmd level Exec Task to command line level PowerShell if they don't allow xp_CmdShell. If you can use Exec Task, you might be able to run BCP or SQLCmd but, again, I can't see them allowing an Cmd level Exec Task if they don't allow xp_CmdShell.

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

Viewing 7 posts - 1 through 7 (of 7 total)

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