How to create sql server report for mail recepient?

Question

Hi Team I need some help i have a query and want to write a sql server report that will do a check . If Stock on Hand = 0 Then it must send a reminder to the recipient mail. That Critical Cyclical Standard and Slow Movement. SELECT InvWarehouse.StockCode InvMaster.Description InvWarehouse.Warehouse InvWarehouse.QtyOnHand InvWarehouse.QtyAllocated InvWarehouse.QtyOnOrder InvWarehouse.QtyInTransit InvWarehouse.QtyOnBackOrder FROM sysproR.dbo.InvMaster InvMaster sysproR.dbo.InvWarehouse InvWarehouse WHERE InvMaster.StockCode = InvWarehouse.StockCode

Jithu Thomas · Answer

Once its work please accept my answer :) happy coding....

Guest User · Answer

Hi Jithu Thanks for your responce let me work on this will give an update. This should be enough for me to test and write.

Jithu Thomas · Answer

Configure Database Mail - reference: - https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/configure-database-mail?view=sql-server-ver16 Modify your query to check for QtyOnHand = 0 and format the results for the email. DECLARE @body NVARCHAR(MAX); -- Prepare the email body with the list of items having Stock on Hand = 0 SET @body = N' Stock Alert: Items with zero stock on hand StockCode Description Warehouse QtyOnHand QtyAllocated QtyOnOrder QtyInTransit QtyOnBackOrder '; -- Add the rows to the email body SELECT @body = @body + N' ' + InvWarehouse.StockCode + N' ' + InvMaster.Description + N' ' + InvWarehouse.Warehouse + N' ' + CAST(InvWarehouse.QtyOnHand AS NVARCHAR) + N' ' + CAST(InvWarehouse.QtyAllocated AS NVARCHAR) + N' ' + CAST(InvWarehouse.QtyOnOrder AS NVARCHAR) + N' ' + CAST(InvWarehouse.QtyInTransit AS NVARCHAR) + N' ' + CAST(InvWarehouse.QtyOnBackOrder AS NVARCHAR) + N' ' FROM sysproR.dbo.InvMaster InvMaster JOIN sysproR.dbo.InvWarehouse InvWarehouse ON InvMaster.StockCode = InvWarehouse.StockCode WHERE InvWarehouse.QtyOnHand = 0; -- Close the HTML tags SET @body = @body + N' '; -- Check if there are items with zero stock on hand IF EXISTS ( SELECT 1 FROM sysproR.dbo.InvWarehouse WHERE QtyOnHand = 0 ) BEGIN -- Send the email EXEC msdb.dbo.sp_send_dbmail @profile_name = 'YourMailProfile' -- Replace with your Database Mail profile name @recipients = 'recipient@example.com' -- Replace with the recipient email address @subject = 'Stock Alert: Items with zero stock on hand' @body = @body @body_format = 'HTML'; END Create a SQL Server Agent Job: Open SQL Server Management Studio (SSMS). In Object Explorer expand the SQL Server Agent node. Right-click Jobs and select New Job. In the New Job dialog enter the job name and description. Go to the Steps page click New and add a new step: Step Name: Check Stock Levels Type: Transact-SQL script (T-SQL) Database: Select the database where your tables are located. Command: Paste the above SQL query. Go to the Schedules page and create a new schedule for how often you want this check to run (e.g. daily at a specific time).