I have a large CI pipeline, it runs a series of deployments against a large set of databases(75 to be specific) on a (mostly) single SQL Server(2019). As part of this CI pipeline, and post deployment I am running a large number of database tests. These tests often create(and delete) SQL Agent jobs as part of there specific prerogatives. They also often invoke standard jobs belonging to a specific database. You can think of the process flow as the following: 1. Set some stuff up 2. Run a job 3. Wait for it to complete 4. Validate it some condition These are being run in CI as the contents of the "setup" and "jobs" can change as the software itself is changes(we store database code in source control and it is deployed with each version of the software). Now most(as far as I'm aware) of the jobs have notifications disabled, and the few that do have notifications enabled are ONLY sending "Failure" notifications: So there shouldn't really be that many even with the 75 instance running not that many jobs are failing at the same time, further more we at a later stage prevent the server from sending emails(so suppress these alerts). However, occasionally I do still receive the following error in my database tests: > Unable to post notification to SQLServerAgent (reason: The maximum > number of pending SQLServerAgent notifications has been exceeded. The > notification will be ignored.) Typically I don't care, but this will cause my DB tests to occasionally fail polluting the results of my CI run. How do I increase the number of allowed "Pending SQLServerAgent notifications", I don't care what the affect that this has on the server as long as "some day" the pending notification is made? Is the only way to reduce or eliminate the existing notification configuration in all jobs? Or is there a configuration setting that can be changed here?

I want to listen to every transaction that is recorded in the database And I noticed that there is a concept in Postgres under the title **LISTEN** and **NOTIFY** (Of course, I am not sure that this is the right way) Now I want to write a trigger that sends a notification to the channel when any operation occurs in any table Is it possible? My way is right? thanks Postgres TRIGGER to call NOTIFY with a JSON payload

I need to migrate my Event Notifications from current server to another one. I would like to generate that code, but I couldn't find an way to do that. **Try 1** I have tried reverse engineering from sys.server_event_notifications but it does not return a value that I can marry with sys.event_notification_event_typeid. **Try 2** I tried also a DAC to query system views, getting more columns from that, but no joy. **Try 3** Tried sp_helptext the objects, but it can't be found. **EXPECTED OUTPUT** (for one event) CREATE EVENT NOTIFICATION [CaptureDeadlocks] ON SERVER WITH FAN_IN FOR DEADLOCK_GRAPH TO SERVICE N'DeadlockService', N'current database'; Any ideas?

I’m developing a medical app (learning purpose) where users can store and manage blood test results, medications, doctor appointments, and medical documents. I’ve designed a PostgreSQL database schema, but I’d love some feedback on best practices, potential pitfalls, and optimizations. Functional Requirements: - User Authentication: Users can log in using email/password or Google OAuth. - User Profile: Stores name, height, weight, and other personal details. - Medical Documents: Users can upload, view, and organize PDFs/images of blood tests, prescriptions, and other medical reports into folders. - Blood Tests Storage: Users input their blood test values (e.g., TSH, T3, T4, cholesterol, etc.), and the system shows trends & graphs over time. - Medication Tracking: Users can schedule medications with: - Start date - Dosage (e.g., 2 pills per day) - Reminder times (e.g., 08:00 AM, 06:00 PM) - Appointments: Users schedule doctor visits and get notifications. - Notifications System: Sends reminders for medications & doctor visits. Users can snooze reminders (e.g., “remind me in 1 hour”). I want to be able to take inputs on how many doses the user needs to take, how often, at wich time, when did he start. Also if the user can not take the medication at the time of the reminder I want the system to make a new reminder an hour later.

I'm seeking recommendations for free / open event monitoring tools. HPOV costs continue to multiply like so many wet gremlins, and we've had enough. There are some interesting suggestions in this article and the subsequent comments, but before I set about trying many of these I'm sure you all have found a few that you like that may or may not be in this list. There is also this thread , but we're really shooting for a no-cost solution. The primary requirements are: 1. Perform a basic ping test 2. Check the up-state of the SQL Server service. 3. Send an email alert if 1 or 2 fail. 4. Works as far back as Sql Server 2005 - present. (This is where PBM comes up a bit short) Additional desired-not-required functionality would be: 1. Ability to pass a synthetic transaction, which if failed triggers an alert. 2. Ability to measure said transaction for duration, not just completion, and alert if a threshold is exceeded. 3. Alert on other measurable (logged) events. Thanks much for any recommendations! Edit: Currently investigating SpiceWorks

Is there any way to use Postgres Listen/Notify feature to deliver a message to a channel and have only one listener consume this message? The purpose for this is that I have multiple 'worker' apps all listening to the same Postgres channel. But I only want the work done once per message received through the notification channel. If Listen/Notify is not the correct feature in Postgres, is there a seperate feature I should be using? Ideally I would like to do this without using any additional extensions.

I'm trying to use PostgreSQL notifications but they are not working for me. # Docker-Compose ## docker-compose.yml

version: "3.8"

secrets:
  db_root:
    file: ./db/.secrets/.psql_name_admin
  db_pwd:
    file: ./db/.secrets/.psql_pwd_admin
  db_dbname:
    file: ./db/.secrets/.psql_name_db

services: 
  db:
    build: 
      context: ./db
    restart: unless-stopped
    secrets:
      - db_root
      - db_pwd
      - db_dbname
    environment:
      - POSTGRES_DB_FILE=/run/secrets/db_dbname
      - POSTGRES_USER_FILE=/run/secrets/db_root
      - POSTGRES_PASSWORD_FILE=/run/secrets/db_pwd
    ports: 
      - 5442:5432
    volumes: 
      - ./db/data:/var/lib/postgresql/data

## ./db/Dockerfile

FROM postgres:latest
COPY init/*.sql /docker-entrypoint-initdb.d/

## ./db/init/0_blogpost.sql

CREATE TYPE BLOGPOST_ENUM AS ENUM ('Technical', 'Agile');

CREATE TABLE writer (
    id SERIAL PRIMARY KEY ,
    name TEXT NOT NULL ,
    surname TEXT NOT NULL ,
    nick TEXT
);

CREATE TABLE blog_post (
    id SERIAL PRIMARY KEY ,
    title TEXT NOT NULL ,
    content TEXT NOT NULL ,
    blogpost_type BLOGPOST_ENUM NOT NULL ,
    writer_id SERIAL REFERENCES writer(id)
);

CREATE OR REPLACE FUNCTION blogpost_added_subscription()
RETURNS trigger AS
$$
BEGIN
	PERFORM pg_notify(
		'blogpost_added' ,
--		json_build_object(
--			'event' , 'newBlogpostAdded' ,
--			'subject' , new.id
--		)::text
    new.id::text
	);

	RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE TRIGGER notifiy_after_blogpost_insert
  AFTER INSERT ON blog_post
  FOR EACH ROW
  EXECUTE PROCEDURE blogpost_added_subscription()
;

INSERT INTO writer (name, surname) VALUES ('Emanuel', 'Vollmer');

INSERT INTO blog_post (title, content, blogpost_type, writer_id)
VALUES (
 'Graphql',
 'Graphql is a query language...',
 'Technical',
 (SELECT id FROM writer WHERE name = 'Emanuel')
);

INSERT INTO blog_post (title, content, blogpost_type, writer_id)
VALUES (
 'Flutter',
 'Flutter is a framework for Dart...',
 'Technical',
 (SELECT id FROM writer WHERE name = 'Emanuel')
);

# Working with pgcli I opened two sessions with

$ pgcli -h 0.0.0.0 -p 5442 -U sysmanager -d demo_db

On session 1 I run

sysmanager@0:demo_db> listen blogpost_added;
LISTEN
Time: 0.001s

On session 2 I run

sysmanager@0:demo_db> INSERT INTO blog_post (title, content, blogpost_type, writer_id)
 VALUES (
  'PostgreSQL Notifiers',
  'PostgreSQL is a powerful, open source object-relational database system...',
  'Technical',
  (SELECT id FROM writer WHERE name = 'Emanuel')
 );
INSERT 0 1
Time: 0.021s

But I'm not receiving any notifications on Session 1, Am I missing something?

We are in the process of removing a previous dba login and he owns all the endpoints and event notification objects. Endpoints were easy to change; Event notification objects not so much. I found this thread about changing the owner of an event notification object (you have to drop and recreate). I don't want to go through this process again if I can avoid it. I doubt it's possible, but outside of logging in as another user, can you create an event notification that runs as sa, etc.?

We have two Oracle database servers. One for production with one instance on it, and another one with multiple instances for Dev, Test, etc. I have some jobs that run on production and email me when they fail. I had originally set the sender to be something like server1.prod@company.com, so that I knew what environment the error was coming from. Problem is when we copy production to one of the test or dev environments to get them up to date, then the job errors out for whatever reason, but it says it is coming from server1.prod@company.com which is incorrect. I would like the notification email to pick up the servername and instance name that it is sent from, so if it comes from test it would be server2.test@company.com. If that is not possible, then it would be fine to get this info in the subject line or body of the email. I can get the server and instance name pretty easy with SELECT host_name, instance_name FROM v$instance; ,but I can't figure out how to get this to be used in the job email notifications. I set these jobs up using the sql developer wizard, so if you can show me how to change it with that would be fine, or with pl/sql code, I am ok with either. If none of this is possible, then is there I way I can make the jobs to not send out once they get to the non production environments at least because I really don't care if they fail there.

The documentation for NOTIFY states > The queue is quite large (8GB in a standard installation) and should be sufficiently sized for almost every use case Is it possible to configure the size of the queue? I couldn't find anything about this in the documentation or in postgresql.conf.

From last two days I can see following in SQL Server errorlog. AppDomain 2 (mssqlsystemresource.dbo[runtime].1) unloaded. & AppDomain 2 (mssqlsystemresource.dbo[runtime].1) is marked for unload due to memory pressure. I have system environment like this : OS- microosft windows server 2012 R2 Standard processors - Intel(R) Xeon(R) CPU E5-2670 0 @2.60 GHz Installed Memory (RAM) - 8GB Hardware Information - VMWare, Inc. VMware Virtual Platform SQL Server - Microsoft SQL Server Enterprise (64-bit) I have checked out in local server for yesterday's event log . I have found thses events Server Name ID Severity Source Log Date and Time ABC 1008 Error Microsoft-Windows-Perflib Application 10/01/2015 2:21 ABC 2 Error TeamCentral Application 9/30/2015 4:20:41 PM ABC 1309 Warning ASP.NET 4.0.30319.0 Application 9/30/2015 4:20:41 PM ABC 10016 Error Microsoft-Windows-DistributedCOM System 9/30/2015 4:06:23 PM ABC 10016 Error Microsoft-Windows-DistributedCOM System 9/30/2015 4:00:04 PM ABC 10016 Error Microsoft-Windows-DistributedCOM System 9/30/2015 4:00:02 PM ABC 2 Error TeamCentral Application 9/30/2015 3:55:13 PM ABC 1309 Warning ASP.NET 4.0.30319.0 Application 9/30/2015 3:55:13 PM ABC 10016 Error Microsoft-Windows-DistributedCOM System 9/30/2015 3:40:06 PM ABC 10016 Error Microsoft-Windows-DistributedCOM System 9/30/2015 3:40:03 PM ABC 1309 Warning ASP.NET 4.0.30319.0 Application 9/30/2015 3:23:54 PM ABC 2 Error TeamCentral Application 9/30/2015 3:23:54 PM ABC 2 Error TeamCentral Application 9/30/2015 3:22:48 PM ABC 1309 Warning ASP.NET 4.0.30319.0 Application 9/30/2015 3:22:48 PM ABC 2 Error TeamCentral Application 9/30/2015 3:21:43 PM ABC 1309 Warning ASP.NET 4.0.30319.0 Application 9/30/2015 3:21:43 PM ABC 2 Error TeamCentral Application 9/30/2015 3:18:24 PM ABC 1309 Warning ASP.NET 4.0.30319.0 Application 9/30/2015 3:18:24 PM ABC 2 Error TeamCentral Application 9/30/2015 3:16:49 PM ABC 1309 Warning ASP.NET 4.0.30319.0 Application 9/30/2015 3:16:49 PM ABC 1309 Warning ASP.NET 4.0.30319.0 Application 9/30/2015 3:15:43 PM ABC 2 Error TeamCentral Application 9/30/2015 3:15:43 PM ABC 1010 Error Microsoft-Windows-Perflib Application 9/30/2015 10:42:00 AM And in Event I am getting such type of message : Event code: 3005 Event message: An unhandled exception has occurred. Event time: 9/30/2015 4:20:41 PM Event time (UTC): 9/30/2015 1:20:41 PM Event ID: 72c24cb89beb4d7f80bec250aaa686e3 Event sequence: 757 Event occurrence: 17 Event detail code: 0 Application information: Application domain: /XX/XXXX/1/ROOT/TeamCentral-2-130850431401577570 Trust level: Full Application Virtual Path: /TeamCentral Application Path: C:\XXXX\wwwroot\TeamCentral\ Machine name: ABC Process information: Process ID: 2180 Process name: w3wp.exe Account name: NT AUTHORITY\NETWORK SERVICE Exception information: Exception type: HttpException Exception message: The remote host closed the connection. The error code is 0x800704CD. at System.Web.Hosting.IIS7WorkerRequest.RaiseCommunicationError(Int32 result, Boolean throwOnDisconnect) at System.Web.Hosting.IIS7WorkerRequest.ExplicitFlush() at System.Web.HttpResponse.Flush(Boolean finalFlush, Boolean async) at System.Web.HttpWriter.WriteFromStream(Byte[] data, Int32 offset, Int32 size) at DevExpress.Web.ASPxClasses.Internal.HttpUtils.WriteFileToResponse(Page page, MemoryStream stream, String fileName, Boolean saveAsFile, String fileFormat, String contentType) at DevExpress.Web.ASPxClasses.Internal.HttpUtils.WriteFileToResponse(Page page, MemoryStream stream, String fileName, Boolean saveAsFile, String fileFormat) at DevExpress.Web.ASPxGridView.Export.ASPxGridViewExporter.WriteToResponse(String fileName, Boolean saveAsFile, String fileFormat, ExportToStream getStream, ExportOptionsBase options) at DevExpress.Web.ASPxGridView.Export.ASPxGridViewExporter.WriteXlsxToResponse(String fileName, Boolean saveAsFile) at DevExpress.Web.ASPxGridView.Export.ASPxGridViewExporter.WriteXlsxToResponse(String fileName) at InfoArch.Web.Grid.ExportHelper.Response(ExportFileType fileType, Grid structure) at InfoArch.Web.Grid.GridManager.SaveToXlsx(String filename) at System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) at System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Request information: Request URL: http://ABC/TeamCentral/Reporting/GridView.aspx?layoutid=38&mf=report&where=&description=PZF6Q0PKJzWQDHkN08mydZNuJ3fZ(:#cd6TgCAL0hv4u4=&screenheight=618 Request path: /TeamCentral/Reporting/GridView.aspx User host address:XXX.XXX.XX.XXX User: rafeek Is authenticated: True Authentication Type: Thread account name: NT AUTHORITY\NETWORK SERVICE Thread information: Thread ID: 190 Thread account name: NT AUTHORITY\NETWORK SERVICE Is impersonating: False Stack trace: at System.Web.Hosting.IIS7WorkerRequest.RaiseCommunicationError(Int32 result, Boolean throwOnDisconnect) at System.Web.Hosting.IIS7WorkerRequest.ExplicitFlush() at System.Web.HttpResponse.Flush(Boolean finalFlush, Boolean async) at System.Web.HttpWriter.WriteFromStream(Byte[] data, Int32 offset, Int32 size) at DevExpress.Web.ASPxClasses.Internal.HttpUtils.WriteFileToResponse(Page page, MemoryStream stream, String fileName, Boolean saveAsFile, String fileFormat, String contentType) at DevExpress.Web.ASPxClasses.Internal.HttpUtils.WriteFileToResponse(Page page, MemoryStream stream, String fileName, Boolean saveAsFile, String fileFormat) at DevExpress.Web.ASPxGridView.Export.ASPxGridViewExporter.WriteToResponse(String fileName, Boolean saveAsFile, String fileFormat, ExportToStream getStream, ExportOptionsBase options) at DevExpress.Web.ASPxGridView.Export.ASPxGridViewExporter.WriteXlsxToResponse(String fileName, Boolean saveAsFile) at DevExpress.Web.ASPxGridView.Export.ASPxGridViewExporter.WriteXlsxToResponse(String fileName) at InfoArch.Web.Grid.ExportHelper.Response(ExportFileType fileType, Grid structure) at InfoArch.Web.Grid.GridManager.SaveToXlsx(String filename) at System.Web.UI.WebControls.ImageButton.OnClick(ImageClickEventArgs e) at System.Web.UI.WebControls.ImageButton.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Custom event details: I have gone through the Stackexchange before asking the question i have found out the ref https://dba.stackexchange.com/questions/63720/app-domain-is-marked-for-unload-due-to-memory-pressure but some point of view , unable to understand the event log message. Output of select @@version; > Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64) Oct 19 2012 > 13:38:57 Copyright (c) Microsoft Corporation Enterprise Edition > (64-bit) on Windows NT 6.2 (Build 9200: ) (Hypervisor) and output of exec sp_configure; Name minimum maximum config_value run_value max server memory (MB) 128 2147483647 2147483647 2147483647 Any suggestion would be appreciated.

My team is looking at getting live updates from our Oracle database from a frequently updated table. I have discovered [CQN](https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_cqn.htm#CHDJBFEC) and configured a test environment to use UTL_HTTP to POST to a webhook when the table changes. However, in the 'Best Practices' section of the documentation for CQN: > Register few queries—preferably those that reference objects that rarely change. And in [other articles](https://dzone.com/articles/demo-oracle-database-continuous-query-notification) for instance: > if you know the table is subject to a lot of change, then your apps will be better off simply polling the table and avoiding any CQN overhead. Note that CQN was designed to be used for relatively infrequently updated tables. CQN had seemed like a great solution until I read these disclaimers. The table we want to get live updates from has about 1000 inserts per second during busy time bursts. The alternative to CQN appears to be polling to query this table every second. Is polling like that the best solution for getting live updates from a table, or is there something I am missing? I am working with Oracle 11g. Thank you.

I have 40 views that are integrated/synced to a work order management system on a weekly basis. The views are based on GIS tables which are *notoriously messy*. Over time, the views can end up becoming **invalid**. Example: - A user deletes or renames a column in an underlying table, but fails to notify I.T. of the change, so the view is not updated accordingly. - As a result, the view would become invalid: ORA-04063: view "ROADS_VW" has errors or ORA-00904: "FIELD1": invalid identifier. -------- I would like to catch & fix invalid views (or fix the underlying data) before the views are synced to the work order management system each week. **Is there a way to get Oracle to notify me about invalid views?** For instance, if the integrations occur on Fridays, then get an email on Thursdays if any of the views are invalid.

CREATE OR REPLACE FUNCTION notify_col() RETURNS trigger AS $BODY$ BEGIN PERFORM pg_notify('channel', row_to_json(NEW)::text); RETURN NEW; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; I'm trying to listen on *channel*, for any updates in the row (transformed with row_to_json()). But I want pg_notify() to send only updated values of the row, not the whole row. Do I need an if condition or is there is a better way to do this?

I was looking for query notification to get notified on multiple client for a specific table changes. It seems that this feature has been removed in SQL Server 2008 R2. Documentation is not very clear about that. When exactly has this been removed (which version exactly)? Is there any other way to manage that without additional components?

I need to automate email notifications on blocking on some SQL 2016 servers. I have had a look at running a job through the SQL agent but seems hack n slash. I found an alternative method online that uses queues and event notifications (can't find source again) that seems more suited to the task, however the queue is not populating with entries and am not sure why. Below is the code I found but it is not working: USE msdb GO EXEC sp_configure 'show advanced options', 1 RECONFIGURE GO EXEC sp_configure 'blocked process threshold', 20 RECONFIGURE GO IF exists (select * from sys.services where name like 'BlockedProcessService') DROP SERVICE [BlockedProcessService] GO IF exists (select * from sys.service_queues where name like 'BlockedProcessQueue') DROP QUEUE [dbo].BlockedProcessQueue GO CREATE QUEUE BlockedProcessQueue CREATE SERVICE BlockedProcessService ON QUEUE BlockedProcessQueue ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]) GO IF exists (select * from sys.objects where name = 'wait_resource_name' and type = 'TF') DROP FUNCTION [dbo].[wait_resource_name] GO CREATE FUNCTION [dbo].[wait_resource_name](@obj nvarchar(max)) RETURNS @wait_resource TABLE ( wait_resource_database_name sysname, wait_resource_schema_name sysname, wait_resource_object_name sysname ) AS BEGIN DECLARE @dbid int DECLARE @objid int IF @obj IS NULL RETURN IF @obj NOT LIKE 'OBJECT: %' RETURN SET @obj = SUBSTRING(@obj, 9, LEN(@obj) - 9 + CHARINDEX(':', @obj, 9)) SET @dbid = LEFT(@obj, CHARINDEX(':', @obj, 1) - 1) SET @objid = SUBSTRING(@obj, CHARINDEX(':', @obj, 1) + 1, CHARINDEX(':', @obj, CHARINDEX(':', @obj, 1) + 1) - CHARINDEX(':', @obj, 1) - 1) INSERT INTO @wait_resource (wait_resource_database_name, wait_resource_schema_name, wait_resource_object_name) SELECT db_name(@dbid), object_schema_name(@objid, @dbid), object_name(@objid, @dbid) RETURN END GO use master go IF exists (select * from sys.objects where name = 'StartBlockedProcessNotification' and type = 'P') DROP PROCEDURE [dbo].StartBlockedProcessNotification GO CREATE PROCEDURE StartBlockedProcessNotification AS begin if exists (select * from sys.server_event_notifications where name = 'BlockedProcessNotification') begin drop event notification BlockedProcessNotification on server end CREATE EVENT NOTIFICATION BlockedProcessNotification ON SERVER FOR BLOCKED_PROCESS_REPORT TO SERVICE 'BlockedProcessService', 'msdb' end GO EXEC sp_procoption 'StartBlockedProcessNotification', 'startup', 'on' EXEC StartBlockedProcessNotification use msdb go if exists (select * from sys.objects where name = 'BlockedProcessActivationProcedure' and type = 'P') drop procedure BlockedProcessActivationProcedure go CREATE PROCEDURE BlockedProcessActivationProcedure AS --Service Broker DECLARE @message_body xml DECLARE @message_body_text nvarchar(max) DECLARE @dialog uniqueidentifier DECLARE @message_type nvarchar(256) WHILE 1 = 1 BEGIN --Process the queue BEGIN TRANSACTION; RECEIVE TOP (1) @message_body = message_body, @dialog = conversation_handle, @message_type = message_type_name FROM BlockedProcessQueue IF @@ROWCOUNT = 0 BEGIN RAISERROR('Nothing more to process', 0, 1) ROLLBACK TRANSACTION RETURN END IF @message_type = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification ' BEGIN DECLARE @mail_body nvarchar(max) DECLARE @post_time varchar(32) DECLARE @duration int DECLARE @blocked_spid int DECLARE @waitresource nvarchar(max) DECLARE @waitresource_db nvarchar(128) DECLARE @waitresource_schema nvarchar(128) DECLARE @waitresource_name nvarchar(128) DECLARE @blocked_hostname nvarchar(128) DECLARE @blocked_db nvarchar(128) DECLARE @blocked_login nvarchar(128) DECLARE @blocked_lasttranstarted nvarchar(32) DECLARE @blocked_inputbuf nvarchar(max) DECLARE @blocking_spid int DECLARE @blocking_hostname nvarchar(128) DECLARE @blocking_db nvarchar(128) DECLARE @blocking_login nvarchar(128) DECLARE @blocking_lasttranstarted nvarchar(32) DECLARE @blocking_inputbuf nvarchar(max) declare @notificationSubject varchar(125) SET @post_time = CONVERT(varchar(32), @message_body.value(N'(//EVENT_INSTANCE/PostTime)', 'datetime'), 109) SET @duration = CAST(@message_body.value(N'(//EVENT_INSTANCE/Duration)', 'bigint') / 1000000 AS int) SET @blocked_spid = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@spid)', 'int') SET @waitresource = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@waitresource)', 'nvarchar(max)') SET @blocked_hostname = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@hostname)', 'nvarchar(128)') SET @blocked_db = DB_NAME(@message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@currentdb)', 'int')) SET @blocked_login = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@loginname)', 'nvarchar(128)') SET @blocked_lasttranstarted = CONVERT(varchar(32), @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/@lasttranstarted)', 'datetime'), 109) SET @blocked_inputbuf = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocked-process/process/inputbuf)', 'nvarchar(max)') SET @blocking_spid = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@spid)', 'int') SET @blocking_hostname = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@hostname)', 'nvarchar(128)') SET @blocking_db = DB_NAME(@message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@currentdb)', 'int')) SET @blocking_login = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@loginname)', 'nvarchar(128)') SET @blocking_lasttranstarted = CONVERT(varchar(32), @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/@lasttranstarted)', 'datetime'), 109) SET @blocking_inputbuf = @message_body.value(N'(//EVENT_INSTANCE/TextData/blocked-process-report/blocking-process/process/inputbuf)', 'nvarchar(max)') set @notificationSubject = 'MS SQL Server - Blocked Process Report: ' + @@servername SELECT @waitresource_name = wait_resource_object_name, @waitresource_schema = wait_resource_schema_name, @waitresource_db = wait_resource_database_name FROM master.dbo.wait_resource_name(@waitresource) SET @mail_body = 'Posted: ' + ISNULL(@post_time, '') + CHAR(10) + 'Duration: ' + ISNULL(CAST(@duration AS varchar) + ' s', '') + CHAR(10) + CHAR(10) + '==========Blocked Process==========' + CHAR(10) + 'SPID: ' + ISNULL(CAST(@blocked_spid AS varchar), '') + CHAR(10) + 'Wait Resource: ' + ISNULL(ISNULL(QUOTENAME(@waitresource_db) + '.' + QUOTENAME(@waitresource_schema) + '.' + QUOTENAME(@waitresource_name), @waitresource), '') + CHAR(10) + 'Hostname: ' + ISNULL(@blocked_hostname, '') + CHAR(10) + 'Current Database: ' + ISNULL(@blocked_db, '') + CHAR(10) + 'Login Name: ' + ISNULL(@blocked_login, '') + CHAR(10) + 'Last Transaction Started: ' + ISNULL(@blocked_lasttranstarted, '') + CHAR(10) + '----------Input Buffer----------' + CHAR(10) + ISNULL(@blocked_inputbuf, '') + CHAR(10) + CHAR(10) + '==========Blocking Process==========' + CHAR(10) + 'SPID: ' + ISNULL(CAST(@blocking_spid AS varchar), '') + CHAR(10) + 'Hostname: ' + ISNULL(@blocking_hostname, '') + CHAR(10) + 'Current Database: ' + ISNULL(@blocking_db, '') + CHAR(10) + 'Login Name: ' + ISNULL(@blocking_login, '') + CHAR(10) + 'Last Transaction Started: ' + ISNULL(@blocking_lasttranstarted, '') + CHAR(10) + '----------Input Buffer----------' + CHAR(10) + ISNULL(@blocking_inputbuf, '') + CHAR(10) EXEC sp_send_dbmail @profile_name = 'MS SQL Server Alerts', @recipients = 'Mssqlserveralerts ', @subject = @notificationSubject, @body = @mail_body END ELSE IF @message_type IN ('http://schemas.microsoft.com/SQL/ServiceBroker/Error ', 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog ') BEGIN EXEC sp_send_dbmail @profile_name = 'MS SQL Server Alerts', @recipients = '', @subject = @notificationSubject, @body = @dialog END CONVERSATION @dialog END COMMIT TRANSACTION END GO ALTER QUEUE BlockedProcessQueue WITH ACTIVATION ( STATUS = ON, PROCEDURE_NAME = [BlockedProcessActivationProcedure], MAX_QUEUE_READERS = 1, EXECUTE AS OWNER ) ### Questions 1. What is the cause queue not being populated? 2. Is there better method of altering on blocking than with queues and event notifications? ----- _**Reopen Request**_ _While the Question and Answer that are linked in the **duplicate** close reason probably do answer the second question, they do not answer the first question: **What is the cause of the above alerting not working?**_.

Tables: Users User_profile -------- ------------- user_id(PK) user_profile_id(PK) username user_id(FK) password display_name date_created birth_date last_login gender Story story_chapter chapters ------ ----------------- ------------- story_id(PK) story_chapter_id(PK) chapter_id(PK) image_id(FK) story_id(FK) chapter_title story_title chapter_id(FK) chapter_content synopsis date_added last_updated story_genre story_tag story_content_warning ----------- --------- ---------------------- story_genre_id(PK) story_tag_id(PK) story_content_warning_id(PK) story_id(FK) story_id(FK) story_id(FK) genre_id(FK) tag_id(FK) content_warning_id(FK) genre tags content_warning ------- ---------- ---------------- genre_id(PK) tag_id(PK) content_warning_id(PK) genre_name tag_name content_warning Published_story ----------------- Profile_image image_type published_id(PK) -------------- ------------ story_id(FK) profile_image_id(PK) image_type_id(PK) user_id(FK) image_type_id(FK) type_name published_date file_name last_updated file_path Bookmark Follow_author ---------- -------------- bookmark_id(PK) Follow_id(PK) published_id(FK) published_id(FK)//get the user_id in published table user_id(FK) user_id(FK)//user_id of the user who followed bookmark_date follow_date review review_type chapter_review/rate ------------ ----------- -------------------- review_id(PK) review_type_id(PK) chapter_rate_id(PK) published_id(FK) type_name chapter_id(FK) review_type_id(FK) user_id(FK) rating rating content/summary rate_date review_date image_type table is nothing special. The row type_name refers to what page this image is used for since each page has different display dimension. For example: 'story' type name is used to display story image cover(200x200). 'user' is used for user profile(100x100). I used image_lib in codeigniter for this. $this->image_lib->resize(); So in my folder where i upload images uploaded. I store 3 types, the original,200x200 version and 100x100 version. **User can:** - browse story - publish story - review story/author/chapter(other users who have published their story) - bookmark story - follow author - get notification Let me elaborate on the notification part. **Users:**
receives notification if a new chapter is added to a story(bookmarked)
receives notification if a new story is published by the author(followed)
receives notification changes made to story/chapter(edit)
**Authors:**
receives a notification if a user/reader reviews story and rates chapter.
receives a notification if a user/reader followed author or bookmarked story.
receives a notification if a user/reader reviews author(profile).
Based on the above specifications, is my database design good enough? Also on notification. I understand that i need to make a log/events table that will record every action. For example, if a user published a story then i'll have to insert it into log/events table. Problem is, idk how to design the event table. There's different kind of events(publish,rate,review etc.). So i need a 'type' column to distinguish. But where do i put the id foreign key to point to the actual record of event. Say published_id(FK) for a publish event. or review_id(FK) for a review event. It would be wrong to just put one column as action_id(FK) but point it to multiple tables(published_story,review,bookmark etc.) I also read online that its bad to have multiple foreign key columns that are null. Since for 1 row i only need 1 foreign key to point which table so the rest of the columns will be null. Event ---------- event_id(PK) publish_id(FK) NULL bookmark_id(FK) NULL review_id(FK) NULL Follow_id(FK) NULL event_date Update: I really have no idea how to design my notification system. If i do not want the above table with multiple null foreign keys, i have to separate them into different tables. event_publish event_review event_follow ------------- ------------- ------------- event_publish_id(PK) event_review_id(PK) event_follow_id(PK) publish_id(FK) review_id(FK) follow_id(FK) event_bookmark event_chapter_rate --------------- ------------------- event_bookmark_id(PK) event_chapter_rate_id(PK) bookmark_id(FK) chapter_rate_id(FK) How do i incorporate the above tables to my notification system then? What i've done with a similar problem was with the profile_image table. There are 2 types of image, 1 for user and 1 for story. If i were to Foreign key the user_id and story_id in the profile_image table, then i would have to set 2 foreign keys and both being NULL. As for each row, either column would have to be null to determine which table the row belongs to. For the notification, i'm using this as the reference but i just cant wrap my head around to implementing it. I just need a simple notification. Nothing fancy. https://dba.stackexchange.com/questions/99401/understanding-a-notification-system?newreg=367be62ed3ee40339f71f4a1ddac22c1

If I send notifications before starting to listen i.e. notify test, 'test message'; listen test; I loose that notifications. How can I persist them for later fetching? Actually, I need this notifications for tables updates, so it is preferably not to start spezialized messaging system like RMQ etc.

I am using the Microsoft ODBC driver on a Linux system to connect to SQL Server 2012 on Windows to use the Query Notification feature. I run my C code and can see my subscription appear in sys.dm_qn_subscriptions as I expect, then my code sends a WAITFOR and blocks - fine so far. Then I do a transaction that will trigger the notification, and I get it - still fine. What I would like to do is then process the notification and loop around again to WAITFOR but after the first notification my subscription no longer appears in the DMV. My question is, is this the intended behaviour and I should re-subscribe every time, or is something weird happening?

1. I have a table where I have a column named state(int(2)) and modify_time (time_stamp on update current timestamp). 2. I have an update trigger which changes the value of state column to 0 based on some condition. 3. I want to set the value of state column to 1 after 24 hours of modify_time, if it is still 0. 4. I tried below method to test : CREATE EVENT myevent1 ON SCHEDULE AT current_timestamp + interval 1 minute DO UPDATE test.mytabletable SET state = 0; This did not doing anything. Is there any alternative method?