openrowset读取Excel,解除字符串长度255之限制.

环境: Windows7, SQL Server 2008 R2, Microsoft Office 2010. 

问题:

select * from openrowset('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=[Excel文件]', 'select * from [sheet1$]')

读取excel结果,字符串被截断.仅保留前255个字符.

 

解决方法:

修改服务器注册表, 以下键值参数为0,重试正常.

excel.reg

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel]
"TypeGuessRows"=dword:00000000