WScript.Shell Object - Problem with Quotes RRS feed

  • Question

  • Hey!

    I'm trying to execute a command via SQL. Therefor I created a Stored Procedure which looks like the following:


    ALTER PROCEDURE [dbo].[custom_ExecuteShell] 
    	-- Add the parameters for the stored procedure here
    	@cmd NVARCHAR(1000)
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
      -- Insert statements for procedure here
    	DECLARE @obj INT
    	DECLARE @finalcmd NVARCHAR(1000)
    	SET @finalcmd = 'RUN(""' + @cmd + '"", 0)'
    	EXEC sp_oacreate 'WScript.Shell', @obj OUT
    	EXEC sp_oamethod @obj,@finalcmd
    	EXEC sp_oadestroy @obj
    	PRINT @cmd
    	PRINT @finalcmd


    There shouldnt be a problem with the permissions anymore. I solved that but additionaly Im executing this procedure as the "sa". Im executing this procedure with:


     custom_ExecuteShell '"C:\scripts\SendMailOnTicket.vbs" "3" "3" "3" "3" "3" "3" "3" "3" "3" "3" "3" "3"'


    The vbs contains parameters. The problem is that there could be spaces within the parameters so I have to quote all the parameters. The outcome is:


    "C:\scripts\SendMailOnTicket.vbs" "3" "3" "3" "3" "3" "3" "3" "3" "3" "3" "3" "3"
    RUN(""C:\scripts\SendMailOnTicket.vbs" "3" "3" "3" "3" "3" "3" "3" "3" "3" "3" "3" "3"", 0)


    And that does not work. If I enter the following command to a cmd Shell it works!

    C:\scripts\SendMailOnTicket.vbs "3 3" "4 3" "4 3" "5" "4" "3" "2" "4" "5" "4" "3" "3"

    What exaclty must be the output of @finalcmd (which is located in the stored procedure) so that the command will be executed successfully?


    Thank you!
    Best regards


    Friday, April 23, 2010 10:11 AM

All replies

  • Unfortunately not...
    Friday, April 23, 2010 11:38 AM
  • I just tried to execute the following:


    DECLARE @obj int
    DECLARE @finalcmd NVARCHAR(1000)
    SET @finalcmd = 'RUN("explorer.exe")'

    EXEC sp_oacreate 'WScript.Shell', @obj OUT
    EXEC sp_oamethod @obj,@finalcmd
    EXEC sp_oadestroy @obj


    Does not open the windows explorer as it does when executing the command directly in a command prompt.


    So I think there is a mistake within this procedure?

    OLE Automation is enabled

    Script executed with "sa" user

    What else could go wrong?


    Thank you!


    Friday, April 23, 2010 2:01 PM
  • Ok, now I know what the problem is:


    The oamethod command needs a RUN("<command_here>",0)

    So the command has to be in quotes!

    Also the parameters have to be in quotes: C:\scripts\test.vbs "param1" "param2" "param3"

    And when it looks like:

    RUN("C:\scripts\SendMailOnTicket.vbs "param1" "param2"...,0) the RUN statement actually is: RUN("C:\scripts\SendMailOnTicket.vbs ",0)

    So the first quote of the first parameter lets the command end.


    How to prevent this?

    Im not sure what you mean with dymamic procedures or how I could implement this...


    Maybe there is an easier solution  for that now that we know what the exact problem is?


    Best regards


    Friday, April 23, 2010 4:18 PM
  • Yeah, but I think dynamic SQL is not solving this issue.

    No matter how I create this command (static or dynamic):

    RUN("C:\scripts\SendMailOnTicket.vbs "param1" "param2",0)

    it won't work!

    The problem are the quotes.

    Batch command parameters need quotes.

    So does the RUN command.


    I could use dynamic sql to create the RUN command, but this wont have sense since the commands I will create wont work due to the quote issue :)


    Or am I wrong?

    Friday, April 23, 2010 5:12 PM