sp_addobjectdef

Description

(Component Integration Services only) Specifies the mapping between a local table and an external storage location.

Syntax

sp_addobjectdef tablename, objectdef [, "objecttype"]

Parameters

tablename

is the name of the object as it is defined in a local table. The tablename can be in any of the following forms:

  • dbname.owner.object

  • dbname..object

  • owner.object

  • object

dbname and owner are optional. object is required. If you do not specify an owner, the default (current user name) is used. If you specify a dbname, it must be the current database name, and you must specify owner or mark the owner with a placeholder in the format dbname..object. Enclose any multipart tablename values in quotes.

objectdef

is a string naming the external storage location of the object. The objecttype at objectdef can be a table, view, or read-only remote procedure call (RPC) result set accessible to a remote server. A table, view, or RPC uses the following format for objectdef:

server_name.dbname.owner.object

server_name and object are required. dbname and owner are optional, but if they are not supplied, a placeholder in the format dbname..object, is required.

See “Server Classes” in the Component Integration Services User’s Guide for more information.

objecttype

is one of the values that specify the format of the object named by objectdef.. Table 1-1 describes the valid values. Enclose the objecttype value in quotes.

Table 1-1: Allowable values for objecttype

Value

Description

table

Indicates that the object named by objectdef is a table accessible to a remote server. This value is the default for objecttype.

view

Indicates that the object named by objectdef is a view managed by a remote server and processed as a table.

rpc

Indicates that the object named by objectdef is an RPC managed by a remote server. Adaptive Server processes the result set from the RPC as a read-only table.

Table 1-2 summarizes how each objecttype is used.

Table 1-2: Summary of objecttype uses

objecttype

create table

create existing table

Write to table

Read from table

table

Yes

Yes

Yes

Yes

view

No

Yes

Yes

Yes

rpc

No

Yes

No

Yes

Examples

Example 1

Maps the local table accounts in the database finance to the remote object pubs.dbo.accounts in the remote server named SYBASE. The current database must be finance. A subsequent create table creates a table in the pubs database. If pubs.dbo.accounts is an existing table, a create existing table statement populates the table finance.dbo.accounts with information about the remote table:

sp_addobjectdef "finance.dbo.accounts", "SYBASE.pubs.dbo.accounts", "table"

Example 2

Maps the local table stockcheck to an RPC named stockcheck on remote server NEWYORK in the database wallstreet with owner “kelly”. The result set from RPC stockcheck is seen as a read-only table. Typically, the next operation would be a create existing table statement for the object stockcheck:

sp_addobjectdef stockcheck, "NEWYORK.wallstreet.kelly.stockcheck", "rpc"

Usage

Permissions

Any user can execute sp_addobjectdef.

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, drop table

System procedures sp_addlogin, sp_addserver, sp_defaultloc, sp_dropobjectdef, sp_helpserver