August 14, 2003 at 5:28 am
Hi
I am having problems retrieving the full column description using fn_listextendedproperties. Althought the destination column is varchar 200, only 30 characters max appear to be retrieved with my code (see below). Has anyone else had a similar problem?
Many Thanks
CREATE PROCEDURE [dbo].[CreateDataDict]
(
@DDTable char(11),
@TableName char(10),
@InsertError int OUTPUT
)
AS
EXEC('INSERT INTO [Lookups].[dbo].['+ @DDTable +']
SELECT CAST(o.[name] as char) ,
CAST(c.colorder as int),
CAST(c.[name] as char),
CAST(e.value as varchar) ,
CAST(t.[name] as varchar),
CAST(m.[text] as varchar),
CAST(c.isnullable as int)
FROM sysobjects o INNER JOIN syscolumns c ON o.[id] = c.[id] INNER JOIN systypes t ON c.[xtype] = t.[xtype] LEFT JOIN syscomments m ON m.[id] = c.[cdefault]
LEFT JOIN ::FN_LISTEXTENDEDPROPERTY(null, N''user'',
N''dbo'', N''table'','''+ @TableName+''', N''column'', null) e ON
c.[name] = e.objname WHERE o.[name] = '''+ @TableName+''' ORDER BY c.colorder')
SET @InsertError = @@error
GO
August 14, 2003 at 6:36 am
Lorna,
You need to supply a maximum VARCHAR length in your CAST (same with CONVERT) function, or the max will be set at 30.
Compare:
select cast(replicate('1234567890', 20) AS VARCHAR)
with
select cast(replicate('1234567890', 20) AS VARCHAR(255))
Cheers,
- Mark
Cheers,
- Mark
August 15, 2003 at 2:54 am
Oops! That's fixed it.
Many thanks for your help, Mark.
Lorna
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply