| Groups | Blog | Home |
sql server notification services : Stuck learning SQL-NS
a look at the example given in msdn2 and in the Microsoft book "Microsoft SQL 2005 NS" but I got problems to understand errors given in Event Viewer. After one day I was able to let the istance respond to the event, but now no notification is produced; in Event Viewer, I got this error message Event Type: Error Event Source: NotificationServices Event Category: None Event ID: 4045 Date: 23/08/2007 Time: 14.47.27 User: N/A Computer: TO2VMS01 Description: Description: The running of the rule failed. Please check the rule. EventParameters: Application Name: SongAlerts Quantum ID: 107 Rule Firing ID: 8 Rule Name: MatchNewSongsByArtist Notification Throttle: 1000 Event Class ID: 1 Subscription Class ID: 1 Rule ID: 2 Rule Action: SELECT dbo.NewSongNotify( subscriptions.SubscriberId, N'DefaultDevice', N'en-US', songs.SongTitle, songs.ArtistName, songs.AlbumTitle, songs.GenreName) FROM SongAdded events JOIN MusicStore..SongDetails songs ON events.SongId = songs.SongId JOIN NewSongByArtist subscriptions ON subscriptions.ArtistName = songs.ArtistName Stored Procedure Name: [dbo].[NSFire2] SqlServerError: Source: .Net SqlClient Data Provider Number: 4121 State: 1 Class: 16 Server: TO2VMS01 Message: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.NewSongNotify", or the name is ambiguous. Procedure: NSFireDeveloperDefined2 Line Number: 6 Description: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.NewSongNotify", or the name is ambiguous. InstanceName: MusicStore ApplicationName: SongAlerts Component: Generator Thread: 4 For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. In fact I have got no column called dbo.NewSongNotify in the views but I don't know if it is possible that this table can be created at run- time. Someone can figure out where I am wrong? BTW, have you any suggestions on free materila for newbies? Thanks Mauro
Hi Mauro -
Are you using SSNS 2000 or SSNS 2005. The match rule that you included as part of your post was for SSNS 2000. Although the basic concepts, terminology, and premise of SSNS 2005 is the the same as the prior version, there are number of improvements that prevent it from working exactly as in SSNS 2000. I talk about some of the differences in these articles: http://www.simple-talk.com/sql/sql-server-2005/building-effective-sql-server-notification-applications-part-i/ http://www.simple-talk.com/sql/sql-server-2005/building-effective-sql-server-notification-applications-part-2/ http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-0074-46c4-bd4f-c3eb2abf4b66&DisplayLang=en As for resources, I used to have quite a few resources, examples, and tutorials on my blog site, but unfortunately I lost the past 3 years worth of postings recently. Arrgh!!!! Anyway, I've just start blogging again. I moved to SQLTeam - http://weblogs.sqlteam.com/joew/ HTH... -- Joe Webb SQL Server MVP http://weblogs.sqlteam.com/joew [quoted text, click to view] On Thu, 23 Aug 2007 13:22:05 +0000, Peruukki wrote:
> Hi guys, I am completely new to Notification Services and I was having > a look at the example given in msdn2 and in the Microsoft book > "Microsoft SQL 2005 NS" but I got problems to understand errors given > in Event Viewer. > After one day I was able to let the istance respond to the event, but > now no notification is produced; in Event Viewer, I got this error > message > > Event Type: Error > Event Source: NotificationServices > Event Category: None > Event ID: 4045 > Date: 23/08/2007 > Time: 14.47.27 > User: N/A > Computer: TO2VMS01 > Description: > Description: The running of the rule failed. Please check the rule. > EventParameters: > Application Name: SongAlerts > Quantum ID: 107 > Rule Firing ID: 8 > Rule Name: MatchNewSongsByArtist > Notification Throttle: 1000 > Event Class ID: 1 > Subscription Class ID: 1 > Rule ID: 2 > Rule Action: SELECT dbo.NewSongNotify( > subscriptions.SubscriberId, > N'DefaultDevice', > N'en-US', > songs.SongTitle, > songs.ArtistName, > songs.AlbumTitle, > songs.GenreName) > FROM SongAdded events > JOIN MusicStore..SongDetails songs ON > events.SongId = songs.SongId > JOIN NewSongByArtist subscriptions ON > subscriptions.ArtistName = songs.ArtistName > Stored Procedure Name: [dbo].[NSFire2] > > SqlServerError: > Source: .Net SqlClient Data Provider > Number: 4121 > State: 1 > Class: 16 > Server: TO2VMS01 > Message: Cannot find either column "dbo" or the user-defined > function or aggregate "dbo.NewSongNotify", or the name is ambiguous. > Procedure: NSFireDeveloperDefined2 > Line Number: 6 > > Description: Cannot find either column "dbo" or the user-defined > function or aggregate "dbo.NewSongNotify", or the name is ambiguous. > InstanceName: MusicStore > ApplicationName: SongAlerts > Component: Generator > Thread: 4 > > > For more information, see Help and Support Center at > http://go.microsoft.com/fwlink/events.asp. > > > In fact I have got no column called dbo.NewSongNotify in the views but > I don't know if it is possible that this table can be created at run- > time. > > Someone can figure out where I am wrong? BTW, have you any suggestions > on free materila for newbies? > > Thanks >
Hi Joe,
I am using SSNS 2005, but I was trying to merge information from different sources. Now I'll have a look at the resources you posted. By the way, I am quite confused by the double chance XML and NMO. Which are the differences? Which one is "better"? In order to learn the second technique too, I was trying to code the same instance but I got this issue with the generator (?). When I drop the xml file in the Events directory, the file is correctly processed (it becomes a .DONE file), but then the information in the file doesn't appear to be loaded (I cannot see anything in the view) The EventProvider is written as HostedEventProvider musicEventProvider = new HostedEventProvider(musicNmoApplication, "MusicEP"); musicEventProvider.ClassName = "FileSystemWatcherProvider"; musicEventProvider.SystemName = notificationServicesHost; AddHostedEventProviderArgument(ref musicEventProvider, "WatchDirectory", sampleDirectory + @"\SongAlerts\Events"); AddHostedEventProviderArgument(ref musicEventProvider, "SchemaFile", sampleDirectory + @"\SongAlerts\SongAddedEventSchema.xsd"); AddHostedEventProviderArgument(ref musicEventProvider, "EventClassName", "MusicEvents"); musicNmoApplication.HostedEventProviders.Add(musicEventProvider); and the eventClass EventClass musicEvents = new EventClass(musicNmoApplication, "MusicEvents"); AddEventField(ref musicEvents, "SongId", "int"); musicEvents.IndexSqlStatements.Add("CREATE INDEX MusicEventsIndex ON MusicEvents ( SongId )"); musicNmoApplication.EventClasses.Add(musicEvents); Console.WriteLine("EventClasses Added"); the xml file is defined as <?xml version="1.0" encoding="utf-16" ?> - <xsd:schema xmlns:sql="urn:schemas-microsoft-com:mapping-schema" xmlns:b="http://schemas.microsoft.com/BizTalk/2003" xmlns:xsd="http:// www.w3.org/2001/XMLSchema"> - <xsd:element sql:relation="SongAdded" name="SongAdded"> - <xsd:complexType> - <xsd:sequence> <xsd:element name="SongId" type="xsd:integer" /> </xsd:sequence> </xsd:complexType> </xsd:element> </xsd:schema> I just post the rule too SubscriptionEventRule musicSubscriptionEventRule = new SubscriptionEventRule(musicSubscriptions, "MusicSubscriptionsEventRule"); musicSubscriptionEventRule.Action = @"INSERT INTO MusicNotifications (SubscriberId, N'My Device', N'en-us', SongTitle, ArtistName, AlbumTitle, GenreName) SELECT MS.SubscriberId, songs.SongTitle, songs.ArtistName, songs.AlbumTitle, songs.GenreName FROM MusicNmoInstanceMusicNmo..MusicEvents ME, MusicNmoInstanceMusicNmo..MusicSubscriptions MS, MusicStore..SongDetails songs WHERE songs.ArtistName = MS.ArtistName AND songs.SongId = ME.SongId"; musicSubscriptionEventRule.EventClassName = "MusicEvents"; musicSubscriptions.SubscriptionEventRules.Add(musicSubscriptionEventRule); musicNmoApplication.SubscriptionClasses.Add(musicSubscriptions); Hope you'll find some clues to help me!!! Thanks, Mauro [quoted text, click to view] On 24 Ago, 04:47, Joe Webb <j...@webbtechsolutions.com> wrote:
> Hi Mauro - > > Are you using SSNS 2000 or SSNS 2005. The match rule that you included as > part of your post was for SSNS 2000. Although the basic concepts, > terminology, and premise of SSNS 2005 is the the same as the prior > version, there are number of improvements that prevent it from working > exactly as in SSNS 2000. > > I talk about some of the differences in these articles: > > http://www.simple-talk.com/sql/sql-server-2005/building-effective-sql... > > http://www.simple-talk.com/sql/sql-server-2005/building-effective-sql... > > http://www.microsoft.com/downloads/details.aspx?FamilyID=3d5e96d9-007... > > As for resources, I used to have quite a few resources, examples, and > tutorials on my blog site, but unfortunately I lost the past 3 years worth > of postings recently. Arrgh!!!! > > Anyway, I've just start blogging again. I moved to SQLTeam -http://weblogs.sqlteam.com/joew/ > > HTH... > > -- > Joe Webb > SQL Server MVPhttp://weblogs.sqlteam.com/joew > > On Thu, 23 Aug 2007 13:22:05 +0000, Peruukki wrote: > > Hi guys, I am completely new to Notification Services and I was having > > a look at the example given in msdn2 and in the Microsoft book > > "Microsoft SQL 2005 NS" but I got problems to understand errors given > > in Event Viewer. > > After one day I was able to let the istance respond to the event, but > > now no notification is produced; in Event Viewer, I got this error > > message > > > Event Type: Error > > Event Source: NotificationServices > > Event Category: None > > Event ID: 4045 > > Date: 23/08/2007 > > Time: 14.47.27 > > User: N/A > > Computer: TO2VMS01 > > Description: > > Description: The running of the rule failed. Please check the rule. > > EventParameters: > > Application Name: SongAlerts > > Quantum ID: 107 > > Rule Firing ID: 8 > > Rule Name: MatchNewSongsByArtist > > Notification Throttle: 1000 > > Event Class ID: 1 > > Subscription Class ID: 1 > > Rule ID: 2 > > Rule Action: SELECT dbo.NewSongNotify( > > subscriptions.SubscriberId, > > N'DefaultDevice', > > N'en-US', > > songs.SongTitle, > > songs.ArtistName, > > songs.AlbumTitle, > > songs.GenreName) > > FROM SongAdded events > > JOIN MusicStore..SongDetails songs ON > > events.SongId = songs.SongId > > JOIN NewSongByArtist subscriptions ON > > subscriptions.ArtistName = songs.ArtistName > > Stored Procedure Name: [dbo].[NSFire2] > > > SqlServerError: > > Source: .Net SqlClient Data Provider > > Number: 4121 > > State: 1 > > Class: 16 > > Server: TO2VMS01 > > Message: Cannot find either column "dbo" or the user-defined > > function or aggregate "dbo.NewSongNotify", or the name is ambiguous. > > Procedure: NSFireDeveloperDefined2 > > Line Number: 6 > > > Description: Cannot find either column "dbo" or the user-defined > > function or aggregate "dbo.NewSongNotify", or the name is ambiguous. > > InstanceName: MusicStore > > ApplicationName: SongAlerts > > Component: Generator > > Thread: 4 > > > For more information, see Help and Support Center at > >http://go.microsoft.com/fwlink/events.asp. > > > In fact I have got no column called dbo.NewSongNotify in the views but > > I don't know if it is possible that this table can be created at run- > > time. >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |