如何通过mssql存储过程读取远程（网络）计算机中xml文件的内容 [英] how to read the content of xml file in the remote(network) machine through mssql stored procedures

问题描述

我尝试使用批量插入在本地计算机中读取单个xml文件的内容。

I tried to read the contents of single xml file in the local machine using bulk insert.

SELECT * FROM OPENROWSET ( BULK '''+ @FILENAME+''' , SINGLE_CLOB ) AS xmlData

它正在工作，但是同样，我正尝试从远程计算机读取。其给出以下错误

It is working, but the same i am trying to read from the remote machine. its giving the following error

Msg 4861, Level 16, State 1, Line 1
Cannot bulk load because the file "Z:\TechnicalLoss_EnergyAccounting_10.10.2012.12.19.PM.XML" could not be opened. Operating system error code 3(The system cannot find the path specified.).

我给定的文件路径为

\\172.16.11.52\D:\TechnicalLoss_EnergyAccounting_10.10.2012.12.19.PM.XML

然后给出相同的错误，我为该远程计算机创建了网络共享驱动器，给定文件路径为（z：）

It gives the same error then, I created the network share drive for that remote machine that is (z:) given the filepath as

Z:\TechnicalLoss_EnergyAccounting_10.10.2012.12.19.PM.XML

相同的错误:-(帮助我解决吗？

same error :-( Help me to resolve it?

推荐答案

这里的问题似乎与文件共享有关，而不是SQL Server。

The issue here seems to be about file shares, not about SQL Server.

首先，驱动器映射是针对每个用户的，因此在使用SQL Server时使用驱动器号会引起问题，因为SQL Server服务帐户没有任何驱动器映射

First, a drive mapping is per-user, so using drive letters causes problems when working with SQL Server because the SQL Server service account doesn't have any drive letters mapped. For that reason it's best to completely avoid drive letters and simply use UNC paths.

接下来，共享的形式为 ＼ \＼ServerName\Share ，看起来像您要尝试的文件访问位于D：驱动器的根目录中，因此正确的路径应为 \\172.16.11.52\D $ \TechnicalLoss_EnergyAccounting_10.10.2012.12.19.PM.XML 。但是请注意， D $ 是默认管理共享，只有管理员可以使用它。由于SQL Server服务帐户希望没有管理员权限，因此无论如何都不要使用它，并且将文件存储在任何驱动器的根目录中（尤其是 C：）是

Next, the form of a share is \\ServerName\Share. It looks like the file you're trying to access is in the root of the D: drive, so the correct path would be \\172.16.11.52\D$\TechnicalLoss_EnergyAccounting_10.10.2012.12.19.PM.XML. But, note that D$ is a default admin share and only administrators can use it. Since the SQL Server service account hopefully does not have admin rights, you shouldn't be using it anyway, and storing files in the root of any drive (especially C:) is usually a bad practice.

最后，即使您有共享，用于访问共享的帐户也必须同时具有共享权限和文件系统权限。这是使用域帐户运行SQL Server的好处。

Finally, even if you have a share, the account you use to access the share needs to have permissions both on the share and on the filesystem. This is one benefit to running SQL Server using a domain account.

总而言之，您可能需要做的是：

In summary, what you probably need to do is this:

1. 如果不是还没有，使用域帐户运行SQL Server（请参见上面的链接）


2. 为文件创建一个文件夹，将其命名为 D：\XMLFiles （或您喜欢的任何内容）


3. 共享文件夹，所以现在您有了一个名为 \\172.16.11.52\XMLFiles


4. 确保SQL Server服务帐户具有读取共享的权限


5. 重试您的代码：

1. If it isn't already, run SQL Server using a domain account (see link above)
2. Create a folder for your files, call it D:\XMLFiles (or whatever you like)
3. Share the folder, so now you have a share called \\172.16.11.52\XMLFiles
4. Ensure that the SQL Server service account has permissions to read from the share
5. Try your code again:

选择*来自OPENROWSET（BULK'\\172.16.11.52\XMLFiles\TechnicalLoss_EnergyAccounting_10.10.2012.12.19。 PM.XML'，SINGLE_CLOB）AS xmlData

这篇关于如何通过mssql存储过程读取远程（网络）计算机中xml文件的内容的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！