sp_defaultloc

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

Syntax

sp_defaultloc dbname, defaultloc, defaulttype

Parameters

Examples

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:

InformationValues
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

Related reference
sp_addobjectdef
sp_addserver
sp_helpserver