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


sp_defaultloc dbname, {"defaultloc"| NULL} 
	[, "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:



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:


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)



Any user can execute sp_defaultloc.

See also

Commands create existing table, create table

System procedures sp_addobjectdef, sp_addserver, sp_helpserver