如何通过mssql存储过程读取远程(网络)机器中xml文件的内容

2022-01-16 00:00:00 bulkinsert sql sql-server stored-procedures sql-server-2008-r2

我尝试使用批量插入读取本地机器中单个 xml 文件的内容.

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

它正在工作，但我正在尝试从远程机器读取.它给出以下错误

Msg 4861, Level 16, State 1, Line 1无法批量加载，因为无法打开文件Z:TechnicalLoss_EnergyAccounting_10.10.2012.12.19.PM.XML".操作系统错误代码 3(系统找不到指定的路径.).

我给出的文件路径为 

\172.16.11.52D:TechnicalLoss_EnergyAccounting_10.10.2012.12.19.PM.XML

然后它给出了同样的错误，我为那个远程机器创建了网络共享驱动器，它是 (z:) 给定的文件路径

Z:TechnicalLoss_EnergyAccounting_10.10.2012.12.19.PM.XML

同样的错误:-(帮我解决它?

解决方案

这里的问题似乎是关于文件共享，而不是关于 SQL Server.

首先，驱动器映射是针对每个用户的，因此在使用 SQL Server 时使用驱动器号会导致问题，因为 SQL Server 服务帐户没有映射任何驱动器号.出于这个原因，最好完全避免驱动器号，而只使用 UNC 路径.

接下来，共享的形式是\ServerNameShare.您尝试访问的文件似乎位于 D: 驱动器的根目录中，因此正确的路径是 \172.16.11.52D$TechnicalLoss_EnergyAccounting_10.10.2012.12.19.PM.XML.但是，请注意 D$ 是 默认管理员共享，并且只有管理员可以使用它.由于 SQL Server 服务帐户希望没有管理员权限，因此您无论如何都不应该使用它，并且将文件存储在任何驱动器的根目录中(尤其是 C:)通常是一种不好的做法.

最后，即使您有一个共享，您用来访问该共享的帐户也需要同时拥有该共享和文件系统的权限.这是使用域帐户运行 SQL Server 的一大好处..p>

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

  1. 如果还没有，请使用域帐户运行 SQL Server(请参阅上面的链接)
  2. 为您的文件创建一个文件夹，将其命名为 D:XMLFiles(或任何您喜欢的名称)
  3. 共享文件夹，所以现在您有一个名为 \172.16.11.52XMLFiles
    4. 的共享
  4. 确保 SQL Server 服务帐户具有从共享读取的权限
  5. 再次尝试您的代码:

SELECT * FROM OPENROWSET (BULK '\172.16.11.52XMLFilesTechnicalLoss_EnergyAccounting_10.10.2012.12.19.PM.XML', SINGLE_CLOB) AS xmlData

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.).

i given the filepath as 

\172.16.11.52D:TechnicalLoss_EnergyAccounting_10.10.2012.12.19.PM.XML

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?

解决方案

The issue here seems to be about file shares, not about 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.

Next, the form of a share is \ServerNameShare. 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.52D$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.

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. 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.52XMLFiles
  4. Ensure that the SQL Server service account has permissions to read from the share
  5. Try your code again:

SELECT * FROM OPENROWSET ( BULK '\172.16.11.52XMLFilesTechnicalLoss_EnergyAccounting_10.10.2012.12.19.PM.XML' , SINGLE_CLOB ) AS xmlData

相关文章