#openrowset

[info-comp]

В материале будет рассмотрена возможность использования распределенных запросов в Microsoft SQL Server для загрузки данных из Excel на языке T-SQL

[dataprose]

Transfer Excel To SQL

My grandparents had a small farm in Iowa . We only lived a few miles away and my brothers and I spent a great deal of time at the farm with our grandparents when we weren’t in school. I traded the rolling corn fields of Iowa for the rusty-reddish brown soil of Arizona many years ago, but I frequently think about my grandparents and working around the farm with the crops, the garden, the animals…

View On WordPress

[prashantjayaram]

T-SQL - Read CSV files using OpenRowSet

T-SQL – Read CSV files using OpenRowSet

We can directly access a CSV file using T-SQL.

Input file

Configure server to run Ad Hoc Distributed Queries

sp_configure 'Ad Hoc Distributed Queries',1 GO reconfigure GO

Check for Microsoft Access Driver on your system

SQL

select [hour], UserID, [ReportLaunch] from openrowset('MSDASQL' ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}' ,'select * from C:\blog\input.CSV') OR select…

View On WordPress

[weeker]

因为 OLE DB 访问接口 'Microsoft.Jet.OLEDB.4.0' 配置为在单线程单元模式下运行，所以该访问接口无法用于分布式查询。

经过google发现是AccessDatabaseEngine的驱动问题，需要从官方网站下载 Microsoft Access Database Engine 2010 Redistributable，并把连接字符串Microsoft.jet.Oledb.4.0 更改为 Microsoft.ACE.OLEDB.12.0

运行环境:Windwos 2008 R2 x64+ SQL Server 2005 x64