sp_defaultloc

Description

Component Integration Services only Defines a default storage location for objects in a local database.

Syntax

sp_defaultloc dbname, {"defaultloc"| NULL} 
	[, "defaulttype"]

Parameters

dbname: 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.

defaultloc

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.

defaulttype

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.

Examples

Example 1

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))

Example 2

Removes the mapping of the database pubs to a remote location:

sp_defaultloc pubs, NULL

Example 3

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)

Usage

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.

Permissions

Any user can execute sp_defaultloc.