Simple Task: Connect To Database, Execute A Stored Procedure, Disconnect

I don't necessarily need to pass the stored procedures any variables from my VBScript, I just need to run the stored procedure on the server. I haven't been able to find any clear

Solution 1:

you can use the ADODB.Connection object from VbScript

check this sample

Dim sServer, sConn, oConn, sDatabaseName, sUser, sPassword
sDatabaseName="test"
sServer="localhost"
sUser="sa"
sPassword="yourpassword"
sConn="provider=sqloledb;data source=" & sServer & ";initial catalog=" & sDatabaseName

Set oConn = CreateObject("ADODB.Connection")
oConn.Open sConn, sUser, sPassword
oConn.Execute "exec sp_help"

WScript.Echo "executed"
oConn.Close
Set oConn = Nothing

Solution 2:

You can create a method like this:

PublicSub ExecuteSql( sqlString )
    Dim oConn
    Set oConn = Server.CreateObject("ADODB.Connection")
    oConn.Open connectionString
    oConn.Execute( CStr(sqlString) )
    oConn.Close
    Set oConn = NothingEndSub

Note: This routine assumes that the SQL statement was built by the calling routine and properly escaped. In addition, connectionString is a constant that you store somewhere with the connection string to the db.

Example call:

Call ExecuteSql( "exec MyProc" )