Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

2.2.5.5.5.1 Metadata

TVPTYPE        =   %xF3

 TVP_TYPE_INFO  =   TVPTYPE
                    TVP_TYPENAME
                    TVP_COLMETADATA
                    [TVP_ORDER_UNIQUE]
                    [TVP_COLUMN_ORDERING]
                    TVP_END_TOKEN
                    *TVP_ROW
                    TVP_END_TOKEN

Parameter

Description

TVPTYPE

%xF3

TVP_TYPENAME

Type name of the TVP

TVP_COLMETADATA

Column-specific metadata

[TVP_ORDER_UNIQUE]

Optional metadata token

[TVP_COLUMN_ORDERING]

Optional metadata token

TVP_END_TOKEN

End optional metadata

*TVP_ROW

0..N TVP_ROW tokens

TVP_END_TOKEN

End of rows

TVP_TYPENAME definition

 DBNAME        =   B_VARCHAR ; Database where TVP type resides
 OwningSchema  =   B_VARCHAR ; Schema where TVP type resides
 TypeName      =   B_VARCHAR ; TVP type name
 TVP_TYPENAME  =   DbName
                   OwningSchema
                   TypeName

TVP_COLMETADATA definition

 DbName               =   B_VARCHAR ; Database where TVP type resides
 fNullable            =   BIT       ; Column is nullable - %x01
 fCaseSen             =   BIT       ; Column is case-sensitive - %x02
 usUpdateable         =   2BIT      ; 2-bit value, one of:
                                    ; 0 = ReadOnly  - %x00
                                    ; 1 = ReadWrite - %x04
                                    ; 2 = Unknown   - %x08
 fIdentity            =   BIT       ; Column is identity column - %x10
 fComputed            =   BIT       ; Column is computed - %x20
 usReservedODBC       =   2BIT      ; Reserved bits for ODBC - %x40+80
 fFixedLenCLRType     =   BIT       ; Fixed length CLR type - %x100
 fDefault             =   BIT       ; Column is default value - %x200
 usReserved           =   6BIT      ; Six leftover reserved bits.
  
 Flags                =   fNullable
                          fCaseSen
                          usUpdateable
                          fIdentity
                          fComputed  
                          usReservedODBC
                          fFixedLenCLRType
                          fDefault
                          usReserved
 Count                =   USHORT    ; Column count up to 1024 max
 ColName              =   B_VARCHAR ; Name of column
 UserType             =   ULONG     ; UserType of column
  
 TypeColumnMetaData   =   UserType
                          Flags
                          TYPE_INFO
                          ColName   ; Column metadata instance
  
  
  
 TVP_NULL_TOKEN       =   %xFFFF
  
 TVP_COLMETADATA      =   TVP_NULL_TOKEN / (Count <Count>TvpColumnMetaData)

DbName, OwningSchema, and TypeName are limited to 128 Unicode characters max identifier length.

DbName is required to be zero-length, only OwningSchema and TypeName can be specified. DbName, OwningSchema, and TypeName are all optional fields and might ALL contain zero length strings. Client SHOULD follow these two rules:

  • If the TVP is a parameter to a stored procedure or function where parameter metadata is available on the server side, then the client can send all zero-length strings for TVP_TYPENAME.

  • If the TVP is a parameter to an ad-hoc SQL statement, parameter metadata information is not available on a stored procedure or function on the server. In this case the client is responsible to send sufficient type information with the TVP to allow the server to resolve the TVP type from sys.types. Failure to send needed type information in this case will result in complete failure of RPC call prior to execution.

Only one new flag, fDefault, is added here from existing COLMETADATA. ColName MUST be a zero-length string in the TVP.

Additional details about input TVPs and usage of flags

  • For an input TVP, if the fDefault flag is set on a column, then the client MUST NOT emit the corresponding TvpColumnData data for the associated column when sending each TVP_ROW.

  • For an input TVP, the fCaseSen, usUpdateable, and fFixedLenCLRType flags are ignored.

  • usUpdateable is ignored by server on input, it is "calculated" metadata.

  • The fFixedLenCLRType flag is not used by the server.

  • Output TVPs are not currently supported.

TVP Flags Usage Chart

Flag

Input behavior

fNullable

Allowed

fCaseSen

Ignored

usUpdateable

Ignored

fIdentity

Allowed

fComputed

Allowed

usReservedODBC

Ignored

fFixedLenCLRType

Ignored

fDefault

Allowed (if set, data not sent in TvpColumnData)

usReserved

Ignored

Show:
© 2015 Microsoft