SQL CODE to check if a list of csv file exists

• pet.dan.mail

  SSC Veteran

  Points: 222

  More actions

  July 24, 2018 at 1:54 pm

  #373804

  Hello,
  When I type the below path on web browser , it shows a list of  csv files.
  \\tntfappl11.companyname.net\Project012\1.Cust\LDSH\4\7

  Now using (SQL Server) sql code , how can I
  1) access this path(folder location) 
  2) I have a table which has a column 'filename' and this column has all the csv file name entries  that are to be checked in the above folder if they exists or not .   . If all files exists then return 1 else return list of files that are missing in folder.

  Please help..

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  July 24, 2018 at 2:12 pm

  #1998887

  You could experiment with these commands:
  EXEC sys.xp_dirtree '\\tntfappl11.companyname.net\Project012\1.Cust\LDSH\4\7', 1, 1;

  EXEC sys.xp_cmdshell 'DIR \\tntfappl11.companyname.net\Project012\1.Cust\LDSH\4\7\*.csv';

  You would want to create a temp table or table variable to capture the output, then use "INSERT INTO <table> EXEC ..."

  One is an undocumented/unsupported external procedure, the other is usually disabled for security reasons.  Take your pick.

• pet.dan.mail

  SSC Veteran

  Points: 222

  More actions

  July 24, 2018 at 2:50 pm

  #1998893

  Thank You Scott,

  I tried,
  1)    EXEC sys.xp_cmdshell 'DIR \\tntfappl11.companyname.net\Project012\1.Cust\LDSH\4\7\*.csv';
      Got error  "The EXECUTE permission was denied on the object 'xp_cmdshell', database 'mssqlsystemresource', schema 'sys'.   

  2)  EXEC sys.xp_dirtree '\\tntfappl11.companyname.net\Project012\1.Cust\LDSH\4\7', 1, 1;
  This displays columnnames    ->     subdirectory, depth, file   but no data.

  I can see folder structures through web browser.  but why its empty here in sql server...

  Thanks!

• Scott Coleman

  One Orange Chip

  Points: 27452

  More actions

  July 24, 2018 at 3:36 pm

  #1998900

  You have to be a sysadmin (under default settings) to run xp_cmdshell.  Also, in both cases, the directory has to be readable by the SQL Server service account.  And I'm assuming that your web browser is doing normal file access like File Explorer, and you're not really browsing a web site that is imitating a file listing.

  If xp_dirtree won't even show any listings for the top level share ("\\tntfappl11.companyname.net\Project012"), I would be pretty sure that it is a file permissions problem.  xp_dirtree does not throw errors, it reports what it finds.  And if it can't read the folder it finds nothing.

  xp_cmdshell is the easiest way to do this, but it's a big security hole so I would not recommend trying to get it enabled.