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

Re: Odd errors with new Subscription Class

Subject: Re: Odd errors with new Subscription Class
From: Joe Webb
Date: Mon, 23 Jan 2006 12:14:45 -0600
Newsgroups: microsoft.public.sqlserver.notificationsvcs
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>