Hi,

When I add a new subscription class, I am getting the following error.

RuleName: CancelOrderEventSubscriptionsRule
RuleType: EventSubscriptionRule
RuleAction: SELECT dbo.CancelOrderNotificationNotify(
s.SubscriberId,s.DeviceName,s.SubscriberLocale,
convert(varchar,orderedDate,101), count(*), sum(case
isordercancelled when 1 then 1 else 0 end)) from
CancelOrderSubscriptions s, test..orderdetails group by
s.SubscriberId,s.DeviceName,s.SubscriberLocale,convert(varchar,orderedDate,101)
An attempt to run a Transact-SQL CREATE PROCEDURE statement failed.

....
...
SqlServerError: Source: .Net SqlClient Data Provider Number: 8120 State: 1
Class: 16 Server: BILLPAY-BALAJI\BILLPAYBALAJI Message: Column
'fttpnocv_stage..orderdetails.OrderedDate' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause. Procedure: NSFire7 Line Number: 35
Column 'test..orderdetails.OrderedDate' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Project : error PRJ0019: A tool returned an error code from "Performing
Makefile project actions"


Where as If I compile the same code in query analyzer, it gets compiled and
the query fetches the expected result.

What could be the problem?

Or a bug in Notification Services?

Regards,
R.Balaji

Balaji,
We're really stumped by this one. I had a couple of people look at it and we
can't seem to reproduce your problem. Can you send us the full SQL error
(seems like your post below has just an abridged version)? Also, if you
could send us your ADF (or the relevant parts of it) that would help too.

Thanks
-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]
s.SubscriberId,s.DeviceName,s.SubscriberLocale,convert(varchar,orderedDate,1
01)
[quoted text, click to view]

Hi Shyam,

Thanks for the reply.

Here goes the full details.


------ Rebuild All started: Project: AppDefinition, Configuration: Release
Win32 ------

Performing Makefile project actions
**** Delete the instance databases
Microsoft Notification Services Control Utility (Standard) (SP1) 2.0.3008.0
Copyright (C) Microsoft Corporation 2003. All rights reserved.
The following instance will be deleted
InstanceName: SpiderInstance
The following applications and their associated databases will be deleted
ApplicationName: Spider
DatabaseName: SpiderInstanceSpider
Deleting application
Application name: Spider
Delete successful.
Could Not Find C:\Program Files\Microsoft SQL Server Notification
Services\v2.0.3008.0\Samples\Spider\Test\Notifications\FileNotifications*
**** Create the application databases
Microsoft Notification Services Control Utility (Standard) (SP1) 2.0.3008.0
Copyright (C) Microsoft Corporation 2003. All rights reserved.
Creating instance
SpiderInstance
Creating application
Application name: Spider
An error occurred when creating the instance stored procedures.
FailedStoredProcedure:
Microsoft.SqlServer.NotificationServices.Common.Generator2
ApplicationName: Spider
The rule could not be added (or updated) to the table of rules in the
application database.
InstanceName: SpiderInstance
ApplicationName: Spider
RuleId: 1
RuleName: CancelOrderEventSubscriptionsRule
RuleType: EventSubscriptionRule
RuleAction: SELECT dbo.CancelOrderNotificationNotify(
s.SubscriberId,s.DeviceName,s.SubscriberLocale,
convert(varchar,orderedDate,101), count(*), sum(case
isordercancelled when 1 then 1 else 0 end)) from
CancelOrderSubscriptions s, fttpnocv_stage..orderdetails group by
s.SubscriberId,s.DeviceName,s.SubscriberLocale,convert(varchar,orderedDate,101)
An attempt to run a Transact-SQL CREATE PROCEDURE statement failed.
InstanceName: SpiderInstance
ApplicationName: Spider
StoredProcedureName: NSFire1
SqlCommandText: CREATE PROC "NSFire1"
@RuleFiringId INT
AS
BEGIN
DECLARE @RuleId INT
DECLARE @Timeout INT
DECLARE @SPID INT
DECLARE @ApplicationDatabaseName NVARCHAR(255)
DECLARE @ServerName NVARCHAR(512)
DECLARE @XPReturnCode INT

SET NOCOUNT ON
SET XACT_ABORT ON

UPDATE dbo.NSRuleFirings1
SET StartRtcTime = GETUTCDATE()
WHERE RuleFiringId = @RuleFiringId

BEGIN TRANSACTION

SELECT @Timeout = 1800
SELECT @RuleId = 1
SELECT @SPID = @@SPID
SELECT @ServerName = CONVERT(NVARCHAR(512), SERVERPROPERTY('servername'))
SELECT @ApplicationDatabaseName = DB_NAME()

EXEC @XPReturnCode = master.dbo.xp_NSBeginNotifications_2_0_3008_0
@ServerName, @ApplicationDatabaseName, @Timeout, @RuleId, @RuleFiringId, @SPID
IF (@XPReturnCode <> 0)
BEGIN
ROLLBACK TRAN
RETURN 1
END

