sp_MSset_oledb_prop N'.12.0', N'AllowInProcess', 1ĮXEC master. –> I searched on net and I got following solution in MSDN forums to register the ACE OLEDB 12.0 provider:ĮXEC master. Neither of the above options runs and ends up in following error:Ĭannot create an instance of OLE DB provider “.12.0” for linked server “XLSX_2010”. Using the Linked Server name within the SELECT query: SELECT * FROM OPENQUERY (XLSX_2010, 'Select * from ') Indirectly, by Creating Linked Server & using OPENQUERY:ĮXEC = N'E:\SQL Server - = N'Excel 12.0 HDR=Yes' 'Excel 12.0 Database=E:\SQL Server - Blogs\LinkedServer2010.xlsx HDR=YES IMEX=1', by using the Linked Server name within the SELECT query fetching records from Excel by using OPENQUERY() function orĢ.b. Indirectly by creating a Linked Server first of all, then:Ģ.a.
Directly fetch records from Excel by using the OPENROWSET() function by providing the providers and other optionsĢ. –> Now, to access the Excel file there are 2 ways:ġ.
You can also enable this setting graphically by going to “Surface Area Configuration” and enable it. Sp_configure 'Ad Hoc Distributed Queries', 1 For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. This is disabled by default, if not enabled you will see the following error message: Please Note: To use a Linked server for fetching records from a particular source first you have to enable the “Ad Hoc Distributed Queries” configuration setting. We don’t need to do any change in the scripts for this version, but we will require some additional steps to do, that we’ll see later on. Will use the same scripts from my previous post to create Linked Server to point to the Excel source. Here we will see how can we query Excel 2010 files. In my previous post I discussed about querying Excel files with both 2003 & 2007 versions from SQL Server.
0 kommentar(er)
