sp_defaultloc

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

Syntax

sp_defaultloc dbname, defaultloc, 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 MYSERVER. 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, MYSERVER.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

There are additional considerations when using sp_defaultloc:

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.

See also create existing table, create table in Reference Manual: Commands.

Permissions

Any user can execute sp_defaultloc. Permission checks do not differ based on the granular permissions settings.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect