I am using SSMS to create a stored procedure in SQL.

Problem: I have a folder in which daily some files are getting published via the different process. The file name is in the format "Testfile datetimestamp". I want to create a stored procedure in SQL that will basically check if the file with todays date is present in the folder or not. If the file with current date is present it will trigger an email to the users that 'We have received the file' if not it will trigger email to receiver 'We are yet to receive file'.

Since I am completely new to this topic, I am not able to figure out how to start with this problem.

Any help on this would be appreciated.

  • 3
    Reading files from folders is not really something you should be doing in SQL server. Nor is sending emails.
    – Dale K
    Aug 6 at 5:59
  • 2
    Don't do it in SQL Server. Ignoring that you shouldn't have SQL Server monitoring file systems (that task is much better performed by external processes) Database Mail is kludgy, at best (with all the limitations of .NET Framework's SmtpClient), and will present you with many problems trying to deliver mail to modern SMTP servers that require a TLS 1.2 minimum protocol version over their SMTPS or STARTTLS connections. Aug 6 at 6:26
  • Actually problem is once we have the file ready in drive.. We are doing other Sql operations as well on the received data. To save resources we were asked to do it on SQL
    – SS25
    Aug 6 at 6:33
  • 3
    This sounds like something more suited to a powershell or other script process. Even if you are doing other processing in SQL Server eg some ETL, perhaps have a queuing table where you indicate success and have an external process monitor this and send the email.
    – Stu
    Aug 6 at 8:26
  • 1
    You can use SQLDbMail instead sqlshack.com/configure-database-mail-sql-server
    – Karthikcbe
    Aug 6 at 9:27


Browse other questions tagged or ask your own question.