microsoft.public.sqlserver.notificationsvcs
[Top] [All Lists]

Re: Stuck learning SQL-NS

Subject: Re: Stuck learning SQL-NS
From: Peruukki
Date: Fri, 24 Aug 2007 03:47:58 -0700
Newsgroups: microsoft.public.sqlserver.notificationsvcs
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



On 24 Ago, 04:47, Joe Webb <[email protected]> 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.
>
> > Someone can figure out where I am wrong? BTW, have you any suggestions
> > on free materila for newbies?
>
> > Thanks
>
> > Mauro



<Prev in Thread] Current Thread [Next in Thread>