question

mehta.parth09 avatar image
mehta.parth09 asked

How to get application log filename along with its location in SQL Server

I would like to fetch any filename from path in SQL server and result should show filename along with its location. I have written below script wherein @files is only fetching file name from different path. I would like to have their location as well. Location may be different and folder name is basically date in yyyymmdd format. Here I would like to fetch file which starts with ERR as prefix from 3 different folders which are 20170602, 20170603, 20170604. Please help me. declare @files table (Filename varchar(1000)) declare @foldername varchar(1000) = REPLACE(CONVERT(varchar(10), GETDATE()-3, 112), '/', '') declare @foldername2 varchar(1000) = REPLACE(CONVERT(varchar(10), GETDATE()-2, 112), '/', '') declare @foldername3 varchar(1000) = REPLACE(CONVERT(varchar(10), GETDATE()-1, 112), '/', '') declare @folderpath varchar(1000) declare @folderpath2 varchar(1000) declare @folderpath3 varchar(1000) set @folderpath = 'xp_cmdshell ''dir E:\Batch\Error_Logs\'+@foldername+ ' /b''' set @folderpath2 = 'xp_cmdshell ''dir E:\Batch\Error_Logs\'+@foldername2+ ' /b''' set @folderpath3 = 'xp_cmdshell ''dir E:\Batch\Error_Logs\'+@foldername3+ ' /b''' INSERT INTO @files exec (@folderpath) INSERT INTO @files exec (@folderpath2) INSERT INTO @files exec (@folderpath3) select * from @files where Filename like 'ERR_%'
sqlsql-serverdatabaseautomation
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

use `dir /b /s` to get the full file path
0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.