Execute VBScript commands or .vbs files via T-SQL.

  • Comments posted to this topic are about the item Execute VBScript commands or .vbs files via T-SQL.

  • This is a great idea but executing more than 1 line of vbscript code will fail, and you can't output special characters(command chars).

    You have to either slice up each line of the vbs, loop through and echo each line to append to the file or just use BCP utility to create your vbs file.

    /***********************************************************************************

    original script by Cade Bryant: http://www.sqlservercentral.com/scripts/Miscellaneous/30963/

    updated to enable generating script that is more than 1 line.

    windows command shell "ECHO" is limited in what it can do. It can't echo more than

    a single line at a time and it can't echo command/special characters

    */

    CREATE PROCEDURE usp_ExecVBS

    @FileOrCommand VARCHAR(4) = NULL,

    @FileCmdText VARCHAR(8000) = NULL

    AS

    SET NOCOUNT ON

    DECLARE @cmd VARCHAR(8000)

    IF @FileOrCommand = 'file'

    BEGIN

    SET @Cmd = 'CScript ' + '"' + @FileCmdText + '"'

    EXEC xp_cmdshell @cmd

    END

    ELSE IF @FileOrCommand = 'cmd'

    BEGIN

    DECLARE @TempFile SYSNAME

    CREATE TABLE ##STAGE

    (Command VARCHAR(8000));

    INSERT INTO ##STAGE

    SELECT @FileCmdText;

    SET @TempFile = 'C:\' + CAST(NEWID() AS SYSNAME) + '.vbs'

    SET @Cmd = 'bcp "SELECT COMMAND FROM ##STAGE" queryout ' + @TempFile + ' -T -S .\POOP -c'

    exec xp_cmdshell @Cmd;

    SET @cmd = 'CScript ' + '"' + @TempFile + '"'

    EXEC xp_cmdshell @cmd;

    SET @cmd = 'DEL ' + @TempFile

    EXEC xp_cmdshell @cmd;

    DROP TABLE ##STAGE

    END;

    GO

    /******************************************************************************

    *execute with "cmd" method:

    *******************************************************************************/

    EXEC usp_ExecVBS 'cmd',

    'Set cdoConfig = CreateObject("CDO.Configuration")

    Set cdoMessage = CreateObject("CDO.Message")

    schema = "http://schemas.microsoft.com/cdo/configuration/"

    With cdoConfig.Fields

    .Item(schema & "sendusing") = 2

    .Item(schema & "smtpserver") = "smtp.gmail.com"

    .Item(schema & "smtpserverport") = 465

    .Item(schema & "smtpusessl") = true

    .Item(schema & "smtpauthenticate") = 1

    .Item(schema & "sendusername") = "gary.luis.ellis@gmail.com"

    .Item(schema & "sendpassword") = "%%%%%%%"

    .Item(schema & "smptconnectiontimeout") = 40

    .update

    End With

    With cdoMessage

    Set .Configuration = cdoConfig

    .From = "gary.luis.ellis@gmail.com"

    .To = "gary.luis.ellis@gmail.com"

    .Subject = "Problems"

    .TextBody = "Problems"

    .Fields.update

    .Send

    End With';

    /******************************************************************************

    * Wrap this into another procedure to setup simple notification for catch

    * block of try catch, or when a job completes, etc.

    *******************************************************************************/

    CREATE PROC uspEXECVBSMAIL

    AS

    EXEC usp_ExecVBS 'cmd',

    'Set cdoConfig = CreateObject("CDO.Configuration")

    Set cdoMessage = CreateObject("CDO.Message")

    schema = "http://schemas.microsoft.com/cdo/configuration/"

    With cdoConfig.Fields

    .Item(schema & "sendusing") = 2

    .Item(schema & "smtpserver") = "smtp.gmail.com"

    .Item(schema & "smtpserverport") = 465

    .Item(schema & "smtpusessl") = true

    .Item(schema & "smtpauthenticate") = 1

    .Item(schema & "sendusername") = "gary.luis.ellis@gmail.com"

    .Item(schema & "sendpassword") = "%%%%%%"

    .Item(schema & "smptconnectiontimeout") = 40

    .update

    End With

    With cdoMessage

    Set .Configuration = cdoConfig

    .From = "gary.luis.ellis@gmail.com"

    .To = "turbogary2000@yahoo.com"

    .Subject = "Problems"

    .TextBody = "Problems"

    .Fields.update

    .Send

    End With'

    EXEC uspEXECVBSMAIL

Viewing 2 posts - 1 through 1 (of 1 total)

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