(Component Integration Services only) Defines a default storage location for objects in a local database.
sp_defaultloc dbname, defaultloc, defaulttype
is the name of a database being mapped to a remote storage location. The database must already have been defined by a create database statement. You cannot map system databases to a remote location.
is the remote storage location to which the database
is being mapped. To direct the server to delete an existing default
mapping for a database, supply NULL for this parameter. The value
of defaultloc must end in
a period (.), as follows:
server.dbname.owner.
is one of the values that specify the format of the object named by object_loc.The valid values are as follows. Enclose the defaulttype value in quotes:
table – indicates that the object named by object_loc is a table accessible to a remote server. This value is the default for defaulttype.
view – indicates that the object named by object_loc is a view managed by a remote server, processed as a table.
rpc – indicates that the object named by object_loc is an RPC managed by a remote server; processes the result set from the RPC as a read-only table.
sp_defaultloc defines
the remote storage location pubs.dbo. in the remote
server named SYBASE. It maps the database pubs to
the remote location. A create table book1 statement
would create a table named book1 at the remote
location. A create
existing table statement for bookN would require
that pubs.dbo.bookN already exist at the remote
location, and information about table bookN would
be stored in the local table bookN:
sp_defaultloc pubs, SYBASE.pubs.dbo., table create table pubs.dbo.book1 (bridges char(15))
Removes the mapping of the database pubs to a remote location:
sp_defaultloc pubs, NULL
Identifies the remote storage location wallst.nasdaq.dbo where “wallst” is
the value provided for server_name, “nasdaq” is
provided for database, and “dbo” is
provided for owner. The RPC sybase must
already exist at the remote location. A create
existing table sybase statement would store information
about the result set from RPC sybase in local
table ticktape. The result set from RPC sybase is
regarded as a read-only table. Inserts, updates and deletes
are not supported for RPCs:
sp_defaultloc ticktape, wallst.nasdaq.dbo., rpc create existing table sybase (bestbuy integer)
sp_defaultloc defines a default storage location for tables in a local database. It maps table names in a database to a remote location. It permits the user to establish a default for an entire database, rather than issue an sp_addobjectdef command before every create table and create existing table command.
When defaulttype is table, view, or rpc, the defaultloc parameter takes the form:
server_name.dbname.owner.
Note that the defaultloc specification
ends in a period (.).
server_name represents a server already added to sysservers by sp_addserver. The server_name parameter is required.
dbname might not be required. Some server classes do not support it.
owner should always be provided to avoid ambiguity. If it is not provided, the remote object actually referenced could vary, depending on whether the external login corresponds to the remote object owner.
Issue sp_defaultloc before any create table or create existing table statement. When either statement is used, the server uses the sysattributes table to determine whether any table mapping has been specified for the object about to be created or defined. If the mapping has been specified, a create table statement directs the table to be created at the location specified by object_loc. A create existing table statement stores information about the existing remote object in the local table.
If you issue sp_defaultloc on defaulttype view and then issue create table, Component Integration Services creates a new table, not a view, on the remote server.
Changing the default location for a database does not affect tables that have previously been mapped to a different default location.
After tables in the database have been created, all future references to tables in dbname (by select, insert, delete and update) are mapped to the correct location.
Any user can execute sp_defaultloc.
Values in event and extrainfo columns from the sysaudits table are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
38 |
exec_procedure |
Execution of a procedure |
|
Commands create existing table, create table
System procedures sp_addobjectdef, sp_addserver, sp_helpserver
