Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Execute VBScript commands or .vbs files via T-SQL. Expand / Collapse
Author
Message
Posted Monday, September 24, 2007 1:35 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, November 05, 2008 2:49 PM
Points: 57, Visits: 14
Comments posted to this topic are about the item Execute VBScript commands or .vbs files via T-SQL.


Post #401871
Posted Monday, June 01, 2009 9:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, May 28, 2011 8:46 PM
Points: 1, Visits: 71
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




Post #726744
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse