Set the path when checking if a file exists in a folder via SQL Server 2016

Question

I am using the following code 1 to check if a file exists in a folder and it works. Then when I move the file to another folder, saying adding multiple New folder, it does not work, only 0 is returned, not 1.(I am sure the path is correct and the file does exist)

I cannot figure out what is the problem.

Code 1:

 DECLARE @result INT
 EXEC master.dbo.xp_fileexist 'C:\Users\$11F7BC3A.jpg', @result OUTPUT
 select @result as 'results'

Code 2:

 DECLARE @result INT
 EXEC master.dbo.xp_fileexist 'C:\Users\James.James\New folder\New folder\$11F7BC3A.jpg', @result OUTPUT
 select @result as 'results'

"it does not work" doesn't tell us much. What does "not work" mean? — Thom A
– Thom A ♦, Commented Dec 11, 2019 at 22:07
Sorry for that. "Does not work" means 0 is returned, rather than 1, which is odd. — Harry
– Harry, Commented Dec 11, 2019 at 22:11
Please edit additional information directly into the question. — Dale K
– Dale K, Commented Dec 11, 2019 at 22:12
I'm importing files into sql and need to use if-statement to skip files which exists in a pre-given table, but not in that folder. Are there any better ways to achieve this purpose? — Harry
– Harry, Commented Dec 12, 2019 at 0:15

Gen Wan · Accepted Answer · 2019-12-11 23:21:27Z

1

Please create two new folders under Users & put in the file & use the below code to test it:

 DECLARE @result INT
 EXEC master.dbo.xp_fileexist 'C:\Users\New folder\New folder\$11F7BC3A.jpg', @result OUTPUT
 select @result as 'results'

If this does work, it means you do not have the full permission to manage James.James folder

Update:

Please follow the instruction in the link to configure the permission:

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-file-system-permissions-for-database-engine-access?view=sql-server-ver15

edited Dec 11, 2019 at 23:21

answered Dec 11, 2019 at 23:02

Gen Wan

2,0192 gold badges15 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Harry Over a year ago

Thanks. It does work. The sql server is under local environment, I suppose to have the full permission to manage the server. Have no idea about the permission to manage one folder. How can I get the full permission then?

Gen Wan Over a year ago

@Harry I am glad I could help. Could you accept my answer to close this issue?

Collectives™ on Stack Overflow

Set the path when checking if a file exists in a folder via SQL Server 2016

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related