I'm a developer for a rich client application with a primary grid that should be refreshed when data is changed by other users. I loath to resort to some sort of polling. It would be really cool if there is a native way to raise an event on the client from a SQL Server trigger. RAISEERROR can send a message to the Connection's InfoMessage() event. But of course this will only send the message back to the user who made the change. Is there anyway in SQL Server to raise an error message on another process so that this ADO event can pick it up?
This is not possible without some elaborate engineering in SQL Server 2005 (even more so in SQL Server 2000). With SQL Server 2005, you can use service broker to do this. It is hard to tell without knowing more details but I suggest that you take a look at the new features in SQL Server 2005 and see if those address your needs.|||You could always use firebird/interbase. probably on of the best rdbms's around.
Other that that (hypotheticals follow)
in SQL 2000 ->
You could put a com+ object in DTS. Trigger on a data-change notifies the com+ object. COM+ object has list of registered clients that it notifies of the changes.
SQL 2005 it would be even easier with the integration of .NET
wouldn't that work?
Oh dont make me do this in VBSUX because VB, well vb simply sux - how did it become so successful?! I dont want to do it in VB.NET as I want to cater the lowest common denominator. And boy is VB the lowest denominator!
Simple to do in delphi! but give me an hour. . . I'll do it in VB
God delphi rox!|||Thanks for your help. I'm not suprised that there isn't anything. However I'm disapointed SQL Server 2005 doesn't have it. SQL Server has all the capabilities as a simply event conduit between it's clients. Kind of a waste then that some other messaging service is required for simple data related events to be passed around.
For an alternative I have an idea to referesh based on user activty. A few events will call a function and refresh if some criteria has been met. Eg the form is activated after five minutes.
|||
well trying to do it using vbsux further ingrained my disdain for the piece of sh|t that vb6 is. Its not hard to do in delphi. It is Actually rather simple.
About 20 lines of actual code you need to write. . .
Create an activeX exe (DBNotifier) that defines an object (NotifyClient) that implements the interface
INotifyClient
{
Notify()
}
with an event OnNotify
another singleton object (Manager) implements the interface -
IManager
{
void RegisterClient(INotifyClient client);
void UnRegisterClient(INotifyClient client);
void NotifyClients()
}
During the call to NotifyClients you need to synchronize access to a collection it contains that will hold references to the clients.
Finally an object that (NotifyAgent) implements
INotifyAgent
{
void Notify(long ID)
}
NotifyAgent calls the Managers.NotifyClients Method
In the GUI app, get a reference to the IManager Object, Instance An INotifyClient and register it. Define an OnNotifyEvent to do what ever you want.
Now in the database (I will use northwind for example) attach a trigger of this sort:
=============================
create trigger trig_employeesDataChange on northwind.dbo.employees for DELETE,INSERT,UPDATE
as
DECLARE @.object int
DECLARE @.hr int
DECLARE @.obj_ID int
DECLARE @.src varchar(255), @.desc varchar(255)
select @.obj_ID = ID from sysobjects o inner join
sysusers u on o.uid = u.uid
where
u.name ='dbo' and
o.name ='employees'
EXEC @.hr = sp_OACreate 'DBNotifier.Agent', @.object OUT
IF @.hr = 0
EXEC @.hr = sp_OAMethod @.object, 'Notify', NULL, @.obj_id
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
=============================
Again. . . dont try in VBSUX, its not worth the effort use a real development environment. Why you might ask? The synchronization is impossible. VB is just a load of cr@.p (have they yet shot the guys who developed it? shoot their mothers too!!!)
Now. . . why is this not implemented natively? Well, because you aren't supposed to remain connected to the database. Its bad design!
Connect, get your data and get out!!!!
Connect, change your data and get out!!!!
|||You can do it using service broker and query notifications mechanisms in SQL Server 2005. It depends on your requirements. As I said, you should check out those features in Books Online.|||
Boy, I could complicate a wet dream!
Its not difficult at all, provided you use Delphi as you can't do it with VBSUX alone because VBSUX is a piece of cr@.p and cant create COM+ event objects. . . Have those guys been shot yet?
Total time of implementation 10 minutes depending on how many SQL objects you want sending notifications!!!
Three parts. . .
● Build, register and install a COM+ Event Object - SQLEvents (this is not hard but you cannot do it in VBSUX!!! Use Delphi!)
● Add triggers to your SQL objects that should initiate the SQLEvents
● Create an ActiveX Event Sink for handling the COM+ Events - SQLEventSink (This can be done in VBSUX!!!)
If you don't have delphi, go home - you suck!
[Part One - Looks like alot, but it takes all of 3 minutes!!!]
1. create a Delphi Active X library, Call it SQLEvents.
2. From the file menu - > Add >Other -> ActiveX -> Automation Object call it SQLEvent with Apartment Threading (no events)
3. From view Menu -> Type Library. . . In the the TypeLibrary editor, add a method to the ISQLEvent interface called Notify that takes a long parameter called SQLObject [see here]
4. Generate the code and call the generated pas file SQLEvents_impl.pas; you dont need to write ANY code!!!!!
5. From the Run Menu -> Register Active X Server (this also builds the DLL)
On the SQL Server machine:
6. Open Component Services and drill down to COM+ Applications. Right Click and select New -> Application - Next -> Empty Application Call it 'SQLEvents' and set Server Application as your activation type -> Applciation Identity Interactive User -> use the default application roles -> dont add any roles -> finish
7. Expand the SQLEvents Aplication folder to the Components folder and right click and select New -> Component -> Next -> Click Install New Event Class(es) and locate and open the SQLEvents.dll you built in step 5. -Next -> Finish
The event object is done. and the tree should look like this when expanded.
[Part 2]
8. Add triggers to the SQL Objects that need to send notifications -
NOTES: My build has a CLSID of "{8FD50E86-203D-4939-9CAE-0F2865C69465}" yours will be different! You can find it out by right clicking the Component installed in step 7 and selecting properties.
Also. this example uses northwind and will trigger events on Update Delete and Insert on the employees table:
=====================================================
CREATE trigger trig_employeesDataChange on northwind.dbo.employees for DELETE,INSERT,UPDATE
as
DECLARE @.object int
DECLARE @.hr int
DECLARE @.obj_ID int
DECLARE @.src varchar(255), @.desc varchar(255)
select @.obj_ID = ID from sysobjects o inner join
sysusers u on o.uid = u.uid
where
u.name ='dbo' and
o.name ='employees'
EXEC @.hr = sp_OACreate '{8FD50E86-203D-4939-9CAE-0F2865C69465}', @.object OUT
IF @.hr = 0
EXEC @.hr = sp_OAMethod @.object, 'Notify', NULL, @.obj_id
IF @.hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @.object, @.src OUT, @.desc OUT
SELECT hr=convert(varbinary(4),@.hr), Source=@.src, Description=@.desc
RETURN
END
=====================================================
Part 3 [you can use VB here, total time 2 minutes!!!]
9. Create a new VB ActiveX DLL Project, save it as SQLEventsSink, Rename Class1 to SQLEventsSink and save the file as SQLEventsSink.cls.
10. From the Projects menu Add a reference to the SQLEvents Lbrary you built in Step 5 above.
11. Add this code to SQLEventsSink:
=================================
Option Explicit
Implements SQLEvents.SQLEvent
Private mSQLObjectID As Long 'local copy
Public Event OnNotify()
Public Property Let SQLObjectID(ByVal vData As Long)
mSQLObjectID = vData
End Property
Public Property Get SQLObjectID() As Long
SQLObjectID = mSQLObjectID
End Property
Private Sub SQLEvent_Notify(ByVal SQLObjectID As Long)
If SQLObjectID = Me.SQLObjectID Then RaiseEvent OnNotify
End Sub
=================================
12. Build the Library and you are done. . . you have an event system!!!
Here's how to use ->
1. Create A New VB Application Project Call it TestApp
2. From the Project Menu, add references to MS Active Data Object 2.6 (minimum), COM+ Admin Library and the SQLEventSink library you built in step 12 above. In the Toolbox add the MS DataGrid 6.0
3. Add a Module, name it globals and add the following code
==============================================
Option Explicit
Public Const CONNECTIONSTRING = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=Northwind;Data Source=.\SQL2000"
' NOTE: CHANGE THE FOLLOWING TO REFLECT THE CLSID
' OF THE SQLEvents.SQLEvent OBJECT YOU PREVIOUSLY
' BUILT AND INSTALLED IN COM+
Public Const CLSID_SQLEVENT = "{8FD50E86-203D-4939-9CAE-0F2865C69465}"
==============================================
4. Add a Module, name it ComUtils and add the following code (funny, in delphi it only takes about 6 lines of code to accomplish the same!!! Have I mentioned that VBSUX, sucks?!?)
==============================================
' This method creates a Transient Subscription to a COM+ Component
' Refer to The Windows Platform SDK and Particularly ICOMAdminCatalog
' clsID is the COM+ Event Component to which you are subscribing
' objref is the subscriber
' hostname is the machine on which the COM+ Event Component is registered
' If empty, connection stays on local machine
Public Function CreateTransientSubscription( _
ByVal clsid As String, _
ByVal objref As Object, _
Optional ByVal hostName As String = "") As String
Dim oCOMAdminCatalog As COMAdmin.COMAdminCatalog
Dim oTSCol As COMAdminCatalogCollection
Dim oSubscription As ICatalogObject
Dim objvar As Variant
On Error GoTo CreateTransientSubscriptionError
Set oCOMAdminCatalog = CreateObject("COMAdmin.COMAdminCatalog")
'Connect to the
If hostName <> "" Then oCOMAdminCatalog.Connect hostName
'Gets the TransientSubscriptions collection
Set oTSCol = oCOMAdminCatalog.GetCollection( _
"TransientSubscriptions")
Set oSubscription = oTSCol.Add
Set objvar = objref
oSubscription.Value("SubscriberInterface") = objref
oSubscription.Value("EventCLSID") = clsid
oSubscription.Value("Name") = "TransientSubscription"
oTSCol.SaveChanges
CreateTransientSubscription = oSubscription.Value("ID")
Set oSubscription = Nothing
Set oTSCol = Nothing
Set oCOMAdminCatalog = Nothing
Set objvar = Nothing
Exit Function
CreateTransientSubscriptionError:
CreateTransientSubscription = ""
Err.Raise Err.Number, "[CreateTransientSubscription]" & _
Err.Source, Err.Description
End Function
==============================================
5. Drop a DataGrid control on your form leaving the properties as their defaults. Add a Timer to the form as you need it because VBSUX does not natively support multi-threading. . . Have I told you how much VBSUX sucks? It really does! I wouldn't lie to you!
6. In the Form1 code add the following:
=========================================
Option Explicit
Private WithEvents mSink As SQLEventSink
Private Sub Form_Load()
Set mSink = New SQLEventSink
CreateTransientSubscription _
"{8FD50E86-203D-4939-9CAE-0F2865C69465}", _
mSink
LoadGrid
End Sub
Private Sub mSink_OnNotify()
Timer1.Enabled = True
End Sub
Private Sub Timer1_Timer()
Timer1.Enabled = False
LoadGrid
End Sub
Private Sub LoadGrid()
Dim con As Connection
Set con = New ADODB.Connection
con.Open CONNECTIONSTRING
If DataGrid1.DataSource Is Nothing Then
With con.Execute("SELECT ID FROM SYSOBJECTS O " & _
" INNER JOIN SYSUSERS U ON O.UID = U.UID " & _
" WHERE U.NAME = 'DBO' and O.NAME = 'EMPLOYEES'")
mSink.SQLObjectID = .Fields(0)
.Close
End With
End If
Dim rst As Recordset
Set rst = New Recordset
rst.CursorLocation = adUseClient
Set rst.ActiveConnection = con
rst.Open "SELECT * FROM EMPLOYEES"
Set rst.ActiveConnection = Nothing
con.Close
Set DataGrid1.DataSource = rst
Set con = Nothing
Set rst = Nothing
End Sub
=========================================
7. Run the app -
if your Northwind has not been changed, the name for employeeID 7 should be: King, Robert.
In SQL Query Analyzer, execute:
update employees set FirstName = 'Stephen' where EmployeeID = 7
and "voila!!!" Stephen King automatically appears in your datagrid!!!
References:
ICOMAdminCatalog
Registering a Transient Subscription
Complete Source Code can be found here. Zip also contains a compiled SQLEvents.dll,, just in case you don't have delphi and are still hanging around!
|||1. Don't implement the sink in VBSUX as the VBSUX com object is unstable.
No problems with a sink implemented in delphi. Have I told you that VBSUX is a total piece of CR@.P?
2. You need to enter a critical immediately upon entering the handler. After entering the critical section, null the Sink reference. Reinitialize the sink right after make changes inside the thread that does the response to the notification. So Don't Implement a Sink Client in VBSUX because Critical sections in VBSUX are a total pain in the @.SS - and threads in VBSUX are even worse!!! Have I told you that VBSUX is a PIECE OF CR@.P?
3. Best perfromance is not kicking the COM+ event off in the sql server but in the applciation that makes the change to the database. Immediately after making a change you want to publish, instance a COM+ Event and call Notify.
On a 2.6 P4 H/T w 775mb mem, I had 22 publishers notifying 30 subscribers. . .slow but no blow-ups.