/** APPLICATION DEFINED RULE STARTS HERE **/
SELECT dbo.CancelOrderNotificationNotify(
s.SubscriberId,s.DeviceName,s.SubscriberLocale,
convert(varchar,orderedDate,101), count(*), sum(case
isordercancelled when 1 then 1 else 0 end)) from
CancelOrderSubscriptions s, fttpnocv_stage..orderdetails group by
s.SubscriberId,s.DeviceName,s.SubscriberLocale,convert(varchar,orderedDate,101)
/** APPLICATION DEFINED RULE ENDS HERE **/

EXEC NSEndRule1 @RuleFiringId, 1, 0
COMMIT TRANSACTION
END
SqlServerError: Source: .Net SqlClient Data Provider Number: 8120 State: 1
Class: 16 Server: BILLPAY-BALAJI\BILLPAYBALAJI Message: Column
'fttpnocv_stage..orderdetails.OrderedDate' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause. Procedure: NSFire1 Line Number: 35
Column 'fttpnocv_stage..orderdetails.OrderedDate' is invalid in the select
list because it is not contained in either an aggregate function or the GROUP
BY clause.
Project : error PRJ0019: A tool returned an error code from "Performing
Makefile project actions"

Build log was saved at "file://c:\Program Files\Microsoft SQL Server
Notification
Services\v2.0.3008.0\Samples\Spider\AppDefinition\Release\BuildLog.htm"
AppDefinition - 1 error(s), 0 warning(s)


---------------------- Done ----------------------

Rebuild All: 0 succeeded, 1 failed, 0 skipped



Build Log ------- Build started: Project: AppDefinition, Configuration:
Release|Win32 -------

Command Lines Creating temporary file
"C:\DOCUME~1\v718264\LOCALS~1\Temp\BAT000005.bat" with contents
[
@echo off
pushd BuildScripts & call RunRebuild.cmd & popd
if errorlevel 1 goto VCReportError
goto VCEnd
:VCReportError
echo Project : error PRJ0019: A tool returned an error code from "Performing
Makefile project actions"
exit 1
:VCEnd
]
Creating command line "C:\DOCUME~1\v718264\LOCALS~1\Temp\BAT000005.bat"
Output Window Performing Makefile project actions
**** Delete the instance databases
Microsoft Notification Services Control Utility (Standard) (SP1) 2.0.3008.0
Copyright (C) Microsoft Corporation 2003. All rights reserved.
The following instance will be deleted
InstanceName: SpiderInstance
The following applications and their associated databases will be deleted
ApplicationName: Spider
DatabaseName: SpiderInstanceSpider
Deleting application
Application name: Spider
Delete successful.
Could Not Find C:\Program Files\Microsoft SQL Server Notification
Services\v2.0.3008.0\Samples\Spider\Test\Notifications\FileNotifications*
**** Create the application databases
Microsoft Notification Services Control Utility (Standard) (SP1) 2.0.3008.0
Copyright (C) Microsoft Corporation 2003. All rights reserved.
Creating instance
SpiderInstance
Creating application
Application name: Spider
An error occurred when creating the instance stored procedures.
FailedStoredProcedure:
Microsoft.SqlServer.NotificationServices.Common.Generator2
ApplicationName: Spider
The rule could not be added (or updated) to the table of rules in the
application database.
InstanceName: SpiderInstance
ApplicationName: Spider
RuleId: 1
RuleName: CancelOrderEventSubscriptionsRule
RuleType: EventSubscriptionRule
RuleAction: SELECT dbo.CancelOrderNotificationNotify(
s.SubscriberId,s.DeviceName,s.SubscriberLocale,
convert(varchar,orderedDate,101), count(*), sum(case
isordercancelled when 1 then 1 else 0 end)) from
CancelOrderSubscriptions s, fttpnocv_stage..orderdetails group by
s.SubscriberId,s.DeviceName,s.SubscriberLocale,convert(varchar,orderedDate,101)
An attempt to run a Transact-SQL CREATE PROCEDURE statement failed.
InstanceName: SpiderInstance
ApplicationName: Spider
StoredProcedureName: NSFire1
SqlCommandText: CREATE PROC "NSFire1"
@RuleFiringId INT
AS
BEGIN
DECLARE @RuleId INT
DECLARE @Timeout INT
DECLARE @SPID INT

Hi,

I got a work around for the problem.

I used min(convert(varchar,orderedDate,101)) in the select statement and it
is getting compiled.
(Though min/max will be meaningless when you use the grouped field in the
select list)

This is the updated query

SELECT dbo.CancelOrderNotificationNotify(
s.SubscriberId,s.DeviceName,s.SubscriberLocale,
min(convert(varchar,orderedDate,101)),
min(convert(varchar,orderedDate,102)),
count(*),
sum(case isordercancelled
when 1 then 1
else 0 end))
from CancelOrderSubscriptions s, fttpnocv_stage..orderdetails
group by
s.SubscriberId,s.DeviceName,s.SubscriberLocale,convert(varchar,orderedDate,101)



Thanks.

Regards,
R.Balaji




[quoted text, click to view]