Post reply

xp_cmdshell help

  • December 7, 2011 at 10:37 am

    #247338

    I want to execute a bunch of commands (built from values in a table) using xp_cmdshell. However, it takes very long to run if I have xp_cmdshell in a loop and run one command at a time as I iterate through the records.

    I found out that you can run several dos commands in one line by separating each with a "&&" This ran over 10x faster than my previous setup. Therefore, I moved some code around and concatenated my commands in a variable inside of the while loop and then ran the xp_cmdshell outside of the loop, passing it the variable. The problem is that the xp_cmdshell cannot accept a variable defined as VARCHAR(MAX), and VARCHAR(8000) isn't enough to hold all of my commands. Does anyone know of a way I might be able to get this all to work? I could go back to the xp_cmdshell call per dos command, but it's just so slow. I was thinking of using NTILE to help divide and conquer, but I'm just not sure what the best strategy is.

    Thanks in advance for the help.

    Mike

    Mike Scalise, PMP
    https://www.michaelscalise.com

  • December 7, 2011 at 10:59 am

    #1418294

    i'm sure xp_cmdshell is limited to 4096 chars; that's a hard limit with no work arounds, as it's a feature of the cmd.exe object int he operating system.

    Powershell might be a better solution; it's a lot more robust,a nd you could have a loop in powershell getting all the data that were were currently putting togeth as a varchar(max)

    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!

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

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