asked on

How to get Notifications in c# if Stored Procedure changes.

Hi,

We have a big application in C# and SQL Server. The SQL Server team is doing the changes in the Stored Procedures which were not communicating to Application Development team. Perhaps It is causing the application break. Can someone please suggest me the possible ways,

1. How to write a C# programme to be notified of these changes?

ASKER CERTIFIED SOLUTION

Éric Moreau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kanti Prasad

Hi

You can set up an auto email sending on the server in question instead of using c#
https://msdn.microsoft.com/en-ie/library/ms190307.aspx

You need to query the sys.objects for modify_date to see what is modified and create_date to see what are the new SPs added.
----15 is the number of days
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SP changes in the last 15 days',
@recipients = 'emailidname@abc.com',
@query = 'SELECT name FROM sys.objects WHERE type = 'P' AND DATEDIFF(D,modify_date, GETDATE()) < 15' ,
@subject = 'SP changes in the last 15 days',
@attach_query_result_as_file = 1 ;

Vitor Montalvão

How to write a C# programme to be notified of these changes?

This can only be made in the SQL Server side where the objects are stored. Best way to do that is with DDL trigger but you need to talk with the database guys to achieve that.
Good luck.