Microsoft KB Archive/153234

= How To Call System-Stored Procedures on SQL Server from RDO =

Article ID: 153234

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Visual Basic 6.0 Learning Edition
 * Microsoft Visual Basic 6.0 Professional Edition
 * Microsoft Visual Basic 6.0 Enterprise Edition
 * Microsoft Visual Basic 4.0 32-Bit Enterprise Edition

-



This article was previously published under Q153234



SUMMARY
This article describes 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 the SQL server. All of these procedures have the prefix "sp_" that distinguishes them from user- or application-written stored procedures.

In order to call these stored procedures from RDO, you must specify the correct database is which they reside, the master database. There are several ways to do this, but the most effective way is to explicitly reference the stored procedure in your call syntax. For example:

{ ? = call master.dbo.sp_addlogin(?,?) }

Another method to accomplish this behavior is to set the default database before creating and executing your stored procedure. The following example uses a prepared statement and assumes that "Pubs" is your default database:

.Execute "Use Master" 'rdoConnection object Set  = .CreatePreparedStatement("", strSQL) .Execute              'rdoPreparedStatement object .Execute "Use Pubs"   'rdoConnection object



MORE INFORMATION
 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 must change the database and UID = and PWD = to the correct values before you run this code. Make sure that UID has the appropriate permissions to perform this operation on the database. 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= ; Pwd= " Set cn = en.OpenConnection(dsName:="", Prompt:=rdDriverNoPrompt, _      ReadOnly:=False, Connect:=strConnect)

Dim ps As rdoPreparedStatement

'Note: If you don't specify master, you will get this following 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

\* Note to KB editor: don't change this to "John Doe" ps.rdoParameters(1) = "Edward" ps.rdoParameters(2) = "Scissorhands"

ps.Execute Debug.Print ps.rdoParameters(0).Value End Sub

 Start the program or press the F5 key.</li> Click the Command1 button to execute the stored procedure and display the parameter count and the output parameter in the debug window.</li></ol>

<div class="references_section">