Wednesday, March 28, 2012

Running VBScript from an SQL Server Stored Procedure

Hi,
I would like to store VBScripts either within a SQL Server database
table or from within Stored Procedure.
The VBScripts are used for basic administration tasks, and by storing
them in the database would make them more portable between customers as
it removes the necessity of a creating a directory structure.
Can someone please advise me on the best method to store and run
VBScripts.
TIA
Chris aka BoobBoo
> I would like to store VBScripts either within a SQL Server database
> table or from within Stored Procedure.
Yikes, why?

> The VBScripts are used for basic administration tasks, and by storing
> them in the database would make them more portable between customers as
> it removes the necessity of a creating a directory structure.
To execute them, you will need to use wscript, and wscript will need to find
a VBS file somewhere... yup... in the file system.
So, put it in the file system somewhere (pretty safe place is creating a
folder for all customers called, say, c:\customScripts) and then you can
execute them from T-SQL like this:
EXEC master..xp_cmdshell 'wscript c:\customScripts\myScript.vbs'
Note that the script must be self-sufficient, e.g. it cannot raise any
inputBox or MsgBox or anything along those lines... SQL Server always has a
"do not disturb" sign up for those things, and you will sit there forever
waiting for someone to answer the prompt...
A

No comments:

Post a Comment