(Component Integration Services only) Creates a proxy table, then retrieves and stores metadata from a remote table and places the data into the proxy table. Allows you to map the proxy table to a table, view, or procedure at a remote location.
The preferred method of creating proxy tables is the create proxy_table command, which eliminates the need to define the column definitions.
create existing table table_name (column_list)
[on segment_name]
[[external {table | procedure | file | connection_type}] at pathname
[column delimiter "string"]]
specifies the name of the table for which you want to create a proxy table.
specifies the name of the column list that stores information about the remote table.
specifies the segment that contains the remote table.
specifies that the object is a remote object.
specifies that the remote object is a table or a view. The default is external table.
specifies that the remote object is a stored procedure.
specifies that the remote object is a file.
determines whether a remote procedure call uses the current or a separate connection. The valid values are:
non_transactional – is a separate connection is used to execute the RPC.
transactional – is the existing connection is used to execute the RPC.
The default behavior is transactional.
specifies the location of the remote object. pathname takes the form: server_name.dbname.owner.object, where:
server_name (required) – is the name of the server that contains the remote object.
dbname (optional) – is the name of the database managed by the remote server that contains this object.
owner (optional) – is the name of the remote server user that owns the remote object.
object (required) – is the name of the remote table, view, or procedure.
used to separate fields within each record when accesssing flat files. The column delimiter can be up to 16 bytes long.
the column delimiter string can be any character sequencer, but if the string is longer than 16 bytes, only the first 16 bytes are used. The use of column delimiter for proxy tables mapped to anything but files results in a syntax error.
Creates the proxy table authors:
create existing table authors ( au_id id, au_lname varchar (40) NOT NULL, au_fname varchar (20) NOT NULL, phone char (12), address varchar (40) NULL, city varchar (20) NULL, state char (2) NULL, zip char (5) NULL, contract bit ) at "nhserver.pubs2.dbo.authors"
Creates the proxy table syb_columns:
create existing table syb_columns ( id int, number smallint, colid tinyint, status tinyint, type tinyint, length tinyint, offset smallint, usertype smallint, cdefault int, domain int, name varchar (30), printfmt varchar (255) NULL, prec tinyint NULL, scale tinyint NULL ) at "remote1.master.dbo.columns"
Creates a proxy table named blurbs for the blurbs table at the remote server SERVER_A:
create existing table blurbs ( author_id id not null, copy text not null ) at "SERVER_A.db1.joe.blurbs"
Creates a proxy table named rpc1 for the remote procedure named p1:
create existing table rpc1 ( column_1 int, column_2 int ) external procedure at "SERVER_A.db1.joe.p1"
create existing table does not create a new table unless the remote object is a file. Instead, CIS checks the table mapping to confirm that the information in column_list matches the remote table, verifies the existence of the underlying object, and retrieves and stores metadata about the remote table.
If the host data file or remote server object does not exist, the command is rejected with an error message.
If the object exists, the system tables sysobjects, syscolumns, and sysindexes are updated. The verification operation requires these steps:
The nature of the existing object is determined. For host data files, this requires determining file organization and record format. For remote server objects, this requires determining whether the object is a table, a view, or an RPC.
For remote server objects (other than RPCs), column attributes obtained for the table or view are compared with those defined in the column_list.
Index information from the host data file or remote server table is extracted and used to create rows for the sysindexes system table. This defines indexes and keys in Adaptive Server terms and enables the query optimizer to consider any indexes that might exist on this table.
The on segment_name clause is processed locally and is not passed to a remote server.
After successfully defining an existing table, issue update statistics for the table. This allows the query optimizer to make intelligent choices regarding index selection and join order.
CIS allows you to create a proxy table with a column defined as NOT NULL even though the remote column is defined as NULL. It displays a warning to notify you of the mismatch.
The location information provided by the at keyword is the same information that is provided by sp_addobjectdef. The information is stored in the sysattributes table.
CIS inserts or updates a record in the systabstats catalog for each index of the remote table. Since detailed structural statistics are irrelevant for remote indexes, only a minimum number of columns are set in the systabstats record—id, indid, and rowcnt.
External files cannot be of datatypes text, image, or Java ADTs.
When using create existing table, you must specify all datatypes with recognized Adaptive Server datatypes. If the remote server tables reside on a class of server that is heterogeneous, the datatypes of the remote table are automatically converted into the specified Adaptive Server types when the data is retrieved. If the conversion cannot be made, CIS does not allow the table to be defined.
The Component Integration Services Users Guide contains a section for each supported server class and identifies all possible datatype conversions that are implicitly performed by CIS.
All server classes allow you to specify fewer columns than there are in the table on the remote server.
All server classes match the columns by name.
All server classes allow the column type to be any datatype that can be converted to and from the datatype of the column in the remote table.
When the proxy table is a procedure-type table, you must provide a column list that matches the description of the remote procedure’s result set. create existing table does not verify the accuracy of this column list.
No indexes are created for procedures.
CIS treats the result set of a remote procedure as a virtual table that can be sorted, joined with other tables, or inserted into another table using insert or select. However, a procedure type table is considered read-only, which means you cannot issue the following commands against the table:
Begin the column name with an underscore (_) to specify that the column is not part of the remote procedure’s result set. These columns are referred to as parameter columns. For example:
create existing table rpc1 ( a int, b int, c int, _p1 int null, _p2 int null ) external procedure at "SYBASE.sybsystemprocs.dbo.myproc"
In this example, the parameter columns _p1 and _p2 are input parameters. They are not expected in the result set, but can be referenced in the query:
select a, b, c from t1 where _p1 = 10 and _p2 = 20
CIS passes the search arguments to the remote procedure as parameters, using the names @p1 and @p2.
Parameter-column definitions in a create existing table statement:
Must allow a null value.
Cannot precede regular result columns—they must appear at the end of the column list.
If a parameter column is included in a select list and is passed to the remote procedure as a parameter, the return value is assigned by the where clause.
If a parameter column is included in a select list, but does not appear in the where clause or cannot be passed to the remote procedure as a parameter, its value is NULL.
A parameter column can be passed to a remote procedure as a parameter if the Adaptive Server query processor considers it a searchable argument. A parameter column is considered a searchable argument if it is not included in any or predicates. For example, the or predicate in the second line of the following query prevents the parameter columns from being used as parameters:
select a, b, c from t1 where _p1 = 10 or _p2 = 20
create existing table automatically updates syscolumns with any encrypted column metadata from the remote table. You cannot include the encrypt keyword in the column list for a create existing table command.
ANSI SQL – Compliance level: Transact-SQL extension.
create existing table permission defaults to the table owner and is not transferable.
Commands alter table, create table, create proxy_table, drop index, insert, order by clause, set, update
