Post reply

Run a stored procedure from BCP

  • February 3, 2010 at 2:25 pm

    #215228

    Hi,

    I'm running a stored procedure from BCP using a command like

    @sql = 'bcp "exec mydb..mysproc" queryout d:\\output.txt -c -T -SserverName .....'

    exec master..xp_cmdshell @sql

    This is working fine and the result returned by the sproc is redirected to output.txt file; however I'm facing a performance issue and using profile i've noticed that the sproc is executed 3 times, one with SET FMTONLY to get a list of columns and this is normal, but there are two other calls to return the result set and I cannot understand the resason for that.

    Is there any work around to prevent BCP to run the sproc many times ?

    Thanks for your help

  • February 3, 2010 at 7:00 pm

    #1113370

    Perhaps include a fully qualified schema name instead of just "dot dot"?

    --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 2 posts - 1 through 1 (of 1 total)

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