Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Posts
    7

    Angry Unanswered: xp_cmdshell doesn't want to execute a vbs

    Hi,

    I've been searching the forum and internet all day to find a solution for my problem.
    I want to post data on a iis server of a friend of mine, by
    1. executing a stored procedure in my SQL Server 2000 (SP3)
    2. in this stored procedure I call a vbs script
    3. which runs/opens an asp on his webserver

    I have have created a stored procedure that uses xp_cmdshell
    set @command_string = 'C:\temp\mail.vbs /action:youwon /accountid:harrynac'
    exec @result = master.dbo.xp_cmdshell @command_string , NO_OUTPUT

    This is the .vbs
    Set WshShell = WScript.CreateObject("WScript.Shell")
    action = WScript.Arguments.Named.Item("action")
    accountid = WScript.Arguments.Named.Item("accountid")
    URL = "http://www.myserver.com/mailer.asp?action=" & action & "&accountid=" & accountid
    WshShell.Run(URL)
    set WshShell = Nothing

    When I run the examples from sql books, It all works fine.
    for example:
    DECLARE @cmd sysname, @var sysname
    SET @var = 'dir/p'
    SET @cmd = @var + ' > dir_out.txt'
    EXEC master..xp_cmdshell @cmd

    but when I use a vb script it won't.

    When I run the stored procedure it gives no error, so i've run out of brains for what it might be.

    Does anyone have a clue??

  2. #2
    Join Date
    Mar 2003
    Location
    Indiana, USA
    Posts
    100
    Try using Cscript.exe or Wscript.exe

    See below:

    Tim S

    SET @command_string =
    'Wscript.exe C:\temp\mail.vbs /action:youwon /accountid:harrynac'

    SET @command_string =
    'Cscript.exe C:\temp\mail.vbs /action:youwon /accountid:harrynac'

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245

    Re: xp_cmdshell doesn't want to execute a vbs

    I think you could also try it in DTS. you can create an ActiveX script in a DTS package, save it and then run the DTS package from an SP.

    Regards,

    Hugh Scott

    Originally posted by alexP
    Hi,

    I've been searching the forum and internet all day to find a solution for my problem.
    I want to post data on a iis server of a friend of mine, by
    1. executing a stored procedure in my SQL Server 2000 (SP3)
    2. in this stored procedure I call a vbs script
    3. which runs/opens an asp on his webserver

    I have have created a stored procedure that uses xp_cmdshell
    set @command_string = 'C:\temp\mail.vbs /action:youwon /accountid:harrynac'
    exec @result = master.dbo.xp_cmdshell @command_string , NO_OUTPUT

    This is the .vbs
    Set WshShell = WScript.CreateObject("WScript.Shell")
    action = WScript.Arguments.Named.Item("action")
    accountid = WScript.Arguments.Named.Item("accountid")
    URL = "http://www.myserver.com/mailer.asp?action=" & action & "&accountid=" & accountid
    WshShell.Run(URL)
    set WshShell = Nothing

    When I run the examples from sql books, It all works fine.
    for example:
    DECLARE @cmd sysname, @var sysname
    SET @var = 'dir/p'
    SET @cmd = @var + ' > dir_out.txt'
    EXEC master..xp_cmdshell @cmd

    but when I use a vb script it won't.

    When I run the stored procedure it gives no error, so i've run out of brains for what it might be.

    Does anyone have a clue??

  4. #4
    Join Date
    Mar 2003
    Posts
    7
    I've tried using cscript and wscript but it didn't make a difference

  5. #5
    Join Date
    Mar 2003
    Posts
    7

    Re: xp_cmdshell doesn't want to execute a vbs

    I've never created a DTS package. Can you help me out here.
    Where is the best place to start or do you know a site with good examples which are similair to my problem?

    Thanx
    Originally posted by hmscott
    I think you could also try it in DTS. you can create an ActiveX script in a DTS package, save it and then run the DTS package from an SP.

    Regards,

    Hugh Scott

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245

    Re: xp_cmdshell doesn't want to execute a vbs

    I might try www.sqldts.com. You can also try SQL BOL. DTS is not too intimidating once you get around a few basic concepts. If you want to run some VBS code insise a DTS package, you simply insert the ActiveX object into a DTS package (the objects are the icons on the left-hand pane of the DTS Package window).

    Once you insert the ActiveX object, you will be prompted to insert the code (it goes inside a function called Main()). The debugging/editing capabilities of DTS are quite simply atrocious (or better said, none-existent). I often write my code in VB and then transfer it over (just remember rules about not declaring types and late-binding and the code will run fine).

    You can easily instantiate any COM+-compliant object using DTS.

    Please feel free to submit additional questions or queries here and someone will be able to tackle them for you.

    Regards,

    Hugh Scott


    Originally posted by alexP
    I've never created a DTS package. Can you help me out here.
    Where is the best place to start or do you know a site with good examples which are similair to my problem?

    Thanx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •