How to Run VBScript from SQL Server 2008
SQL developers can run vb script files from SQL Server t-sql codes by using xp_cmdshell procedure.
It is sometimes a requirement especially for web developers to call vb script from SQL Server engine and execute vbscript from sql code.
Of course calling vbscript from sql can be solved by using CLR in SQL Server after the release of SQL Server 2005.
T-SQL developers and SQL Server database administrators can develop CLR objects and CLR procedures and use .NET Framework objects and methods instead of using sql VBScript.
I hope you will learn SQL Server sql vbscript call methods as many database professionals do on Microsoft data platform after reading this sql tutorial.
Here is a very simple vbscript example where vb script codes are stored in a text file named "vb-sample.vbs".
You can also create this vbscript file by running Notepad.exe and copy-paste the below vbscript code into it. Save and name the vbscript file as vb-sample.vbs in root folder of C drive for example.
Dim objFSO, objFile
Dim strDirectoryName, strFileName, strPath
strDirectoryName = "C:\"
strFileName = "vbscript-from-sql-server.txt"
strPath = strDirectoryName & strFileName
' Create File System Object objFSO using Scripting.FileSystemObject
Set objFSO = CreateObject("Scripting.FileSystemObject")
' Create file using CreateTextFile method with strDirectory parameter and strFile
Set objFile = objFSO.CreateTextFile(strPath)
Wscript.Quit
The above vbscript code create a text file name vbscript-from-sql-server.txt in root of C drive.
Here is execute VBScript sql query example for tsql developers and SQL Server database administrators can use in their applications.
EXEC xp_cmdshell 'CScript "c:\vb-sample.vbs"'
I can guess how you are astonished when you see how easy it is to call vbscript using sql code in MS SQL Server database.
Using tsql vbscript files can be executed by using the xp_cmdshell stored procedure which can reach Windows server objects as seen in this example.
When xp_cmdshell extended stored procedure is executed, the following error message might come up.
This error thrown from SQL Server engine is caused related with the SQL Server configuration settings.
On the current installed SQL Server instance, execute xp_cmdshell command might be prevented as a matter of security.
The default SQL Server configuration setting for xp_cmdshell is disabled by default with a value of "0" for run-value.
First set "show advanced options" configuration settings to 1 on the SQL Server 2008 instance using the below t-sql script.
exec sp_configure
go
exec sp_configure 'show advanced options', 1
go
reconfigure
go
If "show advanced options" settings parameter is 0 or disabled, trying to configure "xp_cmdshell" setting will cause the following error message.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.
So first enable 'show advanced options', then enable 'xp_cmdshell'
Then you are ready to configure xp_cmdshell configuration settings for SQL Server 2008 database instance using the below sql code.
Msg 15281, Level 16, State 1, Procedure xp_cmdshell, Line 1
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.
If you have not yet configured xp_cmdshell, run the below sql batch for configuring related parameters.
exec sp_configure
go
exec sp_configure 'xp_cmdshell', 1
-- Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.
go
reconfigure
go