Special Characteristics of System Procedures

What’s so special about system stored procedures? First, you only need one copy of the procedure, in the master database. The server automatically searches the master database for procedures starting with sp_ .

Second, SQL Server executes the procedure as though it exists in the current database. Let’s look at this more closely.

I am going to create a (regular) stored procedure called show_tables in a user database (not master). The SELECT statement in the procedure displays all objects with a type of U .

create  procedure  show_tables
a s
select  name,  user_name(uid)  "owner"
from    sysobjects
where   type  =  "U"
return

You can execute this procedure normally.

show_tables

name                           owner
------------------             ---------------------
authors                        dbo
publishers                     dbo
titles                         dbo
titleauthor                    dbo
stores                         dbo
sales                          dbo
roysched                       dbo
discounts                      dbo
jobs                           dbo
pub_info                       dbo
employee                       dbo
items                          dbo
orders                         dbo
trtest                         dbo

This runs predictably enough in its own database, but what happens if I change my database and run it again?

use  tempdb
go
show_tables

Msg  2812,  Level  16,  State  4

Stored procedure ‘show_tables’ not found.

This is the first noticeably different behavior from a system procedure: the procedure cannot be found from another database without being fully qualified with its database name. Let’s qualify the name and try again.

pubs..show_tables

name                           owner
------------------             --------------------
authors                        dbo
publishers                     dbo
titles                         dbo
titleauthor                    dbo
stores                         dbo
sales                          dbo
roysched                       dbo
discounts                      dbo
jobs                           dbo
pub_info                       dbo
employee                       dbo
items                          dbo
orders                         dbo
trtest                         dbo

The output is the same as before. No matter where the procedure is executed from, it executes within the database where it was created. In spite of the fact that the tempdb database contains its own version of the sysobjects table, the server displays the contents of the version of sysobjects in the database where the procedure was created.

The same procedure created as a system procedure behaves quite differently. Here’s the code, which must be executed by the sa from the master database:

create procedure sp_x_tables
as
select name, user_name(uid) “owner”
from sysobjects
where type = “U”
return

This system procedure will run from any database context without full qualification, and it will report the contents of the sysobjects table from the current database. All the standard system procedures shipped with SQL Server work in this way.

---

Tip:  If you are planning to write your own system procedures, add some sort of unique identifier to the name to make certain that your procedures don’t all get overwritten when you install a new release of the server.

---

Remote Procedures

Remote procedures allow you to execute stored procedures on a separate server.

A remote procedure is a procedure that is executed on a remote server. A remote server is a server accessed through your local server rather than directly from your client.

Figure 18.1.  Remote procedures are executed on a separate server.

---

NOTE:  The administrative issues associated with multiserver work are more complex than the programming work these days, especially with the current support for replication (loosely coupled multiserver work) and distributed transactions (tightly coupled multiserver work). That’s a change from the past years, when most of the hard work was done by programmers who managed the 2-Phase Commit (2PC) programming from a DB-Library program.

That being said, this book does not go into much detail on multiserver work. This brief section on remote procedures provides some general coding and execution advice, but that’s really all you will find here. If you need more information, the SQL Books online are a good place to start.

---

You write and create remote procedures the same way you do local procedures, so everything you have learned about procs so far applies. If you plan to write remote procs, the rest of this section describes their special characteristics. If not, you may want to skip this section and go on.

Setup Issues

Setting up remote procedures is an administrative function, not a programming function, so it doesn’t belong in this book. In case you’re stuck and have to do it yourself, here are a couple of things you need to do to make remote procedure execution work:

•  The servers have to be visible to each other across a network.

•  Both the remote and local server need to be listed in the sysservers table on each server. Use the sp_addserver stored procedure.

•  You need to establish a mapping between the login name of the user on the local server and a login name on the remote server. That remote login name needs the necessary privileges to execute the remote procedure.

The mapping of remote logins is pretty complex. There are three options, allowing single logins per server, grouped logins, or individual logins per user. The rest of the connectivity issues are simple by comparison.