When a user proxy database is created, metadata for the proxy tables is imported automatically from the remote location that contains the actual tables. This metadata is then used to create proxy tables within the proxy database.
To create a proxy database, use:
create database <dbname> [create database options] [with default_location = ’pathname’] [for proxy_update]]
The use of the clause with default_location allows you to specify the storage location of any new tables, and the location from which metadata may be imported for automatic proxy table creation if the for proxy_update clause is also specified. for proxy_update establishes the database as a proxy database; with default_location defines the location from which proxy tables are imported. Without for proxy_update, the behavior of with default_location is the same as that provided by sp_defaultloc — a default storage location is established for new and existing table creation, but automatic import of proxy table definitions does not take place during the processing of the create database command.
The value of path name is a string identifier in the following format: servername.dbname.owner.
servername – required field; represents the name of the server that owns the objects to be referenced by proxy tables. Must exist in master.dbo.sysservers.srvname.
dbname – optional. The name of the database within servername which contains objects to be referenced by proxy tables
owner – optional. The name of the owner of objects to be referenced by proxy tables. This may be restrictive, so that if more than one user owns objects in dbname, specifying the owner selects only those objects owned by that user. Do not create proxy tables for objects owned by other users.
If for proxy_update is specified with no default_location, an error is reported.
When a proxy database is created (using the for proxy_update option), Component Integration Services functions are called upon to:
Provide an estimate of the database size required to contain all proxy tables representing the actual tables/views found in the primary server’s database. This estimate is provided in terms of the number of database pages needed to contain all proxy tables and indexes. This size is used if no size is specified, and no database devices are specified. This formula is described in create database.
If the database is created with specific size specifications [on device_name = nn],
or if a device name is specified with no size [on device_name],
then the size requirements for the proxy database are not estimated;
it is assumed in this case that the user or data base administrator wants
to override the default size calculated for the proxy database.
If you are importing metadata from another Adaptive Server, remote database users are imported before proxy tables are created. Each imported database user must have a corresponding system user name in syslogins.
Create all proxy tables representing the actual tables/views found in the companion server’s database. Proxy tables are not created for system tables.
Before you create the proxy tables, turn on the quoted
identifier state, and create each table with quotes surrounding
the table name and column name. This allows the creation of tables
containing names that may be Transact-SQL reserved words. When all
proxy tables are created, the quoted identifier state is restored
to its original setting.
Grant all permissions on proxy tables to “public.”
Add the “guest” user to the proxy database.
Import database users from remote site (if Adaptive Server).
Grant create table permission to “public.”
Set the database status to indicate that this database is a user proxy database. This is done by setting a status field in master.dbo.sysdatabases.status3 (0x0001, DBT3_USER_PROXYDB).
After the database has been created, it contains a proxy table for each table or view found in the default location. The behavior for a user proxy database is identical to prior database behavior. Users can create additional objects, such as procedure, views, rules, defaults, and so on, and both DDL and DML statements that operate on proxy tables behave as documented in this book.
The only exception to this is the alter database command. The syntax and capabilities of this command are described in the next section.
