InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 22:27:44 GMT20http://www.sqlservercentral.com/Forums/Topic401871-138-1.aspxThis 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.[code]/***********************************************************************************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 thana single line at a time and it can't echo command/special characters*/CREATE PROCEDURE usp_ExecVBS@FileOrCommand VARCHAR(4) = NULL,@FileCmdText VARCHAR(8000) = NULLASSET NOCOUNT ONDECLARE @cmd VARCHAR(8000)IF @FileOrCommand = 'file'BEGIN SET @Cmd = 'CScript ' + '"' + @FileCmdText + '"' EXEC xp_cmdshell @cmdENDELSE IF @FileOrCommand = 'cmd'BEGINDECLARE @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 ##STAGEEND;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.updateEnd WithWith cdoMessageSet .Configuration = cdoConfig.From = "gary.luis.ellis@gmail.com".To = "gary.luis.ellis@gmail.com".Subject = "Problems".TextBody = "Problems".Fields.update.SendEnd With';/****************************************************************************** * Wrap this into another procedure to setup simple notification for catch * block of try catch, or when a job completes, etc. *******************************************************************************/CREATE PROC uspEXECVBSMAILASEXEC 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.updateEnd WithWith cdoMessageSet .Configuration = cdoConfig.From = "gary.luis.ellis@gmail.com".To = "turbogary2000@yahoo.com".Subject = "Problems".TextBody = "Problems".Fields.update.SendEnd With' EXEC uspEXECVBSMAIL[/code]Mon, 01 Jun 2009 09:22:30 GMTturbogary2000http://www.sqlservercentral.com/Forums/Topic401871-138-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Miscellaneous/30963/">Execute VBScript commands or .vbs files via T-SQL.</A>[/B]Mon, 24 Sep 2007 01:35:56 GMTcadebryant