The DTS MS SQL Connector supports remote calls for any MS SQL Server stored procedures and functions, scalar-valued as well as table-valued.
DTS Naming
DTS Name |
<proc_or_func_name> |
DTS Key |
<schema_name>.<proc_or_func_name> |
Arguments and Results
▪Table type arguments to functions and procedures, as well as results of table-valued functions are fully supported for remote calling
oThe table arguments are always passed through DTS as arrays of custom objects, each object representing a row in the input table
oThe result of a table-valued function is always a DTS stream
See Streams
▪MS SQL does not have a way of representing a single custom object, other than a table with a single row, so that extends to DTS, where they will become arrays with a single element.
▪In MS SQL Server, procedure output arguments always serve as inputs as well. As such, they will appear both in the list of arguments and the list of results for a given remote call and be treated as two distinct items, one going in, one coming out.
For argument and result native and user type mappings, please see Types.
Metadata
The connector is also capable of extracting metadata regarding the arguments and results of a procedure or function:
▪DATA_TYPE
▪CHARACTER_OCTET_LENGTH
▪CHARACTER_MAXIMUM_LENGTH
▪NUMERIC_PRECISION
▪DATETIME_PRECISION
▪NUMERIC_SCALE
▪ORDINAL_POSITION
▪PARAMETER_MODE
▪USER_DEFINED_TYPE
Standard functionality only makes use of DATA_TYPE, ORDINAL_POSITION, PARAMETER_MODE and USER_DEFINED_TYPE, but the other items are available for extension in DTS descriptor objects.