Practice: Execute a Stored Procedure from the MS Access Client
Our next steps will involve the simple execution of a Stored
Procedure to get a feel for this straightforward process. To do this in
the easiest manner, we will use a procedure that we know to exist in MSSQL
Server 2000 already. Whether we have ever created a Stored Procedure in
our copy of the Pubs sample database or not, we can still experience the
execution of a Stored Procedure by using a System Stored Procedure.
The master database on MSSQL Server contains a sizable
collection of administrative Stored Procedures that come along as part
of the installation. These procedures, prefixed with sp_, can be very
useful in managing and monitoring the database in general. I have found some
innovative uses for these procedures, particularly in metadata reporting and
other contexts, while working with clients over the years. My purpose in
bringing System Stored Procedures into our current focus is simply to explain
that we are using a Stored Procedure that already exists on the
server, as it is automatically placed there from the start. This way, we
can initially focus on the execution of the Stored Procedure,
without the distraction of creating a procedure to execute first.
Let's say that we have been asked by the leader of a project
team within the organization to provide a simple means of monitoring the
databases on the back-end server. The users of this functionality will be MS
Access developers who do not have MSSQL client utilities on their machines. (The
back-end development server, to which the developers have ODBC connectivity -
with access privileges - similar to that we have established in the forgoing
section, is in another state.)
We are told that the developers need to be able to easily
obtain size information about all the databases on the server, to be able to
monitor their own consumption of resources as they perform data imports, table updates
and other activities, from the evolving front-end environment.
Because the requirements are minimal, and because we want a "quick
and dirty" solution, we will use a System Stored Procedure, sp_helpdb,
which provides a list of the databases on the server, along with size
information and a few other facts about each, in a minimally formatted
recordset.
We will meet the needs of the information consumers by
taking the following steps:
1.
Type the
following command into the SQL
Pass-Through Query window:
EXECUTE sp_helpdb
(We might have used EXEC, instead of EXECUTE,
to obtain the same results. For more information on the System Stored
Procedures, as well as for Stored Procedures in general, see the
MSSQL Server 2000 Books Online, or other MSSQL Server SQL
references. Our purpose here is to investigate the execution of a back-end Stored
Procedure from an MS Access client, rather than to perform an in-depth
examination of Stored Procedures themselves.)
The SQL
Pass-Through Query window appears, with command in place, as shown in Illustration
22.
Illustration 22: SQL Pass-Through Query Window, with our EXECUTE
Command in Place
The Stored Procedure through which we seek to
interact directly with the back-end server is now in place. Like a Pass-Through
query, the command will avoid the "taxation" of simply executing a query
to obtain the same information, by bypassing two translation steps within the
JET engine and ODBC, merely flowing through them to the back-end RDBMS instead.
2.
Execute the
command by clicking the Run button (see Illustration 23) on the
toolbar.
Illustration 23: Execute the Command using the Run Button
As was the case with the Pass-Through query in our
earlier article, had we not saved the connection information, or had we forgone
the insertion of the connection string in the ODBC Connect Str box of
the QueryProperties page, we would be prompted for connection /
logon information at this point.
The command executes sp_helpdb, and the results
dataset is returned, as partially shown in Illustration 24.
Illustration 24: The Results Dataset Appears (Partial View)
NOTE:Results will obviously reflect the
databases that reside on your own server.
We thus see the results of executing a SQL Server System Stored
Procedure against a back-end server using the Pass-Through query
conduit in MS Access.
3.
Select File
--> Save As and name the query Server DB
Information, as shown in Illustration 25.
Illustration 25: Saving the Stored Procedure Command for
Re-use
4.
Click OK.
5.
Close the
results dataset view.
We see
the new Stored Procedure displayed, alongside the characteristic Pass-Through
icon, as shown in Illustration 26.
Illustration 26: The New Stored Procedure Command Appears
in the Query View
Having saved the Stored Procedure as a Pass-Through
query object, we have left it in place for reuse at any time that the
development team needs to examine space statistics, meeting the need expressed
by the project manager. Next, we will examine how we might handle a specific reporting
need with data from the Pubs database - specifically, with a Stored
Procedure we create for that purpose - all from inside MS Access.
» 
Comment and Contribute
