/***********************************************************************************
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