Check Existing File using xp_cmdshell in SQL and return a value in cmdshell

Question

I am trying to create a file using the SP - MSSQL. before creating a file i want to check Whether the file exist . IF Exist i want to replace a new one or else create a new file. Want to return a value while executing the cmdshell in sql.

               DECLARE @Text AS VARCHAR(100)
               DECLARE @Cmd AS VARCHAR(100)
               DECLARE @exstFile AS VARCHAR(100)

            set @cmd = 'IF EXIST Z:\AppTextFile.txt (ECHO 1) ELSE (ECHO 0)'
            EXEC @cmd = xp_cmdshell @cmd
            select @cmd datum

Instead of `ECHO`, try `EXIT` to set the exit code. – Dan Guzman Apr 27 '18 at 10:40 — Dan Guzman, Apr 27 '18 at 10:40
Why don't you use `xp_fileexist` – Sandeep Apr 27 '18 at 10:47 — Sandeep, Apr 27 '18 at 10:47

score 0 · Answer 1 · answered Apr 27 '18 at 10:48

0

An alternative way for to check files existence could be to use xp_FileExist:

DECLARE @Exists INT;

EXEC master..xp_FileExist N'U:\SSIS\Test.txt', @Exists OUTPUT

select @Exists

answered Apr 27 '18 at 10:48

M.Ali

67,945
13
101
127

I remember this SP was working for UNC share name only and not for network drive. Is that fixed now? – Sandeep Apr 27 '18 at 10:50
Thanks bro... Thank you very much. It Worked with me well. @M.ali – Manu C Rajan Apr 27 '18 at 11:13

Check Existing File using xp_cmdshell in SQL and return a value in cmdshell

1 Answers1