August 1, 2013 at 9:48 am
Hi
I have just downloaded the extended events gui for sql2008.
Can anybody point me to any articles on how to set this up to capture the info for sort warnings and hash warnings ?
August 1, 2013 at 3:10 pm
Thanks Steve.
I did see that first article earlier today but then decided not follow it as Mr Fritchey mentioned extended events where easier to setup (but that the article would absolutely work, because i initially asked him about that very one!)
However - i have just tried to implement its advice.
I have run the query in the lising number 6
use database1
--enable service broker --
ALTER DATABASE database1 SET ENABLE_BROKER;
--ENSURE WE CAN EXECUTE QUERIES AGAINST DMV'S --
ALTER DATABASE database1 SET TRUSTWORTHY ON;
GO
--CREATE A QUEUE TO COLLECT EVENTS --
CREATE QUEUE dbo.sortWarningsQueue
WITH STATUS=ON;
GO
--CREATE A SERVICE ON THE QUEUE THAT REFERENCES THE EVENT NOTIFICATIONS CONTRACT --
CREATE SERVICE SortWarningsService
onqueue dbo.sortwarningsqueue
(
[http://schemas.microsoft.com/sql/notifications/PostEventNotification]
);
go
-- CREATE THE DATABASE EVENT NOTIFICATION
CREATE EVENT NOTIFICATION NotifySort_Warnings
on server
FOR SORT_WARNINGS
TO SERVICE 'SortWarningsService','current database';
GO
and i get the following error:
Msg 15151, Level 16, State 1, Line 1
Cannot find the contract 'http://schemas.microsoft.com/sql/notifications/PostEventNotification', because it does not exist or you do not have permission.
I have checked the database settings and it is now enabled service broker. Can this error be ignored or how can i fix it ?
Thanks
August 1, 2013 at 3:49 pm
I'm not an expert in this area at all. Grant is much better.
That error looks like either a contract isn't set up properly, or it's trying to make a call out to microsoft.com to verify some schema in a DOM.
August 2, 2013 at 1:26 pm
Can anybody explain how I set up a 'contract' if I have not already done so? Is there anything I can do to check the settings of this contract' ?
August 7, 2013 at 8:51 am
If anybody is having the same issue as me regarding the 'service contract' error from the above script - i think i have fixed it. Well it now runs on my machine at least. I altered the code slightly to look like this.....
--CREATE A SERVICE ON THE QUEUE THAT REFERENCES THE EVENT NOTIFICATIONS CONTRACT --
USE ServerName
CREATE SERVICE [//ServerName/SortWarningsService]
onqueue dbo.sortWarningsQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
go
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply