SQL Server中的分布式查询，来自XLS的数据 [英] Distributed Queries in SQL Server, data from XLS

问题描述

我想在SQL Server 2008中创建一个视图，显示Excel文件中包含的数据。

I would like to create a view in SQL Server 2008 that displays the data contained in an Excel file.

我不想使用导入数据数据更新。

I do not want to use the import data as these data are updated.

我发现这一点： http://support.microsoft.com/kb/321686/en

我做了这些命令来启用一些选项：

I made these commands to enable some options :

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

但是当我尝试使用此查询读取Excel文件时：

But when I try to read the Excel file with this query:

SELECT * FROM INTO XLImport3 OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\Documents and Settings\jconnor\Desktop\Test.xlsx') ... [Sheet1$]

我回来（这是法语的翻译）：

I back (it's a translation from french) :

OLE DB提供erMicrosoft.Jet.OLEDB.4.0用于链接服务器（null）返回>消息未指定错误。
消息7303，级别16，状态1，行1
无法初始化对象数据源OLE DB提供程序Microsoft.Jet.OLEDB.4.0for> linked server（null）。 p>

The OLE DB provider " Microsoft.Jet.OLEDB.4.0 " for linked server " (null) " returned >message " Unspecified error " . Msg 7303 , Level 16 , State 1, Line 1 Unable to initialize the object data source OLE DB provider " Microsoft.Jet.OLEDB.4.0 " for >linked server " (null) " .

有人有任何线索吗？

提前谢谢

推荐答案

Kal，

Kal,

所以你的主要错误可能是这样的;

So your main error is likely this;

OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)".

我会检查一些权限。

检查Temp文件夹的权限

Check the permissions on the Temp folder

这是必需的，因为提供者在检索数据时使用临时文件夹。根据您是否使用本地系统帐户或网络域帐户，该文件夹可以是以下之一。

This is needed because the provider uses the temp folder while retrieving the data. The folder can be one of the below based on whether you use a local system account or network domain account.

对于网络帐户，文件夹为

For network accounts, folder is

\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp

本地系统帐户其\Windows\ServiceProfiles\LocalService\AppData\Local\Temp

and for local system account its \Windows\ServiceProfiles\LocalService\AppData\Local\Temp

右键单击此文件夹，并给它读取写访问权限帐号（或组）执行代码。这解决了我的错误。

Right click on this folder and give it read write access to the account (or group) executing the code. That solved the error for me.

另外

http://blogs.msdn.com/b/spike/archive/2008/07/23/ole-db-provider-microsoft-jet-oledb-4-0-for-linked-server-null -returned-message-unspecified-error.aspx

这是因为SQL Server服务正在尝试将temp DSN写入用于登录的临时文件夹这开始了服务，在这种情况下是管理员/管理员登录。
临时文件夹类似于： C：\Documents and Settings\Admin\Local Settings\Temp
.15如上所述，OleDbProvider将始终执行初始化它的用户的上下文，在这种情况下是用户/用户。
.16用户/用户对此文件夹没有权限（ C：\Documents and Settings\Admin\Local Settings\Temp ）。
如果在执行SQL时运行FileMon，我们可以看到以下内容：
（实际上，尝试使用 Process Monitor - http://technet.microsoft.com/en-us/sysinternals/bb896645 ）

