Data Management

Database management through stored procedures

Joe Barnes describes how to use and create stored procedures to manage your database.

What are stored procedures? Are they database queries? Sometimes. Are they used by DBAs (database administrators) or DBOs (database owners)? Sometimes. So, what are they? The best description I’ve found for a stored procedure is that it’s a set of instructions written in the Transact-SQL (T-SQL) language and compiled and executed within a relational database.

Okay, our next topic: Why use stored procedures? As I mentioned, stored procedures ARE compiled. Basic translation: The instruction set that’s used by the computer to perform the operation requested is broken down to the lowest possible level available to the platform being worked on. (Bottom line: speed.) The alternative would be to create queries and database maintenance tasks that are executed each time the request is submitted—requiring your SQL Server to do much more work than necessary.

There are many other benefits to using and creating stored procedures, including the ability to centrally manage reusable tasks. Included in the latest version of Microsoft SQL Server are a number of prepackaged stored procedures for just this scenario.

In this article, we’ll discuss a few examples, focusing on the creation and management of databases. We’ll examine using these stored procedures within the Query Analyzer (which ships with SQL Server 7.0) and calling them remotely.

To begin, we have configured our server as follows:
  • Windows NT Server 4.0 with Service Pack 4
  • SQL Server 7.0 (full installation)
  • MDAC 2.0 (Microsoft Data Access Components)

Microsoft Management Console
Microsoft SQL Server (in its latest incarnation) is managed via MMC (Microsoft Management Console). MMC provides an interface to snap-ins (or programs such as SQL Server).

Through MMC, the DBO can administer all of the databases located on the server. In addition, security, jobs, data transformation, SQL server management, and support services may be managed from this interface.

As you drill down through the options available for each database, you’ll see the Stored Procedures menu option, shown in Figure A. Here, you’ll find existing stored procedures for the database being viewed.

Figure A
The MMC SQL Server snap-in

Query Analyzer
SQL Server 7’s tool-set is quite impressive. In addition to the integration within MMC, you’re provided with the ability to perform a great deal of fine-tuning using programs such as Microsoft’s Performance Monitor. Beyond the performance aspect, you’re provided with additional debugging tools such as Query Analyzer.

Query Analyzer gives you the ability to execute SQL queries and stored procedures in an easy-to-use interface, shown in Figure B. Query Analyzer may be used on a local or remote SQL Server. Using the interface, you can select the database you’re accessing and either write SQL code directly or execute stored procedures. We will be using Query Analyzer later in our example to test some of the stored procedures we’re accessing.

Figure B
Query Analyzer’s interface

Background: “Canned” stored procedures
The ability to call canned stored procedures is one of SQL 7’s greatest assets. During installation, a series of stored procedures is created and housed within the master database. The master database acts as a repository for SQL Server configuration information and common tasks.

Due to the sensitive nature of the information and tools available in the master database, only members of the sysadmin group can perform any activity (as shown in Figure C). Please keep this in mind as we run through our examples.

Figure C
Only members of the sysadmin group can perform any activity.

The first canned stored procedure we will use is sp_create_removable. This is an incredibly useful stored procedure (for DBAs as well as developers). The purpose of this stored procedure is to allow for the creation of a database (through code) that is easily moveable to any type of removable media device.

In order to use this stored procedure, we will review the syntax, arguments, and return codes.

Making the call
The syntax of this stored procedure is as follows:
sp_create_removable @dbname = ‘dbname’

The arguments associated with the call are broken down into three main groups: system information, log information, and data information. The system data is contained within the “syslogical,” “sysphysical,” and “syssize” parameters. The syslogical argument represents the logical file name. The sysphysical represents the physical file path and name. The syssize parameter represents the file size in megabytes. Each parameter is required, and the same format is used for the log information and data information to follow.

For example, if we were to create the “foTrack” database using this stored procedure, it would look something like Figure D.

Figure D
sp_create_removable as seen in the Query Analyzer

When this stored procedure executes, a return code of 0 is returned if it’s successful. If the stored procedure produces an error, a return code of 1 is returned. This is very useful when accessing these examples through code.

