访问链接数据库字段长度超出

通过链接服务器访问某表报错，提示字段长度超出，错误如下：

Msg 7347, Level 16, State 1, Line 1

OLE DB provider 'SQLNCLI10' for linked server 'LINKSERVERK2BP' returned data that does not match expected data length for column '[LINKSERVERK2BP].[Swire_Workflow].[dbo].[UTC_User].username'. The (maximum) expected data length is 100, while the returned data length is 102.

 

 

查询某网站有解决方法:

> I setup a trace in profiler and ran the SP against the linked server.  I
> have been going through the commands that show up in the trace and then
[quoted text clipped - 8 lines]
>
> TMS     dbo     OBJECTDESCRIPTION     ObjectNumber     NULL     NULL     4    

0     NULL     4     0     129     NULL     24     24     NULL     NULL    
NULL     master     dbo     iso_1     master     dbo    
SQL_Latin1_General_CP1_CI_AI     NULL     NULL     NULL     NULL     1033    
196611     54     0x0904F00036     0

> (That sure does look ugly).  According to the output of the command, the
> CHARACTER_MAXIMUM_LENGTH is 24.  When I look at the table definition
[quoted text clipped - 4 lines]
> supported by a third party.  At this point I think that I either need to
> adjust the view, or create another view with a properly sized field.

So ObjectDescription is a view? Maybe the length of the underlying column
has been changed without the view being refreshed?

Run this:

BEGIN TRANSACTION

select name, length from syscolumns where id =
object_id('ObjectDescription')

EXEC sp_refreshview ObjectDescription

select name, length from syscolumns where id =
object_id('ObjectDescription')

ROLLBACK TRANSACTION

If you see the length change from 24 to 42 this is the answer. (I suggest
a rollback in case you don't want perform updates in the third-party
database. You can of course change that if you like.

 

 

---------------------------------

结果：通过sp_refreshview 解决，但syscolumns表中没找到对应视图。