How to Call System Stored Procedures on SQL Server from RDO

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.