Additional Table-Valued Parameter Metadata

Additional Table-Valued Parameter Metadata

 

System_CAPS_ICON_warning.jpg Warning

SQL Server Native Client (SNAC) is not supported beyond SQL Server 2012. Avoid using SNAC in new development work, and plan to modify applications that currently use it. The Microsoft ODBC Driver for SQL Server provides native connectivity from Windows to Microsoft SQL Server and Microsoft Azure SQL Database.

To retrieve metadata for a table-valued parameter, an application calls SQLProcedureColumns. For a table-valued parameter, SQLProcedureColumns returns a single row. Two additional SQL Server-specific columns, SS_TYPE_CATALOG_NAME and SS_TYPE_SCHEMA_NAME, have been added to provide schema and catalog information for table types associated with table-valued parameters. In conformance with the ODBC specification, SS_TYPE_CATALOG_NAME and SS_TYPE_SCHEMA_NAME appear before all driver-specific columns added in earlier versions of SQL Server, and after all columns mandated by ODBC itself.

The following table lists columns that are significant for table-valued parameters.

Column nameData typeValue/comments
DATA_TYPESmallint not NULLSQL_SS_TABLE
TYPE_NAMEWVarchar(128) not NULLThe type name of the table-valued parameter.
COLUMN_SIZEIntegerNULL
BUFFER_LENGTHInteger0
DECIMAL_DIGITSSmallintNULL
NUM_PREC_RADIXSmallintNULL
NULLABLESmallint not NULLSQL_NULLABLE
REMARKSVarcharNULL
COLUMN_DEFWVarchar(4000)NULL
SQL_DATA_TYPESmallint not NULLSQL_SS_TABLE
SQL_DATETIME_SUBSmallintNULL
CHAR_OCTET_LENGTHIntegerNULL
ORDINAL_POSITIONInteger not NULLThe ordinal position of the parameter.
IS_NULLABLEVarchar"YES"
SS_TYPE_CATALOG_NAMEWVarchar(128) not NULLThe catalog that contains the type definition for the table type of the table-valued parameter.
SS_TYPE_SCHEMA_NAMEWVarchar(128) not NULLThe schema that contains the type definition for the table type of the table-valued parameter.

The WVarchar columns are defined as Varchar in the ODBC specification, but are actually returned as WVarchar in all recent SQL Server ODBC drivers. This change was made when Unicide support was added to the ODBC 3.5 specification, but not called out explicitly.

To obtain additional metadata for table-valued parameters, an application uses the catalog functions SQLColumns and SQLPrimaryKeys. Before these functions are called for table-valued parameters, the application must set the statement attribute SQL_SOPT_SS_NAME_SCOPE to SQL_SS_NAME_SCOPE_TABLE_TYPE. This value indicates that the application requires metadata for a table type rather than an actual table. The application then passes the TYPE_NAME of the table-valued parameter as the TableName parameter. SS_TYPE_CATALOG_NAME and SS_TYPE_SCHEMA_NAME are used with the CatalogName and SchemaName parameters, respectively, to identify the catalog and schema for the table-valued parameter. When an application has finished retrieving metadata for table-valued parameters, it must set SQL_SOPT_SS_NAME_SCOPE back to its default value of SQL_SS_NAME_SCOPE_TABLE.

When SQL_SOPT_SS_NAME_SCOPE is set to SQL_SS_NAME_SCOPE_TABLE, queries to linked servers fail. Calls to SQLColumns or SQLPrimaryKeys with a catalog that contains a server component will fail.

Table-Valued Parameters (ODBC)

Community Additions

ADD
Show:
© 2016 Microsoft