Technical Article

Run SQL Hosted VBS Script from SQL

,

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'

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating