SQL Server has a number of prewritten, stored procedures that are used to configure and administer your SQL Server. All of these procedures have the prefix sp_ that distinguishes them from user-written or application-written stored procedures.
In order to call these stored procedures from RDO, you must specify the master database—the database in which they reside. One effective way to do this is to explicitly reference the stored procedure in your Call syntax. For example::
{ ? = call master.dbo.sp_addlogin(?,?) }
Another method is to set the current database before creating and executing your stored procedure. The following example uses a prepared statement and assumes that pubs is your current database:
<Object>.Execute "Use Master" 'rdoConnection object Set <Object> = <Object>.CreatePreparedStatement({call "sp_addlogin(?,?)}", strSQL) <Object>.Execute 'rdoPreparedStatement object <Object>.Execute "Use Pubs" 'rdoConnection object
To execute a stored procedure on SQL Server from RDO, perform the following steps:
Start a new project in Visual Basic. Form1 is created by default.
Add a Command button, Command1, to Form1.
Paste the following code into the General Declarations section of Form1.
Note You need to change your DATABASE, UID, and PWD parameters in the OpenConnection method.
Private Sub Command1_Click()
Dim en As rdoEnvironment
Dim cn As rdoConnection
Dim rs As rdoResultset
Dim strConnect As String
Dim strSQL As String
Set en = rdoEnvironments(0)
en.CursorDriver = rdUseOdbc
strConnect = "Driver={SQL Server}; Server=MyServer; " & _
"Database=pubs; Uid=sa; Pwd="
Set cn = en.OpenConnection(dsName:="", Prompt:=rdDriverNoPrompt, _
ReadOnly:=False, Connect:=strConnect)
Dim ps As rdoPreparedStatement
Note If you don't specify master in the following statement, you will get the error: "An invalid parameter was passed."
strSQL = "{ ? = call master.dbo.sp_addlogin(?,?) }"
Set ps = cn.CreatePreparedStatement("", strSQL)
ps.BindThreshold = 1024 'largest column that will be bound under ODBC.
Debug.Print ps.rdoParameters.Count
ps.rdoParameters(0).Direction = rdParamOutput
ps.rdoParameters(1).Direction = rdParamInput
ps.rdoParameters(2).Direction = rdParamInput
ps.rdoParameters(1) = "LoginID"
ps.rdoParameters(2) = "Password"
ps.Execute
Debug.Print ps.rdoParameters(0).Value
End Sub
Start the program or press the F5 key.
Click the Command1 button to execute the stored procedure. The parameter count and the output parameter will be displayed in the debug window.