Though it’s fairly well publicised how to call an existing VBS file from SQL, or how to execute VBScript within a SQL agent, there’s not much info out there on how to have SQL run VBS directly. The code below allows you to manage your scripts in a database table, creating temp files to host and run the scripts as required. The sp_RunSQL stored proc can also be used to run any VBS script; including dynamically generated code.
As always, feedback, suggestions, etc are welcome.
Code:
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,'%','%%') --based on experience 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 ( @script nvarchar(max) , @arguments nvarchar(max) , @timeoutSecs int = null , @batchMode bit = 1 , @tempfileUri nvarchar(max) out ) as begin declare @cmd varchar(8000) --has to be varchar rather than nvarchar due to xp_cmdshell implementation 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
if OBJECT_ID('sp_RunScriptByID') is not null drop proc sp_RunScriptByID go create proc sp_RunScriptByID ( @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 sp_RunScript @script , @arguments , @timeoutSecs , @batchMode , @tempfileUri out end go
if OBJECT_ID('sp_RunScriptByName') is not null drop proc sp_RunScriptByName go /* provides a friendly interface to sp_RunScriptByID */ 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_RunScriptByID @id, @arguments end go
Example Usage:
--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 on error resume next objFSO.CreateFolder(path) ''create a folder to demonstrate that the vbs is running / affecting the outside environment if err.number = 0 then wscript.echo "Folder " & path & " successfully created." else wscript.echo "Folder " & path & " was not created. " & cstr(Err.number) & ": " & Err.Description err.clear end if on error goto 0 else wscript.echo "Folder " & path & " already exists." end if set objFSO = Nothing wscript.echo "Done" ', null, 0 go --execute above script via friendly name sp_RunScriptByName 'demo','"this is a demo" "hopefully it will work" yes it does'