在 sql server 中检查文件是否存在?

2021-12-13 00:00:00 sql sql-server-2008 sql-server

解决方案:http://www.tech-recipes.com/rx/30527/sql-server-how-to-check-if-a-file-exists-in-a-directory/

使用 stackoverflow 问题就这个问题发表了一篇文章来帮助他人.

Made a post about this question using stackoverflow question to help others.

id  filepath

1   C:vishwanath21776656.docx
2   C:vishwanathvishs_srv_req_2009.txt
3   C:UsersdalviDWDW20SharedAmd64.exe
4   C:Usersdalvi1.txt

我在我的数据库服务器中创建了这样的表，我在文件路径列中存储了文件路径，现在我必须使用 sql 检查文件是否存在于我的机器中，如果存在我需要添加临时我的表中的列显示是，如果存在，则不存在.

I've table like this created in my db server, I've stored file paths in it filepath column, now I've to check using sql whether the file exists in my machine, if it exists I need to add temporary column in my table showing yes if exists and no it doesn't exists.

我写的这段代码适用于 1 个文件，但我不知道如何将它用于我的表格.

I wrote this code which works for 1 file But I don't know how to use it for my table.

DECLARE @isExists INT
exec master.dbo.xp_fileexist 'C:vishwanath21776656.docx', 
@isExists OUTPUT
SELECT case @isExists 
when 1 then 'Yes' 
else 'No' 
end as isExists

最终输出应该是这样的

id  filepath                                 Isexists

1   C:vishwanath21776656.docx               Yes
2   C:vishwanathvishs_srv_req_2009.txt     Yes
3   C:UsersdalviDWDW20SharedAmd64.exe     Yes
4   C:Usersdalvi1.txt                      No

推荐答案

像这样创建一个函数:

CREATE FUNCTION dbo.fn_FileExists(@path varchar(512))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

编辑您的表并添加一个计算列(IsExists BIT).将表达式设置为:

Edit your table and add a computed column (IsExists BIT). Set the expression to:

dbo.fn_FileExists(filepath)

然后只需选择:

SELECT * FROM dbo.MyTable where IsExists = 1

更新:

在计算列之外使用函数:

To use the function outside a computed column:

select id, filename, dbo.fn_FileExists(filename) as IsExists
from dbo.MyTable

更新:

如果函数为已知文件返回 0，则可能存在权限问题.确保 SQL Server 的帐户具有足够的权限来访问文件夹和文件.只读应该足够了.

If the function returns 0 for a known file, then there is likely a permissions issue. Make sure the SQL Server's account has sufficient permissions to access the folder and files. Read-only should be enough.

是的，默认情况下，网络服务"帐户没有足够的权限进入大多数文件夹.右键单击相关文件夹并选择属性"，然后单击安全"选项卡.单击编辑"并添加网络服务".点击应用"并重新测试.

And YES, by default, the 'NETWORK SERVICE' account will not have sufficient right into most folders. Right click on the folder in question and select 'Properties', then click on the 'Security' tab. Click 'Edit' and add 'Network Service'. Click 'Apply' and retest.

相关文章