Other links about these System Stored Procedures(SSPs):
- the location to download
the software;
- the ISUG Tech Journal article
discussing background and
architecture of these SSPs;
- the manual
pages for the SSPs.
Q1.1
Why do these stored procedures have a name starting with
"sp_rv_" ?
Q1.2 Can I rename the procedures
from "sp_rv_..." to something else ?
Q1.3 What makes these "New
Generation SSPs" different from the normal Sybase
SSPs ?
Q1.4 What's the basic structure
of these "New Generation SSPs" ?
Q1.5 Why has the code been
"obfuscated" ?
Q1.6 So is it safe to install
and use stored procs if you cannot verify what they're
doing ... ?
Q1.7 What about the
"execute immediate" feature in ASE 12.0 ?
Q1.8 Is it allowed to bundle or
sell these SSPs ?
Q2.1 Why is the following
function not implemented: ...... ?
Q2.2 Will these SSPs also run on
MS SQL Server ?
Q2.3 Is there any support for
these SSPs ?
Q3.1 I've accidentally dropped
the tables in sybsystemprocs. What do I do now ?
Q3.2 Shouldn't those
sybsystemprocs tables be cleaned up ?
Q3.3 Why does
"sp_rv_update_statistics 'execute' " not work ?
Q1.1
Why do these stored procedures have a name starting with
"sp_rv_" ?
This is to distinguish these procedures
from the standard ASE system stored procedures: many DBAs
like their non-default SSPs named in some special way to
indicate the fact that these are not the standard Sybase
SSPs that "sybsystemprocs" normally contains.
So why is it "sp_rv_", and not, say,
"sp_xyz_" ? Well, check out my initials ...
Q1.2
Can I rename the procedures from "sp_rv_..." to
something else ?
It is best not to change the "sp_rv_"
prefix for these stored procedures, as certain functions
won't work correctly anymore. The only exception is
"sp_rv_dboption" which you can rename as you
like.
Q1.3
What makes these "New Generation SSPs"
different from the normal Sybase SSPs ?
The main feature of these "New
Generation SSPs" is that you can perform actions in
different databases in the server in just a single
command (like adding a datatype to all databases in the
server). The traditional SSPs by Sybase do not have this
capability, meaning that you'd have to execute a command
repeatedly for each database. Furthermore, in many cases
there's no need for the "use" command to switch
databases anymore; this also means many of the
shell-script tools that DBAs have created over time are
not needed anymore.
In short, these SSPs will give DBAs more control in a
multi-database environment, allowing them to work more
efficiently.
Q1.4
What's the basic structure of these "New Generation
SSPs" ?
Basically, all procedures are based on two simple T-SQL
features, which are described in detail in the ISUG Tech
Journal article . Check out
the source code of "sp_rv_dboption" : it's the
best example of how these features are used. For the
other SSPs, a significant amount of functionality has
been engineered on top of these basic principles.
Q1.5
Why has the code been "obfuscated" ?
As of version
2.1 , the SQL is published
without attempts to hide anything. At the time, there
were reasons for keeping things hidden, but these are not
relevant anymore now.
Q1.6 So
is it safe to install and use stored procs if you cannot
verify what they're doing ... ?
N/A. See previous point.
Q1.7
What about the "execute immediate" feature in
ASE 12.0 ?
The next major release
of ASE (version 12.0, a.k.a. "Avatar") will
contain the long-awaited "execute immediate"
feature. This feature makes it possible to execute a
T-SQL command string which is dynamically created in a
character variable. As many ASE users expect this feature
to deliver significant new flexibility, one might wonder
whether the existence of the new-generation SSPs
described in this article is still justified in version
12.0. It certainly is: the "execute immediate"
functionality only overlaps with "feature #2"
discussed in the
ISUG Tech Journal article
(executing a variable stored
procedure); "feature #1" (controlling the
database context), being the other building block of
these SSPs is not related to "execute
immediate" at all. Furthermore, "execute
immediate" does not support the "use"
command, while the new SSPs will also work in ASE
versions 10.x to 11.9.x.
Note:
version 2.1
uses "execute immediate" in
various ways. Check it out!
Q1.8
Is it allowed to bundle or sell these SSPs ?
No. It is explicitly not allowed to use
these SSPs for any commercial purpose. You may only use
these for your own professional, non-commercial purposes.
Please check out the copyright
issues .
If you see great commercial opportunities for these SSPs,
we would need to talk first...
Q2.1
Why is the following function not implemented: ...... ?
Obviously, it wasn't possible to implement
everything I could think of. So if you feel a certain
feature would be really useful, please E-mail
me your idea and I'll do my best to stick
it in...
Q2.2
Will these SSPs also run on MS SQL Server ?
No, they won't. These SSPs are written
specifically for ASE, using ASE-specific features.
Porting these SSPs to MS SQL Server could be an
interesting thing to do, but nothing is planned.
Q2.3
Is there any support for these SSPs ?
There's no guarantee of getting
support, but having said that, I'll try to help when
possible. In case you encounter any problems, please post
a message to the newsgroup
"comp.databases.sybase", and I'll try to help
out if my own schedule allows. You could of course also
send me your problems directly through E-mail, but I'll
handle newsgroup questions with higher priority.
Q3.1
I've accidentally dropped the tables in sybsystemprocs.
What do I do now ?
There are two tables in the
"sybsystemprocs" database
("tab_sp_rv_incl_db" and
"tab_sp_rv_excl_db"), which contain the
settings made through "sp_rv_exec_include_db"
and "sp_rv_exec_exclude_db". If these tables
have disappeared, "sp_rv_exec" and the other
SSPs won't run anymore, but generate lots of error
messages.
If this happens, you can simply repair this by running
"sp_rv_exec_crtab", which will recreate the
tables.
There is also a table named "tab_sp_rv_msgs",
which only contains informational messages (the stuff
that's printed when you use '?' as a parameter). To
restore this table, you must re-run the installation SQL
script (new_ssp.sql).
Q3.2
Shouldn't those sybsystemprocs tables be cleaned up ?
The tables "tab_sp_rv_incl_db"
and "tab_sp_rv_excl_db" contain the settings
made through "sp_rv_exec_include_db" and
"sp_rv_exec_exclude_db". These
settings are session-specific, and because they're not
automatically cleaned up when the session exits, there
may be some accumulation of "old" rows. Every
now and then, "sp_rv_exec" will try to clean up
rows which are obviously obsolete, so the tables
shouldn't get too large.
If you wish, you can also manually delete the contents of
these tables, but be aware that you may affect settings
for currently active sessions.
Q3.3
Why does "sp_rv_update_statistics 'execute' "
not work ?
In order to run "sp_rv_update_statistics" with
the "execute" option, you need to be able to
use CIS features, which are available only in ASE 11.5
and later (except on AIX, where you'll need 11.9.2 or
later). If you run an earlier version of ASE, you cannot
use the "execute" option, but only the
"generate" option.
When running "sp_rv_update_statistics", the
server configuration is examined, and if necessary you'll
be prompted to run "sp_rv_cis_setup1", restart
the server, and run "sp_rv_cis_setup2". This
will change some server configuration parameters to allow
CIS to run properly. In case you still get errors, you
probably have a CIS problem. Verify your CIS setup as
follows (assuming your servername is YOURSERVER) :
1> connect to YOURSERVER_MYSELF
2> go
Entered passthru mode to server 'YOURSERVER_MYSELF'.
1> disconnect
2> go
Exited passthru mode from server 'YOURSERVER_MYSELF'.
If you don't get the
message "Entered passthru mode to server
'YOURSERVER_MYSELF'", this means there is a CIS
problem. Check the server errorlog for messages
indicating CIS didn't initalise properly.
If the above worked properly, try the following command :
exec sp_remotesql YOURSERVER_MYSELF, "select getdate()"
This should return the
current date and time. Basically, when these example
statements don't work, you'll have to get this working
first.
In case you've customised the remote access
authorisations, check if you have object owner
permissions (or better: "dbo" permissions)
after connecting to YOURSERVER_MYSELF: you need the
proper permissions to run "UPDATE STATISTICS".
|