Simple task: Connect to database, execute a stored procedure, disconnect

Question

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 examples of how to do this—just a lot of people explaining how to pass a variable from a SP back to a VBScript.

Any help would be so appreciated! It looks like I'll have to open a connection, then send the command to execute the stored procedure, then close the connection, but I'm a bit lost about how to do this from a VBscript.

Thanks!

Hi, a friendly tip, saying "M$" will actually make many of the people that *could* answer your question just turn away from it. Your opinions with Microsoft nonwithstanding, airing them out in the open might not give you the real benefits of Stack Overflow. — Lasse V. Karlsen, May 13 '11 at 21:30

score 8 · Accepted Answer · edited Jul 14 '15 at 18:51

8

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

edited Jul 14 '15 at 18:51

KyleMit

30,350
66
462
664

answered May 13 '11 at 21:35

RRUZ

134,889
20
356
483

This is perfect! Thank you so, so much! – Joshua May 15 '11 at 16:53

score 2 · Answer 2 · edited Jul 14 '15 at 18:52

2

You can create a method like this:

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

edited Jul 14 '15 at 18:52

KyleMit

30,350
66
462
664

answered May 13 '11 at 21:25

Thomas

63,911
12
95
141

Thomas, thank you for the reply. I'm also unsure of how to setup the connectionString, could you point me in the right direction? Thanks again! – Joshua May 13 '11 at 21:34
1

@Joshua - Try www.connectionstrings.com. – Thomas May 13 '11 at 21:36

Simple task: Connect to database, execute a stored procedure, disconnect

2 Answers2

Linked