Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)
Run SQL Hosted VBS Script from SQL

By John Bevan, 2012/09/20
The code includes example usage. Here I've created a table to host the VBScript code & settings such as whether the script is interactive, or if it should timeout after a given period. However the script should be easy to alter to run ad hoc scripts also (just amend sp_RunScript from taking an id and fetching the script/parameters to moving the @script, @batchMode, @timeoutSecs variables to the function's parameter list.
The code runs on the host server under the database instance's service account.
 use [insert_db_name_here]

 go

 ----use below code to enable commands (required for xp_cmdshell to work)

 --exec sp_configure 'show advanced options', 1

 --go

 --reconfigure

 --go

 --exec sp_configure 'xp_cmdshell', 1 

 --go

 --reconfigure

 --go



 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



 if OBJECT_ID('vbsScripts') is not null drop table vbsScripts

 go

 create table vbsScripts 

 (

 id bigint not null identity(1,1) constraint pk_vbsScripts primary key clustered 

 , name nvarchar(256) not null constraint uk_vbsScripts_name unique

 , script nvarchar(max) not null

 , timeoutSecs int null constraint df_vbsScripts_timeoutSecs default(86400)--leave as null if you don't want a timeout / defaults to 1 day / 24*60*60

 , batchMode bit not null constraint df_vbsScripts_batchMode default(1)

 )

 go



 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



 /*

 Gets the temp directory from environment variables

 usage:

 declare @tempPath nvarchar(max)

 exec GetTempDirectory @tempPath out 

 select @tempPath 

 */

 if OBJECT_ID('GetTempDirectory') is not null drop proc GetTempDirectory

 go

 create proc GetTempDirectory(@path nvarchar(max) out)

 as

 begin

 set @path = ''

 declare @tempTable table(data nvarchar(max))

 insert @tempTable exec master..xp_cmdshell 'echo %temp%'

 select top 1 @path = data from @tempTable

 if SUBSTRING(@path,len(@path),1) <> '\' set @path = @path + '\'

 end

 go



 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 /*

 Creates a unique filename (using guid to ensure uniqueness and datetime to make the name friendlier)

 usage:

 declare @tempPath nvarchar(max)

 exec GetTempDirectory @tempPath out 

 select @tempPath 

 */

 if OBJECT_ID('GetTempFilename') is not null drop proc GetTempFilename

 go

 create proc GetTempFilename(@fn nvarchar(max) out)

 as

 begin

 --exec GetTempDirectory @fn out --can just use environment variable - originally had issues testing as was looking at the wrong user's temp directory :/

 --set @fn = @fn + 'sqlTemp_' + replace(replace(replace(convert(nvarchar(24), getutcdate(),127),'-',''),':',''),'.','') + '_' + CAST(NEWID() as nvarchar(36)) + '.tmp'

 set @fn = '%temp%\' + 'sqlTemp_' + replace(replace(replace(convert(nvarchar(24), getutcdate(),127),'-',''),':',''),'.','') + '_' + CAST(NEWID() as nvarchar(36)) + '.tmp'

 end

 go



 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



 if OBJECT_ID('dbo.fn_EscapeDosCharachters') is not null drop function dbo.fn_EscapeDosCharachters

 go

 create function dbo.fn_EscapeDosCharachters

 (

 @text nvarchar(max)

 )

 returns nvarchar(max)

 as

 begin

 --http://www.dostips.com/?t=Snippets.Escape

 set @text = REPLACE(@text,'^','^^')

 set @text = REPLACE(@text,'!','^!')

 set @text = REPLACE(@text,'&','^&')

 set @text = REPLACE(@text,'|','^|')

 set @text = REPLACE(@text,'%','%%')

 return @text

 end

 go

 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



 if OBJECT_ID('createTempTextFile') is not null drop proc createTempTextFile

 go

 create proc createTempTextFile

 (

 @fn nvarchar(max) out 

 --the filename to output to (nb: environment variables don't currently work (e.g. you can't use %temp%\myFile.vbs)

 --works fine with spaces in filename (so far at least)

 --if user passes null a temporary filename will be auto allocated & returned in this variable

 , @content nvarchar(max)

 )

 as

 begin



 declare @charPos int

 , @cmd varchar(8000) --has to be varchar rather than nvarchar due to xp_cmdshell implementation



 if @fn is null or LEN(@fn)=0

 begin

 exec GetTempFilename @fn out

 end



 set @cmd = '">'@echo.>' + @fn --create a new file for our script output

 EXEC master..xp_cmdshell @cmd, no_output



 set @content = replace(@content,char(13) + char(10), char(10))--ensure uniform line endings (i.e. \r\n -> \n)

 set @content = replace(@content,char(13), char(10))--ensure uniform line endings (i.e. \r -> \n)

 set @content = @content + CHAR(10) --ensure last character of script is new line

 set @charPos = CHARINDEX(char(10),@content)

 while (@charPos > 0)

 begin

 --todo: consider what additional escaping is required to prevent injection issues

 set @cmd = '@echo.' + dbo.fn_EscapeDosCharachters(SUBSTRING(@content,1,@charPos-1)) + '>> ' + @fn

 EXEC master..xp_cmdshell @cmd, no_output

 set @content = SUBSTRING(@content,@charPos+1,len(@content))

 set @charPos = CHARINDEX(char(10),@content)

 end



 end 

 go

 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------



 if OBJECT_ID('deleteTempTextFile') is not null drop proc deleteTempTextFile

 go

 create proc deleteTempTextFile

 (

 @fn nvarchar(max)

 )

 as

 begin

 declare @cmd varchar(8000)

 if CHARINDEX(' ',@fn)>0 and CHARINDEX('"',@fn)>1 set @fn = QUOTENAME(@fn,'"')

 set @cmd = 'del ' + @fn

 EXEC master..xp_cmdshell @cmd, no_output

 end

 go

 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------





 if OBJECT_ID('sp_RunScript') is not null drop proc sp_RunScript

 go

 create proc sp_RunScript

 (

 @scriptId bigint

 , @arguments nvarchar(max)

 )

 as

 begin

 declare @timeoutSecs int

 , @batchMode bit

 , @script nvarchar(max)

 , @tempfileUri nvarchar(max)

 , @cmd varchar(8000) --has to be varchar rather than nvarchar due to xp_cmdshell implementation

 

 select @timeoutSecs=timeoutSecs 

 , @batchMode = batchMode

 , @script = script

 from vbsScripts 

 where id = @scriptId

 

 exec createTempTextFile @tempfileUri out, @script

 

 if CHARINDEX(' ',@tempfileUri)>0 and CHARINDEX('"',@tempfileUri)>1 set @tempfileUri = QUOTENAME(@tempfileUri,'"')

 set @cmd = 'cscript ' + @tempfileUri + ' //E:vbscript //NOLOGO '

 

 --batch mode or interactive

 if @batchMode=1 

 set @cmd = @cmd + '//B '

 else

 set @cmd = @cmd + '//I '

 

 --should script timeout after x seconds?

 if @timeoutSecs is not null

 set @cmd = @cmd + '//T:' + CAST(@timeoutSecs as nvarchar(18)) + ' '

 

 set @cmd = @cmd + isnull(@arguments,'')

 --select @cmd --if debugging enable this line to see the script file / etc

 

 EXEC master..xp_cmdshell @cmd --if required we can capture output as has been done in GetTempDirectory

 

 exec deleteTempTextFile @tempfileUri --tidyup the temp script - disable this line for debugging

 

 end

 go

 if OBJECT_ID('sp_RunScriptByName') is not null drop proc sp_RunScriptByName

 go



 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 /*

 provides a friendly interface to sp_RunScript

 */

 create proc sp_RunScriptByName

 (

 @scriptName nvarchar(256)

 , @arguments nvarchar(max)

 )

 as

 begin



 declare @id bigint



 select @id = id

 from vbsScripts 

 where name = @scriptName



 exec sp_RunScript @id, @arguments



 end

 go



 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 --demo



 --register a new script in the scripts table

 insert vbsScripts 

 select 'demo', '

 option explicit

 dim objFSO, i, path

 path = "c:\example1\"

 wscript.echo "hello" ''show what console output looks like (if interactive)

 for i = 0 to wscript.Arguments.Count-1 ''show that we can handle command line arguments

 wscript.echo wscript.arguments.item(i)

 next

 set objFSO = CreateObject("Scripting.FileSystemObject") 

 if not objFSO.FolderExists(path) then

 objFSO.CreateFolder(path) ''create a folder to demonstrate that the vbs is running / affecting the outside environment

 else

 wscript.echo "Folder " & path & " already exists."

 end if

 set objFSO = Nothing

 ', null, 0

 go



 --execute above script via friendly name

 sp_RunScriptByName 'demo','"this is a demo" "hopefully it will work" yes it does'