SQL Notifications for GP Tables

[Borvik]

Yes this is related to the error I had in thread632-1647254.

I'm attempting to write a dashboard application, that gives some statistics for the date/month including: today's count of new orders, order's shipped, top sales rep's for the day, and number of new accounts this month.

Right now I have the application polling the database on a timer, though as you can imagine the network traffic this causes is significant.

I found that SQL Server 2005 (which we are using) had a feature that I could subscript to change notifications use SqlDependency. I played around on a test database to see how that worked, and it worked pretty good, so I changed one of the queries pulling data for the dashboard to use SqlDependency. My dashboard didn't crash, but I did start getting reports of the error I mentioned in the other thread.

For the programmers and db admins that may know more about this than I do: Are the "SET" option requirements for a GP database just not compatible with using SqlDependency? Or is there a way I can have my cake and it it too?

Thanks.

 

[Borvik]

Here is another description of the problem from another user, that describes what is happening here:

http://www.eggheadcafe.com/software...tion-error-when-sqldependency-is-active.aspx.

I think I found a way around this, though it is quite a hobble.

I setup a table in my company database:

CREATE TABLE [dbo].[_WatchTable](
	[tablename] [nvarchar](50) NOT NULL,
	[actiontaken] [char](1) NOT NULL,
	[lastrun] [datetime] NOT NULL,
 CONSTRAINT [PK__WatchTable] PRIMARY KEY CLUSTERED 
(
	[tablename] ASC,
	[actiontaken] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Then a stored procedure to handle Upserts to this table:

CREATE PROCEDURE [dbo].[watch_changeupsert]
	-- Add the parameters for the stored procedure here
	@TableName as nvarchar(50),
	@Action as char(1)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	begin tran
	--updlock,serializable
	if exists (select * from _WatchTable with (nolock) where tablename = @TableName AND actiontaken = @Action)
		begin
			update _WatchTable set lastrun = GETDATE() where tablename = @TableName AND actiontaken = @Action
		end
	else
		begin
   			insert into _WatchTable(tablename, actiontaken, lastrun)
      			values(@TableName, @Action, GETDATE())
		end
	commit tran
END

Then a trigger on the table I wish to watch:

CREATE TRIGGER [dbo].[watch_OrderChange] 
   ON  [dbo].[SOP10100] 
   AFTER INSERT, UPDATE, DELETE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here
    DECLARE @Action as char(1)
	DECLARE @Count as int
	SET @Action = 'I' -- Set Action to 'I'nsert by default.
	SELECT @Count = COUNT(*) FROM DELETED
	if @Count > 0
    	BEGIN
        	SET @Action = 'D' -- Set Action to 'D'eleted.
        	SELECT @Count = COUNT(*) FROM INSERTED
        	IF @Count > 0
            	SET @Action = 'U' -- Set Action to 'U'pdated.
    	END
    	
    exec watch_changeupsert 'SOP10100', @Action
END

Finally I setup another database, and Replication to replicate the _WatchTable. Now in my application I can set the SqlDependency to this other database, without compromising the GP stored procedures. I get my notifications, and then run the real SQL on the GP table.

The SQL to create the dependency looks something like this:

SELECT tablename, actiontaken, lastrun FROM dbo._pdpWatchTable WHERE tablename = @tableName AND actiontaken = @actionTaken

Currently that will watch for a specific table, and a specific action (INSERT - I, UPDATE - U, or DELETE - D). You could easily let off the actionTaken parameter in the query to just get ALL notification changes for a given table.

Hope that helps someone out there.