×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL Notifications for GP Tables

SQL Notifications for GP Tables

SQL Notifications for GP Tables

(OP)
Yes this is related to the error I had in thread632-1647254: Edit Operation on Table X failed.

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? smile

Thanks.

RE: SQL Notifications for GP Tables

(OP)
Here is another description of the problem from another user, that describes what is happening here: http://www.eggheadcafe.com/software/aspnet/29798612/setoption-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:

CODE

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:

CODE

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:

CODE

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:

CODE

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close