SQL Server Performance

Parameters to xp_cmdshell

Discussion in 'SQL Server DTS-Related Questions' started by DBA_Newbie, Mar 8, 2005.

  1. DBA_Newbie New Member

    Hi,

    I am trying to populate the command string parameter for xp_cmdshell using DTS Global Variables. I put an SQL Task on my DTS designer and write the following SQL Statement

    Exec master..xp_cmdShell ?

    Where ? is populated from a DTS global variable.

    I click on Parameters button and I get the following error "Syntax Error or Access violation"

    I know that we can pass parameters this way to a stored procedure that accepts Input parameters via global variable and returns an output parameter and populates a global variable. But I am having issues when I use xp_cmdshell

    I also tried to build the string using the following SQL code

    declare @query varchar(100)
    declare @mypath varchar(100)
    set @mypath = 'Del D:MyappMyExcel.XLS'
    set @query ='master..xp_cmdshell ''' + ? + ''''
    exec (@query)

    This code snippet works fine on SQL Query Analyser if we replace ? with the variable @mypath. But when I put the same on the DTS, and click on Parameters to populate from a global variable, I get the same error "Syntax Error or Access violation"

    Any one who has resolved this, Kindly help me with the solution.

    Thanks in Advance.
  2. DBA_Newbie New Member

    Hi,<br /><br />I didnt have much time to spend on research. Hence, I wrote the same code in a stored procedure that accepts input parameters and made it work <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Thanks

Share This Page