环境: 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