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

Re: Odd errors with new Subscription Class

Subject: Re: Odd errors with new Subscription Class
From:
Date: 23 Jan 2006 10:36:40 -0800
Newsgroups: microsoft.public.sqlserver.notificationsvcs
Ah - so obvious.  Should have see that one.

Which do you guys think would be more friendly as far as tuning &
performance goes- BETWEEN or <?  ....we'll have possibly 50-100 events
firing each day, but possibly 100,000 subscribers.


Joe Webb wrote:
> Vince is right. Here's a sample <Action> element from BOL 2005. Notice
> the use of the &gt; in the WHERE clause.
>
>
>
> <Action>
> -- Insert New Stock Symbols with Prices
> INSERT dbo.StockEventsChron (StockSymbol, StockPrice)
> SELECT e.StockSymbol, e.StockPrice
>     FROM dbo.StockEvents AS e
>     WHERE e.StockSymbol
>         NOT IN (SELECT StockSymbol FROM dbo.StockEventsChron);
> -- Update Existing Stock Symbols with New Prices
> UPDATE dbo.StockEventsChron
>     SET StockPrice = e.StockPrice
>     FROM dbo.StockEvents AS e
>     JOIN dbo.StockEventsChron AS c
>         ON e.StockSymbol = c.StockSymbol
>         WHERE e.StockPrice &gt; c.StockPrice;
> </Action>
>
>
> HTH...
>
>
> --
> Joe Webb
> SQL Server MVP
> http://www.sqlns.com
>
>
> ~~~
> Get up to speed quickly with SQLNS
> http://www.amazon.com/exec/obidos/tg/detail/-/0972688811
>
> I support PASS, the Professional Association for SQL Server.
> (www.sqlpass.org)
>
>
> On Mon, 23 Jan 2006 09:41:07 -0800, "Vince Sefcik"
> <[email protected]> wrote:
>
> >It's probably complaining about the "<" because nscontrol considers "<" to
> >be an XML delimiter.  Change the "<" to "&lt;" (without the quotation marks.
> >Ie.g., change:
> >
> >WHERE udf_Haversine(e.latitude,e.longitude,l.latitude,l.longitude) <
> >l.radius
> >
> >to
> >
> >WHERE udf_Haversine(e.latitude,e.longitude,l.latitude,l.longitude) &lt;
> >l.radius
> >
> ><[email protected]> wrote in message
> >news:[email protected]
> >>I got around the error by using the BETWEEN operator instead of < or >.
> >> Does anyone know why I can't use these operators?
> >>
> >>
> >> [email protected] wrote:
> >>> I am attempting to modify my existing Event Class, & add a subscription
> >>> class.  The basic summary is to send notifications to subscribers when
> >>> a certain event happens within a user-specified radius of their
> >>> subscribed address.  So a user subscribes by inputting their address &
> >>> desired radius - my subscription management interface geocodes it
> >>> (fetches a latitude & longitude).  I've already got lat/long for my
> >>> events.  Then I have a udf (udf_haversine) to calculate distance
> >>> between 2 lat/long points.  See the Action SQL below that I thought
> >>> would work.  It compares the subscriber's lat/long to the event's
> >>> lat/long and I thought would send notifications whenever the specified
> >>> event happened within <radius> distance of the subscriber's location.
> >>> But when I proceed to update my NS app, it does not like my WHERE
> >>> clause for some reason.  I think it does not like the "<" operator.
> >>> But I need this operator. I receive the following error:
> >>>
> >>> Name cannot begin with the ' ' character, hexadecimal value 0x20. Line
> >>> 155, position 76. (System.Xml)
> >>>
> >>> See my ADF XML below.  I thought for sure this would work.  Any
> >>> ideas???
> >>>
> >>>
> >>> <SubscriptionClass>
> >>> <SubscriptionClassName>LocationChange</SubscriptionClassName>
> >>> <Schema>
> >>> <Field>
> >>> <FieldName>DeviceName</FieldName>
> >>> <FieldType>nvarchar(255)</FieldType>
> >>> <FieldTypeMods>not null</FieldTypeMods>
> >>> </Field>
> >>> <Field>
> >>> <FieldName>SubscriberLocale</FieldName>
> >>> <FieldType>nvarchar(10)</FieldType>
> >>> <FieldTypeMods>not null</FieldTypeMods>
> >>> </Field>
> >>> <Field>
> >>> <FieldName>Latitude</FieldName>
> >>> <FieldType>decimal(18,9)</FieldType>
> >>> <FieldTypeMods>not null</FieldTypeMods>
> >>> </Field>
> >>> <Field>
> >>> <FieldName>Longitude</FieldName>
> >>> <FieldType>decimal(18,9)</FieldType>
> >>> <FieldTypeMods>not null</FieldTypeMods>
> >>> </Field>
> >>> <Field>
> >>> <FieldName>Radius</FieldName>
> >>> <FieldType>int</FieldType>
> >>> <FieldTypeMods>not null</FieldTypeMods>
> >>> </Field>
> >>> </Schema>
> >>> <EventRules>
> >>> <EventRule>
> >>> <RuleName>LocationChangeRule</RuleName>
> >>> <Action>
> >>> INSERT INTO LocationChangeEventAlert(SubscriberId,
> >>>   DeviceName, SubscriberLocale, RegistrationNumber, Fullname, Date,
> >>>
> >>>                     AddressLine1, AddressLine2, City, State, Zip)
> >>> SELECT l.SubscriberId, l.DeviceName, l.SubscriberLocale,
> >>>   e.RegistrationNumber, e.FullName, e.Date, e.AddressLine1,
> >>> e.AddressLine2,
> >>>   e.City, e.State, e.Zip
> >>> FROM ChangeofAddress e, LocationChange l
> >>> WHERE udf_Haversine(e.latitude,e.longitude,l.latitude,l.longitude) <
> >>> l.radius;
> >>> </Action>
> >>> <EventClassName>LocationChangeEvent</EventClassName>
> >>> </EventRule>
> >>> </EventRules>
> >>> </SubscriptionClass>
> >> 
> >


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