Synonyms is a type of SQL object that was added in SQL 2005. A synonym permits you to access an object with a different name. Synonyms can be handy when you need to do cross-database or cross-server access. The problem with this type of access is that you can't tell beforehand which name the other database or server will have, and the name could change when a new test environment is set up. By defining synonyms for the objects in the other database/server, you don't need to litter your code with dynamic SQL to handle the database/server name. All you need to do is to change the definition of the synonyms. AbaPerls supplies a stored procedure for this task.
Let's say that you work with a suite of stored procedures in the SUBSIE subsystem that need to access tables and stored procedure in a different database, maybe on a different server. I will refer to that database as the target database. Once you have identified which objects you need to access in the target database, you create a file with the extension .syno in the MESSAGE directory of the SUBSIE subsystem (or some inner subsystem) to hold the synonym definitions. Let's assume that the file is called mysynonyms.syno. Here is an example how the file could look like:
CREATE SYNONYM otherdb_orders FOR orders CREATE SYNONYM otherdb_order_details FOR order_details CREATE SYNONYM otherdb_products FOR products CREATE SYNONYM otherdb_order_get_sp FOR order_get_sp
When you load a synonym file, you must specify the subsystem with the
‑subsystem option, like you need to do with many other file types as well.
As you can see, the synonym definition does not include any database name, and in fact AbaPerls will give you an error if you do. When you create the file initially, AbaPerls will assume tempdb as the remote database.
Next, you use the stored procedure ap_sob_retarget_synonyms_sp to change the definitions of the synonyms to point to the actual target database you will work with. You could put the call to this procedure in a .postsql file, but in most cases you don't want to do that, because typically the name of the target database will be different in different environments. Thus, you run this procedure when you need to target a new database.
ap_sob_retarget_synonyms_sp takes four parameters:
|@subsystem||The subsystem for the synonym file.|
|@filename||The name of the synonym file, in this example mysynonyms.syno.|
|@dbname||The database in which the target objects reside. You can pass this parameter as NULL or the empty string to denote that the objects reside in the local database. You cannot leave this parameter NULL/empty if you specify a server.|
|@server||The remote server for the target objects, if any. There is a default of NULL for the local server.|
In your case the target objects are in the database thatdb, so you run:
EXEC ap_sob_retarget_synonyms_sp 'SUBSIE', 'mysynonyms.sym', 'thatdb'
As a receipt of the operation, ap_sob_retarget_synonyms_sp lists the synonyms in the file, and their new definition:
otherdb_orders [thatdb].[dbo].[orders] otherdb_order_details [thatdb].[dbo].[order_details] otherdb_products [thatdb].[dbo].[products] otherdb_order_get_sp [thatdb].[dbo].[order_get_sp]
After you have been working for some time, you realise that your original pick of synonyms was not the best, so you edit mysynonyms.syno to read:
CREATE SYNONYM otherdb_orders FOR orders CREATE SYNONYM otherdb_order_details FOR order_details CREATE SYNONYM otherdb_order_get_sp FOR order_get_for_remote_sp CREATE SYNONYM otherdb_order_put_sp FOR order_put_sp
That is, you deleted one definition, changed one and you added a new one.
When AbaPerls loads the file, it will find that the file is already stored in the database, and will therefore retrieve the current server/database part of the synonyms. AbaPerls then drops all synonyms associated with the file, and then loads the synonyms as now listed in the file. But this time AbaPerls does not tack on tempdb in the definition, but instead restores the server/database part that already was there. Thus, you don't need to run ap_sob_retarget_synonyms_sp just because you reloaded the synonym file. Note that AbaPerls assumes that all synonyms associated with the file have the same server/database part. If that is not the case (because someone has tampered with the synonyms outside AbaPerls), the result is undefined.
Since AbaPerls handles all synonyms in a file as a unit, it follows that if you target objects could be spread out over several databases, you should put them in different files.
If the synonym definition starts with a number character (#), AbaPerls does not add any server/database part to the definition.
As you have seen, AbaPerls handles synonyms somewhat different from other type of objects. Here are the key differences:
When you build an empty database DBBUILD, synonym files are created very early, after the .sql files, and likewise they appear early in the update scripts generated by DBUPDGEN.
If a synonym could be accessed directly by a user, the user will need to have permission on the synonym, as well as the underlying object. In this case, you will need to add entries to grant.template to define the permissions for the synonyms. Since a synonym can refer to different sort of objects, different synonyms may seem to need different permissions. But if you as a matter of routine want to grant SELECT on all tables and EXEC on all stored procedures, you can do this in grant.template:
GRANT EXEC, SELECT ON .* TO dvp -- SYNONYM
When you run ap_sob_retarget_synonyms_sp, it makes sure that the permissions are retained when it drops and recreates the synonyms.
Copyright © 1996-2011,
Erland Sommarskog SQL-Konsult AB.
All rights reserved. AbaPerls is available under Perl Artistic License
This page last updated 11-04-29 17:14