none
Passing a stored procedure parameter to an xp_cmdshell - sqlcmd line

    Question

  • I have the following sql commands but I can't get the parameter to pass to the stored procedure - the resulting file shows that I need to declare the @Divs variable which I hav done...how can I get the xp_cmdshell>sqlcmd line to recognize and accept that variable as a parameter??

    DECLARE @Divs varchar(50)
    SET @Divs = '01'
    Exec master..xp_cmdshell 'SQLCMD -E -S000DBS02 -d xxxx_xxxxx -Q "EXEC dbo.xxxx_PDDP @Divs" -o \\000dbs02\c$\temp\TestPDDP.html -h-1 -y0'

    When I just put the 01 into the exec dbo.xxxx line the output works fine and I get what I need, but I have to be able to pass this parameter via a variable or some other means.

    The above code is simplified from a larger set of code that does a while loop and gets the next @Divs and then tries to execute the xp_cmdshell line again...26 times...which is why I have to pass the parameter as a variable.  The stored procedure creates an xml output and the -0 output saves the file as an html file.  It works great as long as I pass the parameter as a static entity.

    I also tried setting the output file as a variable but it didn't recognize that either so hopefully the answers I get will solve both problems.

    Thank you for any and all assistance!!

     

    DECLARE @Divs varchar(50)
    SET @Divs = '01'
    Exec master..xp_cmdshell 'SQLCMD -E -S000DBS02 -d ULTIPRO_CHSFL -Q "EXEC dbo.CHSPRDistWebReport_PDDP @Divs" -o \\000dbs02\c$\temp\Test702.html -h-1 -y0'

    Tuesday, May 08, 2012 5:34 PM

Answers

  • You will need to use the same trick as you used already:

    DECLARE @Divs varchar(50), @cmd varchar(4000), @FileName varchar(1000) SET @Divs = '01' SET @FileName = '\\000dbs02\c$\temp\TestPDDP' + @Divs + '.htm' Print @FileName --set @cmd = 'SQLCMD -E -S000DBS02 -d ULTIPRO_CHSFL -Q "EXEC dbo.CHSPRDistWebReport_PDDP ' + quotename(@Divs,'''') + '" -o \\000dbs02\c$\temp\TestPDDP.html -h-1 -y0' set @cmd = 'SQLCMD -E -S000DBS02 -d ULTIPRO_CHSFL -Q "EXEC dbo.CHSPRDistWebReport_PDDP ' + quotename(@Divs,'''') +

    '" -o ' + @FileName + ' -h-1 -y0' print @cmd -- verify that the command is correct - this will work if filename doesn't contain spaces, otherwise surround it with double quotes Exec master..xp_cmdshell @Cmd



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Tuesday, May 08, 2012 6:24 PM
    Moderator

All replies

  • try

    DECLARE @Divs varchar(50)
    DECLARE @Cmd varchar(1000)
    SET @Divs = '01'
    SET @Cmd = 'SQLCMD -E -S000DBS02 -d xxxx_xxxxx -Q "EXEC dbo.xxxx_PDDP ' + @Divs + '" -o \\000dbs02\c$\temp\TestPDDP.html -h-1 -y0'
    PRINT @Cmd
    --Exec master..xp_cmdshell @Cmd


    Tuesday, May 08, 2012 5:41 PM
  • You will have to embed the @Divs variable into your query, e.g.

    declare @Divs varchar(50), @cmd nvarchar(4000)

    set @Divs = '01'

    set @cmd = 'SQLCMD -E -S000DBS02 -d xxxx_xxxxx -Q "EXEC dbo.xxxx_PDDP ' + quotename(@Divs,'''') + '" -o \\000dbs02\c$\temp\TestPDDP.html -h-1 -y0'

    exec master..xp_cmdshell @cmd


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, May 08, 2012 5:41 PM
    Moderator
  • The following blog post has example for SQLCMD execution of a parametrized stored procedure:

    http://www.sqlusa.com/bestpractices2005/sqlcmdselect/


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Tuesday, May 08, 2012 5:52 PM
    Moderator
  • Thanks for the answers...you've gotten me a lot closer!!  Naomi's code actually produced the desired result!!

    Now to change the output file to show the @Divs in it....when I change the code as follows I get no output at all....any further help greatly appreciated...

    DECLARE @Divs varchar(50),
     @cmd varchar(4000),
     @FileName varchar(1000)

    SET @Divs = '01'

    SET @FileName = '\\000dbs02\c$\temp\TestPDDP' + @Divs + '.htm'
    Print @FileName
    --set @cmd = 'SQLCMD -E -S000DBS02 -d ULTIPRO_CHSFL -Q "EXEC dbo.CHSPRDistWebReport_PDDP ' + quotename(@Divs,'''') + '" -o \\000dbs02\c$\temp\TestPDDP.html -h-1 -y0'
    set @cmd = 'SQLCMD -E -S000DBS02 -d ULTIPRO_CHSFL -Q "EXEC dbo.CHSPRDistWebReport_PDDP ' + quotename(@Divs,'''') + '" -o @FileName -h-1 -y0'

    Exec master..xp_cmdshell @Cmd

    Tuesday, May 08, 2012 6:22 PM
  • You will need to use the same trick as you used already:

    DECLARE @Divs varchar(50), @cmd varchar(4000), @FileName varchar(1000) SET @Divs = '01' SET @FileName = '\\000dbs02\c$\temp\TestPDDP' + @Divs + '.htm' Print @FileName --set @cmd = 'SQLCMD -E -S000DBS02 -d ULTIPRO_CHSFL -Q "EXEC dbo.CHSPRDistWebReport_PDDP ' + quotename(@Divs,'''') + '" -o \\000dbs02\c$\temp\TestPDDP.html -h-1 -y0' set @cmd = 'SQLCMD -E -S000DBS02 -d ULTIPRO_CHSFL -Q "EXEC dbo.CHSPRDistWebReport_PDDP ' + quotename(@Divs,'''') +

    '" -o ' + @FileName + ' -h-1 -y0' print @cmd -- verify that the command is correct - this will work if filename doesn't contain spaces, otherwise surround it with double quotes Exec master..xp_cmdshell @Cmd



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Tuesday, May 08, 2012 6:24 PM
    Moderator
  • Thank you Naomi!!  I tried every combination of quotes I could think of....forgot the '+''s....duh.

    Tuesday, May 08, 2012 6:28 PM