How To Call SQL Server System Stored Procedures from RDO

MORE INFORMATION

1. Start a new Standard EXE project. Form1 is created by default.
2. Add a CommandButton (Command1) to Form1.
3. From the Project menu, select References, and place a check next to Microsoft Remote Data Object 2.0.
4. Paste the following code into the General Declarations section of Form1:

         Private Sub Command1_Click()
            Dim cn As New rdoConnection
            Dim qr As New rdoquery
   
            With cn
            .Connect = "Driver={SQL Server};Server=MyServer;" & _
                     "Database=pubs;Uid=<username>;Pwd=<strong password>"
            .EstablishConnection Prompt = rdDriverNoPrompt
            End With
   
            With qr
               Set .ActiveConnection = cn
               'Note: If you don't specify master, you will get the following
               'error: "An invalid parameter was passed."
               .SQL = "{ ? = call master.dbo.sp_addlogin(?,?) }"
               .Prepared = True
               .BindThreshold = 1024 'largest column that will be bound under
                                     'ODBC.
               Debug.Print .rdoParameters.Count
               .rdoParameters(0).Direction = rdParamOutput
               .rdoParameters(1).Direction = rdParamInput
               .rdoParameters(2).Direction = rdParamInput
               .rdoParameters(1) = "Edward"
               .rdoParameters(2) = "Scissorhands"
   
               .Execute
               Debug.Print .rdoParameters(0).Value
               'Const SQL_SUCCESS As Long = 0
               'Const SQL_ERROR As Long = -1
            End With
         End Sub
   
   						

5. Note that you need to change your DATABASE, UID, and PWD parameters in the Connect Property.
6. Start the program or press the F5 key.
7. Click the Command1 button to execute the stored procedure and display the parameter count and the output parameter in the debug window.