Developer42

2012-08-21

Run VBScript from SQL

Filed under: Microsoft, SQL Server, Technology — Tags: , , , — Developer42 @ 18:13

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'
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: