0

I'm trying to check if the network location file exists or not using SQL query. I'm using xp_fileexist. But for network location, this extended SP doesn't work to identify if the file exists or not. Is there any other way to check file existence of network location file from SQL query ?

Ajit Medhekar
  • 1,018
  • 1
  • 10
  • 39

2 Answers2

1

This will return all files in a location. You can find use the temp table results to find if a file exists.

/* Create the table to store file list with full info, including file timestamp*/
DECLARE @FileList TABLE (FileNumber INT IDENTITY(1,1), FileName VARCHAR(256))

/* Insert file list from directory to SQL Server */
DECLARE @Path varchar(256) = 'dir "\\NetworkLocation"'
DECLARE @Command varchar(1024) =  @Path + ' /A-D-S /B'

INSERT INTO @FileList (FileName)
EXEC xp_cmdshell @Command
Mike Petri
  • 570
  • 3
  • 10
  • Yeah, it's a good workaround. But when I'm executing xp_cmdshell from SQL (SSMS), I'm getting an error - "Access is denied." But when I'm running the same from CMD prompt, I can get the results. I have access on those files and even I can see those files from File Explorer. – Ajit Medhekar Jun 18 '21 at 10:24
  • That's permissions. Open SQL Configuration Manager on the server. The account running SQL Server doesn't have access to the network location. – Mike Petri Jun 18 '21 at 15:35
0

Yes, it is better option to use xp_cmdshell.

xp_cmdshell is disabled by default, use sp_configure to enable it. Visit sp_configure page to enable xp_cmdshell.

Also check if the folder has appropriate permissions.

There is a good workaround for folder permissions in thread which might help you.

NiharikaMoola-MT
  • 4,700
  • 1
  • 3
  • 15