Yeah, that is an illegal datetime value.

Look at the output from this slightly modified example:

declare @statexml as xml

set @statexml = '<data>

<main>

<row number="1" modified="aoMODIFIED">

<order_id modified="False" type="String">CS0808240001</order_id>

<eta_date modified="False" type="DateTime">2008-08-25T00:00:00</eta_date>

<eta_time modified="False" type="DateTime">1900-01-01T10:00:00</eta_time>

</row>

</main>

</data>'

select

...

oops, sorry. It's missing the "END" statement at the end of the procedure.

Simon_L (8/14/2008)

---

but to describe publisher city as functionally dependant on publisher I just cant get...

if something is functionally dependant it can be uniquely determined by another attribute. But a publisher_city...

No, it's like this:

Create EVENT NOTIFICATION NotifyALTER_T1

ON DATABASE

FOR ALTER_TABLE

TO SERVICE 'NotifyService',

'current database';

Oh, I see, you are using the GUID printed in the example in BOL. Well, that won't work. You need to execute the aforementioned command and check the...

Why are you using an explicit ID for your broker_instance? You should be using 'current database' here....

Of course we use a startup procedure to define the UDT's in TempDB.

Questions:

1) Are you doing the ALTER TABLE commands on the same database? (LoggingDemo?)

2) Could you Drop and re-Create the Server Event Notification?

Thanks

GilaMonster (9/1/2008)

---

rbarryyoung (8/31/2008)

---

The large majority of the reading, writing and caching strategies for hard drives centers on the fact that sequential sector IO's are assumed to be approximately 10x faster...

Well I do not see anything wrong with the Service Broker items. Can you execute the following script and post the results:

select * from sys.service_broker_endpoints

select * from sys.event_notifications

select *...

I don't think that you need the xs:dateTime function. Try it like this:

select

nref.value('order_id[1]','varchar(50)') order_id,

nref.value('eta_date[1]','varchar(50)') eta_date_orig,

nref.value('eta_date[1])','datetime') eta_date1,

nref.value('(eta_date)[1])', 'datetime') eta_date

from

escalation_events

cross apply

statexml.nodes('/data/main/row') as...

In SSMS, go to the databases's Service Broker section and script out all of the non-system items: Message Types, Contracts, Services, Queues. Routes and Remote Service Bindings. Sorry, but...

Glad we could help.

Glad we could help, Karthik.

This was already explained in the discussion attached to this question. Please go there.