sp_defaultloc

Description

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

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:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • 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

See also

Commands create existing table, create table

System procedures sp_addobjectdef, sp_addserver, sp_helpserver