Z
Zardoz
I have an Access 2003 front-end (mdb/mde) connected to a SQL Server 2000
back-end. SQL Server 2000 offers the ability to add extended properties
(such as a caption, for example) to objects (tables, columns, etc.) using a
stored procedure called sp_AddExtendedProperty, along with the ability to
retrieve the values of these extended properties via a function named
fn_ListExtendedProperty.
With fn_ListExtendedProperty, four columns can be returned/selected using a
Select statement: objtype, objname, name and value. The first three columns
returned are of datatype sysname, while value is of datatype sql_variant.
Using a "Select * from ::fn_ListExtendedProperty..." statement in a
pass-through query in my front-end mdb generates the following Microsoft
Access error message: "The size of a field is too long." Clicking help for
this error message reveals that it is error number 3017. Replacing the * in
the Select Statement with any or all of the three sysname datatype columns
(i.e., "Select objtype, objname, name from ::fn_ListExtendedProperty...")
works without error; however, as soon as the fourth column (value) is
specified, the error occurs.
I believe the error must have something to do with the fact that value is of
datatype sql_variant. The values that I have assigned to extended properties
in the testing that I am doing have been no longer than 10 characters.
The extended properties are of no use to me if I cannot retrieve the values
assigned to these properties. I would appreciate any advice on how to get
this working.
back-end. SQL Server 2000 offers the ability to add extended properties
(such as a caption, for example) to objects (tables, columns, etc.) using a
stored procedure called sp_AddExtendedProperty, along with the ability to
retrieve the values of these extended properties via a function named
fn_ListExtendedProperty.
With fn_ListExtendedProperty, four columns can be returned/selected using a
Select statement: objtype, objname, name and value. The first three columns
returned are of datatype sysname, while value is of datatype sql_variant.
Using a "Select * from ::fn_ListExtendedProperty..." statement in a
pass-through query in my front-end mdb generates the following Microsoft
Access error message: "The size of a field is too long." Clicking help for
this error message reveals that it is error number 3017. Replacing the * in
the Select Statement with any or all of the three sysname datatype columns
(i.e., "Select objtype, objname, name from ::fn_ListExtendedProperty...")
works without error; however, as soon as the fourth column (value) is
specified, the error occurs.
I believe the error must have something to do with the fact that value is of
datatype sql_variant. The values that I have assigned to extended properties
in the testing that I am doing have been no longer than 10 characters.
The extended properties are of no use to me if I cannot retrieve the values
assigned to these properties. I would appreciate any advice on how to get
this working.