This is because the SQL Server Service is trying to write the temp DSN to the temp folder for the login that started the service, in this case the Admin/Admin login. The temp folder is something like: C:\Documents and Settings\Admin\Local Settings\Temp .15 As mentioned, the OleDbProvider will always execute in the context of the user who initialized it, in this case User/User. .16 User/User has no rights on this folder (C:\Documents and Settings\Admin\Local Settings\Temp). If running FileMon when the SQL is executed, we can see the following: (Actually, try using Process Monitor - http://technet.microsoft.com/en-us/sysinternals/bb896645)

sqlservr.exe：000查询信息C：\DOCUME〜1\Admini〜1\LOCALS〜1\Temp ACCESS DENIED属性：错误

sqlservr.exe:000 QUERY INFORMATION C:\DOCUME~1\Admini~1\LOCALS~1\Temp ACCESS DENIED Attributes: Error

所以总结到目前为止：
SQL Server服务以Admin / Admin启动，当进行选择时，OleDb提供程序由User / User调用。
现在OleDb提供程序尝试在临时目录中创建临时DSN。这将是SQL Server服务（Admin / Admin）
的临时目录，但用户（在这种情况下为User / User）对此文件夹没有写入权限。发生错误。

So to summarize so far: The SQL Server service is started as Admin/Admin, when the select is made, the OleDb provider is invoked by User/User. Now the OleDb provider attempts to create a temporary DSN in the temp directory. This will be the temp directory for the SQL Server service (Admin/Admin) but the user (in this case User/User) does not have write permissions on this folder. And the error occurs.

有两种方法可以解决这个问题。

There are two ways to resolve this.

选项1 ：
注销计算机并以启动SQL Server服务（在本例中为Admin / Admin）的帐户登录，然后启动命令提示符
并键入set t（无引号），这将显示如下：

Option 1: Log out of the machine and log in as the account that starts the SQL Server Service (in this case Admin/Admin) then start a command prompt and type "set t" (no quotes), this will show something like:

TEMP = C：\DOCUME〜1\Admin\LOCALS〜1\Temp
TMP = C ：\DOCUME〜1\Admin\LOCALS〜1\Temp

TEMP=C:\DOCUME~1\Admin\LOCALS~1\Temp TMP=C:\DOCUME~1\Admin\LOCALS~1\Temp

这些是为％TEMP％和％TMP％设置的环境变量，因此请转到该文件夹并右键单击并选择属性 - >安全性，
然后添加用户，在这种情况下用户/用户注意，用户的默认是读取&执行/列表文件夹内容/读取，这还不够，您必须选择写入。

these are the environment variables set for %TEMP% and %TMP%, so go to that folder and right click and select Properties -> Security, then add the user, in this case User/User, note that the default for the user is Read&Execute/List Folder Content/Read, this not enough, you have to select Write as well.

注销并以用户/用户身份再次登录，并从SSMS重新运行命令。

Log out, and log in again as User/User and rerun the command from SSMS. This time it should work.

选项2 ：
以管理员身份登录，并将TEMP和TMP变量更改为，例如，C：\Temp，基本上这将Temp目录移出Documents and Settings文件夹。
但是，您必须重新启动SQL服务器才能生效。

Option 2: Log on as Admin and change the TEMP and TMP variable to, for example, C:\Temp, basically this moves the Temp directory out of the Documents and Settings folder. However, you must restart the SQL server for this to take effect.

所以基本上发生的是，当SQL Server启动时，它使用Temp文件夹的启动帐户（管理员/管理员），但MICROSOFT.JET.OLEDB.4.0将始终执行
作为调用SQL命令（用户/用户）的用户，这将失败，除非该用户没有写访问权限到该临时文件夹。

So basically, what happens is that when SQL Server starts, it uses the Temp folder of the startup account (Admin/Admin) but the MICROSOFT.JET.OLEDB.4.0 will always execute as the user who calls the SQL command (User/User) and this will fail unless this user does not have Write access to that temp folder.

不知道所有设置，也许选项2是首选解决方案，因为选项1，您将不得不添加所有将调用的用户提供者可能不实用。
此外，更改SQL Server服务的启动帐户时，将使用该帐户的TEMP目录，您将再次看到错误，直到您再次为此TEMP上的所有用户授予写入权限文件夹...或用户组（首选）。

Without knowing all setups out there, perhaps option 2 is the preferred solution since with option 1, you will have to add ALL the users that will invoke the provider which may not be practical. Also, when changing the startup account for the SQL Server service, then the TEMP directory for that account will be used, and you will see the error again until you, again, give write permissions for all the users on this TEMP folder...or a user group (preferred).

这篇关于SQL Server中的分布式查询，来自XLS的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！