Now that our database has been created, let’s test the removal from the server. Please note, once this stored procedure is executed, the physical files still reside on the server. The detach process simply instructs the SQL Server not to recognize the database as part of the SQL Server group. This is accomplished by the removal of the database record from the sysdatabases table in the master database.

The syntax of this stored procedure is as follows:
sp_detach_db @dbname = ‘dbname’

The only argument associated with this stored procedure is the UPDATE STATISTICS command. This argument is supplied in the form of a true or false statement.

Again, if the stored procedure is successful, a return code of 0 is returned. If the stored procedure isn’t successful, a return code of 1 is returned.

In order to execute this stored procedure on the foTrack database, the syntax would look like what’s shown in the top box in Figure E.

Figure E
sp_detach_db as seen in the Query Analyzer.

Once the database has been detached from the server, there are a number of tasks you can perform with it. For the purpose of keeping this example simple, we will simply reattach it.

The sp_attach_db stored procedure is similar in syntax to the sp_detach_db call. The syntax is as follows:
sp_attach_db @dbname = ‘dbname’,
 @filename = ‘filename_n’[,…16]

The filename argument represents any and all files associated with the database. In the sp_create_removable example, we started with three files: fosysphysical.mdf, fologphysical.ldf, and fodataphysical.ndf.

Our foTrack example will resemble what’s shown in Figure F.

Figure F
sp_attach_db as seen in the Query Analyzer

Calling remotely
As you can probably imagine, there are a number of ways to use these stored procedures. There are also a number of ways you can call these stored procedures.

Other than using Query Analyzer (as in our previous examples), stored procedures may be called using ADO (ActiveX Data Objects). ADO provides an interface to several data sources. In particular, ADO provides a number of ways to access Microsoft SQL Server.

In the following example, we’ll provide access to the foTrack database using ADO and Visual Basic. We’ll call the sp_attach_db stored procedure.
This is one of many ways to accomplish this goal. For more information on ADO and SQL Server, please visit the Microsoft Data Access Web site .Public Function sp_attach_db() as Integer
Dim con As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim strConStr As String
Dim strSrvName As String
Dim strDBName As String
Dim strUID As String
Dim strPass As String
Dim strIn, strIn2, strIn3 As String

strSrvName = "OFFSRV1"
strDBName = "foTrack"
strUID = "sa"
strPass = ""
strConStr = "Data Source=" & strSrvName & _
 & "; User Id=" & strUID & ";Password=" & strPass & ";"
strComText = "sp_attach_db"

strIn = "c:\mssql7\data\fosysphysical.mdf"
strIn2 = "c:\mssql7\data\fologphysical.ldf"
strIn3 = "c:\mssql7\data\fodataphysical1.ndf"

With con
.Provider = "SQLOLEDB"
.ConnectionString = strConStr
End With

If con.State = 1 Then
cmd.ActiveConnection = con

‘ Set the type of command your using equal
‘ to a stored procedure.

cmd.CommandType = adCmdStoredProc
cmd.CommandText = strComText

‘ The following debug.print statements assist
‘ in ensuring you have the right variables for the
‘ stored procedure’s arguments.

Debug.Print cmd.CommandText
Debug.Print cmd.Parameters(0).Name
Debug.Print cmd.Parameters(1).Name
Debug.Print cmd.Parameters(2).Name

‘ Set the value (s) for the arguments and
‘ execute the stored procedure.

cmd.Parameters(1) = strDBName
cmd.Parameters(2) = strIn
cmd.Parameters(3) = strIn2
cmd.Parameters(4) = strIn3

sp_attach_db = cmd.State
End If
End Sub

As you can see, there are many potential hidden treasures within SQL Server 7. In addition to the stored procedures, there are numerous wizards that handle everything from data transformation (importing and exporting) to Web publishing. By utilizing the tools provided, you can accomplish a great deal in a short amount of time. Look around—you may be surprised. Happy hunting!

Joe Barnes is a senior project manager for Fundamental Objects, Inc., and he works with large-scale infrastructure and software development projects. His specialties include Windows NT, Visual Basic, and Lotus Notes. If you’d like to contact Joe, send him an e-mail .

The authors and editors have taken care in preparation of the content contained herein, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.

Editor's Picks

Free Newsletters, In your Inbox