|
|
You're in the
sql server notification services
group:External DB Call to Notify function failing
sql server notification services:
I have a ScheduledRule in my ADF call an SP in another database. This SP in
turn calls the Notify function in my NS database. The call is generating a 60018 error, which indicates that the "Generator context" is not valid for the Notify call. The ADF Rule is s just a single exec to the external proc. From what I understand this is a valid design. Is there anything else I need to do to
Hi Mark,
I don't know off hand. One thought would be the cross database permissions. I also wonder if it's because the event and subscription views that are dynamically created (and later cleaned up) during the generator quantum are not accessible when called by the external sproc - the one that's calling the Notify() function. I'll see if I can set this up to test. BTW, I've got to ask...why are you implementing it this way? HTH... Joe Webb SQL Server MVP ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] > I have a ScheduledRule in my ADF call an SP in another database. This > SP in turn calls the Notify function in my NS database. The call is > generating a 60018 error, which indicates that the "Generator context" > is not valid for the Notify call. > > The ADF Rule is s just a single exec to the external proc. From what > I understand this is a valid design. Is there anything else I need to > do to allow the external SP call to the Notify function to succeed? >
Hi Mark,
I meant to ask: Have you run profiler to see what's going on there? HTH... Joe Webb SQL Server MVP ~~~ Get up to speed quickly with SQLNS http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 [quoted text, click to view] > I have a ScheduledRule in my ADF call an SP in another database. This > SP in turn calls the Notify function in my NS database. The call is > generating a 60018 error, which indicates that the "Generator context" > is not valid for the Notify call. > > The ADF Rule is s just a single exec to the external proc. From what > I understand this is a valid design. Is there anything else I need to > do to allow the external SP call to the Notify function to succeed? >
Joe, I am experiencing a similiar problem to Mark. I had the NS running just
fine and then had to run an update that changed the Event tables and rules, the XSLT, and a couple of other minor configuration settings. Now, I get the following error in the SQL logs and event logs: Error: 60018, Severity: 19, State: 50 Notification Services error 60018 Not very discriptive or helpful. I traced through all the permissions the account has, cross db chaining, and everything looked ok. Some permissions had been lost when the replacement objects due to the NSCONTROL update, I replaced those and still nothing. I traced things to the Notify function and extracting out what it executes I get the following...which gives me the error I am seeing. DECLARE @SPID INT SELECT @SPID = @@SPID EXEC @XPReturn = master.dbo.xp_NSNotify_2_0_3008_0 @SPID, 1, 1, 0, 1000, 'General Delivery - 423939', 'EmailDevice', 'en-us', 528, '1013470877', 423939, 1.00, 'Test' select @XPReturn Results: Server: Msg 60018, Level 19, State 1, Line 0 Notification Services error 60018 ----------- 1 (1 row(s) affected) Anyone have any thoughts? I have already unregistered, removed everything, re-registered, double checked the permissions, and dropped and re-created the extended procs. Totally at a loss. [quoted text, click to view] "Joe Webb" wrote:
> Hi Mark, > > I meant to ask: Have you run profiler to see what's going on there? > > > > HTH... > Joe Webb > SQL Server MVP > > ~~~ > Get up to speed quickly with SQLNS > http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > > > > > I have a ScheduledRule in my ADF call an SP in another database. This > > SP in turn calls the Notify function in my NS database. The call is > > generating a 60018 error, which indicates that the "Generator context" > > is not valid for the Notify call. > > > > The ADF Rule is s just a single exec to the external proc. From what > > I understand this is a valid design. Is there anything else I need to > > do to allow the external SP call to the Notify function to succeed? > > > > >
As additional information if it helps any.
The call works if you add one after the other parameter until NotificationThrottle!? EXEC @XPReturn = master.dbo.xp_NSNotify_2_0_3008_0 @SPID, 1, @DigestDelivery, @MaxBatchSize, @NotificationThrottle It really seems to be an issue within the execution of this xp. [quoted text, click to view] "[WRX]Raven" wrote:
> Joe, I am experiencing a similiar problem to Mark. I had the NS running just > fine and then had to run an update that changed the Event tables and rules, > the XSLT, and a couple of other minor configuration settings. > > Now, I get the following error in the SQL logs and event logs: > > Error: 60018, Severity: 19, State: 50 > Notification Services error 60018 > > Not very discriptive or helpful. I traced through all the permissions the > account has, cross db chaining, and everything looked ok. Some permissions > had been lost when the replacement objects due to the NSCONTROL update, I > replaced those and still nothing. > > I traced things to the Notify function and extracting out what it executes I > get the following...which gives me the error I am seeing. > > DECLARE @SPID INT > > SELECT @SPID = @@SPID > > EXEC @XPReturn = master.dbo.xp_NSNotify_2_0_3008_0 > @SPID, 1, 1, 0, 1000, 'General Delivery - > 423939', > 'EmailDevice', 'en-us', 528, '1013470877', 423939, > 1.00, 'Test' > > select @XPReturn > > Results: > > Server: Msg 60018, Level 19, State 1, Line 0 > Notification Services error 60018 > > ----------- > 1 > > (1 row(s) affected) > > Anyone have any thoughts? > > I have already unregistered, removed everything, re-registered, double > checked the permissions, and dropped and re-created the extended procs. > Totally at a loss. > > "Joe Webb" wrote: > > > Hi Mark, > > > > I meant to ask: Have you run profiler to see what's going on there? > > > > > > > > HTH... > > Joe Webb > > SQL Server MVP > > > > ~~~ > > Get up to speed quickly with SQLNS > > http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > > > > > > > > > I have a ScheduledRule in my ADF call an SP in another database. This > > > SP in turn calls the Notify function in my NS database. The call is > > > generating a 60018 error, which indicates that the "Generator context" > > > is not valid for the Notify call. > > > > > > The ADF Rule is s just a single exec to the external proc. From what > > > I understand this is a valid design. Is there anything else I need to > > > do to allow the external SP call to the Notify function to succeed? > > > > > > > > >
Hello,
This error generally indicates that the in-memory state used by the Notify XP has become corrupted. This usually happens if the Notify function has been invoked directly i.e. not from within the Notification Services context. Is this a possibility in your scenario? Unfortunately, the only way to recover from this state is to restart the SQL server. -shyam -- Learn more about SQL-NS: http://www.amazon.com/exec/obidos/tg/detail/-/0672326647/ --------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm. --------------------------------------------- [quoted text, click to view] "[WRX]Raven" <WRXRaven@discussions.microsoft.com> wrote in message news:CF13EDBF-B51D-4425-9A87-455AE38E6E98@microsoft.com... > As additional information if it helps any. > > The call works if you add one after the other parameter until > NotificationThrottle!? > > EXEC @XPReturn = master.dbo.xp_NSNotify_2_0_3008_0 > @SPID, > 1, > @DigestDelivery, > @MaxBatchSize, > @NotificationThrottle > > It really seems to be an issue within the execution of this xp. > > "[WRX]Raven" wrote: > > > Joe, I am experiencing a similiar problem to Mark. I had the NS running just > > fine and then had to run an update that changed the Event tables and rules, > > the XSLT, and a couple of other minor configuration settings. > > > > Now, I get the following error in the SQL logs and event logs: > > > > Error: 60018, Severity: 19, State: 50 > > Notification Services error 60018 > > > > Not very discriptive or helpful. I traced through all the permissions the > > account has, cross db chaining, and everything looked ok. Some permissions > > had been lost when the replacement objects due to the NSCONTROL update, I > > replaced those and still nothing. > > > > I traced things to the Notify function and extracting out what it executes I > > get the following...which gives me the error I am seeing. > > > > DECLARE @SPID INT > > > > SELECT @SPID = @@SPID > > > > EXEC @XPReturn = master.dbo.xp_NSNotify_2_0_3008_0 > > @SPID, 1, 1, 0, 1000, 'General Delivery - > > 423939', > > 'EmailDevice', 'en-us', 528, '1013470877', 423939, > > 1.00, 'Test' > > > > select @XPReturn > > > > Results: > > > > Server: Msg 60018, Level 19, State 1, Line 0 > > Notification Services error 60018 > > > > ----------- > > 1 > > > > (1 row(s) affected) > > > > Anyone have any thoughts? > > > > I have already unregistered, removed everything, re-registered, double > > checked the permissions, and dropped and re-created the extended procs. > > Totally at a loss. > > > > "Joe Webb" wrote: > > > > > Hi Mark, > > > > > > I meant to ask: Have you run profiler to see what's going on there? > > > > > > > > > > > > HTH... > > > Joe Webb > > > SQL Server MVP > > > > > > ~~~ > > > Get up to speed quickly with SQLNS > > > http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > > > > > > > > > > > > > I have a ScheduledRule in my ADF call an SP in another database. This > > > > SP in turn calls the Notify function in my NS database. The call is > > > > generating a 60018 error, which indicates that the "Generator context" > > > > is not valid for the Notify call. > > > > > > > > The ADF Rule is s just a single exec to the external proc. From what > > > > I understand this is a valid design. Is there anything else I need to > > > > do to allow the external SP call to the Notify function to succeed? > > > > > > > > > > > > > > > >
As part of my troubleshooting I did execute the call to the notify xp. You
might want to add a comment to that function when you generate it stating that it should not be executed directly. An entry in the BoL would be good to. Unfortunately this system is a 24x7 operation so it is going to be around 3 weeks before I can effect a restart to see if that fixes things. Thanks for the pointer. Simon [quoted text, click to view] "Shyam Pather [MSFT]" wrote:
> Hello, > This error generally indicates that the in-memory state used by the Notify > XP has become corrupted. This usually happens if the Notify function has > been invoked directly i.e. not from within the Notification Services > context. Is this a possibility in your scenario? > > Unfortunately, the only way to recover from this state is to restart the SQL > server. > -shyam > -- > Learn more about SQL-NS: > http://www.amazon.com/exec/obidos/tg/detail/-/0672326647/ > --------------------------------------------- > This posting is provided "AS IS" with no warranties, and confers no rights. > Use of included script samples are subject to the terms specified at > http://www.microsoft.com/info/cpyright.htm. > --------------------------------------------- > "[WRX]Raven" <WRXRaven@discussions.microsoft.com> wrote in message > news:CF13EDBF-B51D-4425-9A87-455AE38E6E98@microsoft.com... > > As additional information if it helps any. > > > > The call works if you add one after the other parameter until > > NotificationThrottle!? > > > > EXEC @XPReturn = master.dbo.xp_NSNotify_2_0_3008_0 > > @SPID, > > 1, > > @DigestDelivery, > > @MaxBatchSize, > > @NotificationThrottle > > > > It really seems to be an issue within the execution of this xp. > > > > "[WRX]Raven" wrote: > > > > > Joe, I am experiencing a similiar problem to Mark. I had the NS running > just > > > fine and then had to run an update that changed the Event tables and > rules, > > > the XSLT, and a couple of other minor configuration settings. > > > > > > Now, I get the following error in the SQL logs and event logs: > > > > > > Error: 60018, Severity: 19, State: 50 > > > Notification Services error 60018 > > > > > > Not very discriptive or helpful. I traced through all the permissions > the > > > account has, cross db chaining, and everything looked ok. Some > permissions > > > had been lost when the replacement objects due to the NSCONTROL update, > I > > > replaced those and still nothing. > > > > > > I traced things to the Notify function and extracting out what it > executes I > > > get the following...which gives me the error I am seeing. > > > > > > DECLARE @SPID INT > > > > > > SELECT @SPID = @@SPID > > > > > > EXEC @XPReturn = master.dbo.xp_NSNotify_2_0_3008_0 > > > @SPID, 1, 1, 0, 1000, 'General Delivery - > > > 423939', > > > 'EmailDevice', 'en-us', 528, '1013470877', > 423939, > > > 1.00, 'Test' > > > > > > select @XPReturn > > > > > > Results: > > > > > > Server: Msg 60018, Level 19, State 1, Line 0 > > > Notification Services error 60018 > > > > > > ----------- > > > 1 > > > > > > (1 row(s) affected) > > > > > > Anyone have any thoughts? > > > > > > I have already unregistered, removed everything, re-registered, double > > > checked the permissions, and dropped and re-created the extended procs. > > > Totally at a loss. > > > > > > "Joe Webb" wrote: > > > > > > > Hi Mark, > > > > > > > > I meant to ask: Have you run profiler to see what's going on there? > > > > > > > > > > > > > > > > HTH... > > > > Joe Webb > > > > SQL Server MVP > > > > > > > > ~~~ > > > > Get up to speed quickly with SQLNS > > > > http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 > > > > > > > > > > > > > > > > > I have a ScheduledRule in my ADF call an SP in another database. > This > > > > > SP in turn calls the Notify function in my NS database. The call is > > > > > generating a 60018 error, which indicates that the "Generator > context" > > > > > is not valid for the Notify call. > > > > > > > > > > The ADF Rule is s just a single exec to the external proc. From > what > > > > > I understand this is a valid design. Is there anything else I need > to > > > > > do to allow the external SP call to the Notify function to succeed? > > > > > > > > > > > > > > > > > > > > > > >
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 |