-3

Is there a way i can detect if the text file is open using MS SQL server.I want to check if the files is open, if it is open then send out an mail alert.

RAJ
  • 57
  • 7

1 Answers1

0

I have got a basic script. Modify as per your requirement.

DECLARE @FILENAME VARCHAR(200)='TEST.TXT' ---- THE FILE WHICH YOU WANT TO CHECK 
DECLARE @QUERY VARCHAR(500) 

CREATE TABLE #tasklist  (      output VARCHAR(max)   ) 

DECLARE @TASKS TABLE   (      output VARCHAR(max)   ) 

INSERT INTO #tasklist 
EXEC ('EXEC XP_CMDSHELL ''WMIC PROCESS GET COMMANDLINE''') 

SET @QUERY = 'SELECT * FROM #TASKLIST WHERE OUTPUT LIKE ''%' 
             + @FILENAME + '%''' 

INSERT INTO @TASKS 
EXEC (@QUERY) 

IF EXISTS(SELECT Count(1) 
          FROM   @TASKS) 
  BEGIN 
      SELECT 'THE FILE IS OPEN AS FOLLOWS', 
             * 
      FROM   @TASKS 
  END 
ELSE 
  BEGIN 
      SELECT 'NO SUCH FILE OPEN' 
  END 

DROP TABLE #tasklist
Ranjana Ghimire
  • 1,785
  • 1
  • 12
  • 20
  • I tried running the above code by altering it as required, but it doesnt seem to be working. – RAJ Jul 26 '18 at 18:17
  • Code is not erroring out, i dont see the output in table #tasklist for the file i am looking for, i can see the table with blank rows. – RAJ Jul 29 '18 at 17:28
  • The weird thing what i observed is if run the same command in the command prompt it details the file name, but if i run it in SQL it doesnt details the file name. – RAJ Jul 29 '18 at 18:08
  • May be you should give fill access in that specific folder. MSSQL account doesnt seem to have acess. Try by giving full access. – Ranjana Ghimire Jul 30 '18 at 07:37