Execute VBS a partir de SQL

Código:

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. rn -> 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


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

Exemplo de uso:

--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""

'