sp_addobjectdef

(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.
objecttype – is one of the values that specify the format of the object named by objectdef.. Valid values are:
- 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. The SAP ASE server processes the result set from the RPC as a read-only table.
Enclose the objecttype value in quotes.

This table summarizes how each objecttype is used:

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

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 MYSERVER. 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", "MYSERVER.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

There are additional considerations when using sp_addobjectdef:

sp_addobjectdef specifies the mapping between a local table and an external storage location. It identifies the format of the object at that location.You can use sp_addobjectdef only when Component Integration Services is installed and configured.
sp_addobjectdef replaces the sp_addtabledef command. sp_addobjectdef allows existing scripts to run without modification. Internally, sp_addtabledef invokes sp_addobjectdef.
Only the system administrator can provide the name of another user as a table owner.
When objecttype is table, view, or rpc, the objectdef parameter takes the following form:
```
"server_name.database.owner.tablename"
```
- server_name – represents a server that has already been added to sysservers by sp_addserver.
- database – may not be required. Some server classes do not support it.
- owner – should always be provided, to avoid ambiguity. If you do not specify owner, the remote object referenced may vary, depending on whether or not the external login corresponds to the remote object owner.
- tablename – is the name of a remote server table.
Use sp_addobjectdef before issuing any create table or create existing table commands. However, if a remote table exists, you need not use sp_addobjectdef before executing create proxy_table.

create table is valid only for the objecttype values table and file. When either create table or create existing table is used, the SAP ASE server checks sysattributes to determine whether any table mapping has been specified for the object. Follow the objecttype values view and rpc with create existing table statements.
After the table has been created, all future references to the local table name (by select, insert, delete, and update) are mapped to the correct location.

Permissions

The permission checks for sp_addobjectdef differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be the table owner or a user with `manage database` privilege.
Disabled	With granular permissions disabled, you must be the table owner, the database owner, or a user with sa_role.

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