Simple task: connect to the database, execute the stored procedure, disconnect

Question

I don’t have to pass stored procedures to any variables from my VBScript, I just need to run the stored procedure on the server. I could not find any clear examples of how to do this - just many people explain how to pass a variable from SP back to VBScript.

Any help would be greatly appreciated! It looks like I will need to open a connection, and then send a command to execute the stored procedure, and then close the connection, but I lost a little about how to do this from VBscript.

Thank!

Answer 1

you can use 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 

Answer 2

You can create this way:

 Public Sub ExecuteSql( sqlString ) Dim oConn Set oConn = Server.CreateObject("ADODB.Connection") oConn.Open connectionString oConn.Execute( CStr(sqlString) ) oConn.Close Set oConn = Nothing End Sub 

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

Call example:

 Call ExecuteSql( "exec MyProc" )