AnsweredAssumed Answered

T-SQL Query for Checked Out Files Email

Question asked by Sandy Scott on Feb 19, 2020
Latest reply on Feb 19, 2020 by Mario Zahren

Hi,

I've wanted to set up email notifications about checked out files for some time now, and finally got round to it, so I thought I'd share this.

 

 

It's a pure SQL Server solution - there's some downsides to that (this is not the prettiest code I've ever written), but it means no additional server software to install & maintain because SQL Server has built in scheduling, email and scripting.

 

I wrote 2 versions

  • No description (just filename and folder)
  • Includes the description.

the one that pulls up the description of the file is more complicated and less portable because your PDM variable that corresponds to the description is likely to be different to mine.

 

Appearance is customised with a <style> tag in the head of the html email to customise the appearance - this should work on most email systems - No issues with Office365/Outlook as tested here.

 

To set it up

  • (IT Admin) Set up a Database mail profile on the SQL Server
  • (IT Admin) Create user with read-only access to the database and the mail profile
  • Customise the script with the name of your database, email subject and Description Variable and Configuration IDs (if using)
  • (IT Admin) Install the script as a scheduled job that will run as the read-only user.

Outcomes