Asp Forum - SqlDataSource and Caching problems - microsoft.public.dotnet.framework.aspnet.caching

masmith

6/29/2006 10:14:00 PM

I have a ASP.NET web page with a SqlDataSource configured with the select
pointing to a stored procedure. This works as expected and using the
SqlDataSource_Selected event I test how many rows selected by:
Debug.WriteLine(e.AffectedRows);

When viewing the page I find the following:
When EnableCaching=False it returns the expected value of 2 (2 rows of data).
When EnableCaching=True and SqlCacheDependency=CommandNotification it
returns 0.

/***Stored procedure used by select***/
set ANSI_NULLS ON
set ANSI_PADDING ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Test1_Sel]
AS
BEGIN
set ANSI_NULLS ON
set ANSI_PADDING ON
set ANSI_WARNINGS ON
set CONCAT_NULL_YIELDS_NULL ON
set QUOTED_IDENTIFIER ON
set NUMERIC_ROUNDABORT OFF
set ARITHABORT ON
Select ID, Name, ChangeDate, ChangeUserID, ListID From dbo.Test1 Order By ID
END
/*** ***/

I also have followed the instruction to enable SQL Server 2005 to support
this:
Enable SQL Server 2005 for Notificatgions:
(Assume you already hvae Notification Services installed)

/***Enable the .NET CLR***/
EXEC sp_configure 'show advanced options', '1'
go
reconfigure
go
EXEC sp_configure 'clr enabled', '1'
go
reconfigure
go
/***Enable Broker for database***/
Alter DATABASE <database name> SET ENABLE_BROKER
/***Grant permissions to sUBSCRIVE***/
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <username>

Also the SQL Server 2005 is service is using "Network Service" for the logon
account.

Thanks,
--
Mark

3 Answers

Teemu Keiski

7/1/2006 8:53:00 AM

Have you checked also the last step mentioned in this:

http://www.asp.net/QuickStart/aspnet/doc/caching/SQLInvalid...

1.. Queries must explicitly include column names in the SELECT statement.
Using "SELECT *" results in a query that will not be registered with Sql
Server 2005 query notifications.
2.. Table names in queries must include the ownername. For example, if you
issue a query against the authors table in the pubs database, the query must
reference the table as "dbo.authors".
3.. The security identity running the query must have rights to register
queries for notification in Sql Server 2005. This right can be granted with
the following T-SQL command: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO
username.
4.. The security identity running the query must also have rights to send
query notifications from Sql Server 2005. This right can be granted with the
following T-SQL command: GRANT SEND ON SERVICE::SqlQueryNotificationService
TO username.

--
Teemu Keiski
ASP.NET MVP, AspInsider
Finland, EU
http://blogs.aspadvice....

"masmith" <masmith@discussions.microsoft.com> wrote in message
news:C70BB2EE-0261-4165-B958-6CD120F5261B@microsoft.com...
>I have a ASP.NET web page with a SqlDataSource configured with the select
> pointing to a stored procedure. This works as expected and using the
> SqlDataSource_Selected event I test how many rows selected by:
> Debug.WriteLine(e.AffectedRows);
>
> When viewing the page I find the following:
> When EnableCaching=False it returns the expected value of 2 (2 rows of
> data).
> When EnableCaching=True and SqlCacheDependency=CommandNotification it
> returns 0.
>
> /***Stored procedure used by select***/
> set ANSI_NULLS ON
> set ANSI_PADDING ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sp_Test1_Sel]
> AS
> BEGIN
> set ANSI_NULLS ON
> set ANSI_PADDING ON
> set ANSI_WARNINGS ON
> set CONCAT_NULL_YIELDS_NULL ON
> set QUOTED_IDENTIFIER ON
> set NUMERIC_ROUNDABORT OFF
> set ARITHABORT ON
> Select ID, Name, ChangeDate, ChangeUserID, ListID From dbo.Test1 Order By
> ID
> END
> /*** ***/
>
> I also have followed the instruction to enable SQL Server 2005 to support
> this:
> Enable SQL Server 2005 for Notificatgions:
> (Assume you already hvae Notification Services installed)
>
> /***Enable the .NET CLR***/
> EXEC sp_configure 'show advanced options', '1'
> go
> reconfigure
> go
> EXEC sp_configure 'clr enabled', '1'
> go
> reconfigure
> go
> /***Enable Broker for database***/
> Alter DATABASE <database name> SET ENABLE_BROKER
> /***Grant permissions to sUBSCRIVE***/
> GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <username>
>
> Also the SQL Server 2005 is service is using "Network Service" for the
> logon
> account.
>
> Thanks,
> --
> Mark

masmith

7/4/2006 8:01:00 PM

Yes I checked that and all the other information on what is required to make
it work.

What I have at this point is that it works on my development pc pointed to a
local database on my computer. It does not work when the connection is
pointed to a test database server or a production database server. All three
SQL instances are using the same copy of the database and have the same items
that are required to make it work set or permissions granted.

Is there any place or way I can get an error message or information when
what I assume is the SqlClient.SqlDependency.Start or the SqlDataSource fails
to do what is required for this to work?
--
Thank,
Mark

"Teemu Keiski" wrote:

> Have you checked also the last step mentioned in this:
>
> http://www.asp.net/QuickStart/aspnet/doc/caching/SQLInvalid...
>
> 1.. Queries must explicitly include column names in the SELECT statement.
> Using "SELECT *" results in a query that will not be registered with Sql
> Server 2005 query notifications.
> 2.. Table names in queries must include the ownername. For example, if you
> issue a query against the authors table in the pubs database, the query must
> reference the table as "dbo.authors".
> 3.. The security identity running the query must have rights to register
> queries for notification in Sql Server 2005. This right can be granted with
> the following T-SQL command: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO
> username.
> 4.. The security identity running the query must also have rights to send
> query notifications from Sql Server 2005. This right can be granted with the
> following T-SQL command: GRANT SEND ON SERVICE::SqlQueryNotificationService
> TO username.
>
> --
> Teemu Keiski
> ASP.NET MVP, AspInsider
> Finland, EU
> http://blogs.aspadvice....
>
>
> "masmith" <masmith@discussions.microsoft.com> wrote in message
> news:C70BB2EE-0261-4165-B958-6CD120F5261B@microsoft.com...
> >I have a ASP.NET web page with a SqlDataSource configured with the select
> > pointing to a stored procedure. This works as expected and using the
> > SqlDataSource_Selected event I test how many rows selected by:
> > Debug.WriteLine(e.AffectedRows);
> >
> > When viewing the page I find the following:
> > When EnableCaching=False it returns the expected value of 2 (2 rows of
> > data).
> > When EnableCaching=True and SqlCacheDependency=CommandNotification it
> > returns 0.
> >
> > /***Stored procedure used by select***/
> > set ANSI_NULLS ON
> > set ANSI_PADDING ON
> > set QUOTED_IDENTIFIER ON
> > GO
> > ALTER PROCEDURE [dbo].[sp_Test1_Sel]
> > AS
> > BEGIN
> > set ANSI_NULLS ON
> > set ANSI_PADDING ON
> > set ANSI_WARNINGS ON
> > set CONCAT_NULL_YIELDS_NULL ON
> > set QUOTED_IDENTIFIER ON
> > set NUMERIC_ROUNDABORT OFF
> > set ARITHABORT ON
> > Select ID, Name, ChangeDate, ChangeUserID, ListID From dbo.Test1 Order By
> > ID
> > END
> > /*** ***/
> >
> > I also have followed the instruction to enable SQL Server 2005 to support
> > this:
> > Enable SQL Server 2005 for Notificatgions:
> > (Assume you already hvae Notification Services installed)
> >
> > /***Enable the .NET CLR***/
> > EXEC sp_configure 'show advanced options', '1'
> > go
> > reconfigure
> > go
> > EXEC sp_configure 'clr enabled', '1'
> > go
> > reconfigure
> > go
> > /***Enable Broker for database***/
> > Alter DATABASE <database name> SET ENABLE_BROKER
> > /***Grant permissions to sUBSCRIVE***/
> > GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <username>
> >
> > Also the SQL Server 2005 is service is using "Network Service" for the
> > logon
> > account.
> >
> > Thanks,
> > --
> > Mark
>
>
>

masmith

7/18/2006 3:46:00 PM

After working with Microsoft I found two problems with what I was doing.
1) You can not use window authentication in the Application_Start event of
the Global.asax.
2) The SqlDependency.Start method needs some additional permission that you
would not normally give a typical user of the database. I determined that I
could leave the Start in the Application_Start event but hard code a SQL
login with the required permissions granted. I also implemented encryption
of the connectionString section in the web.config.

The following is a good article on permissions required by the
SqlDependency.Start:
http://www.codeproject.com/useritems/SqlDependencyPermi....

--
Sincerely,
Mark

"masmith" wrote:

> Yes I checked that and all the other information on what is required to make
> it work.
>
> What I have at this point is that it works on my development pc pointed to a
> local database on my computer. It does not work when the connection is
> pointed to a test database server or a production database server. All three
> SQL instances are using the same copy of the database and have the same items
> that are required to make it work set or permissions granted.
>
> Is there any place or way I can get an error message or information when
> what I assume is the SqlClient.SqlDependency.Start or the SqlDataSource fails
> to do what is required for this to work?
> --
> Thank,
> Mark
>
>
> "Teemu Keiski" wrote:
>
> > Have you checked also the last step mentioned in this:
> >
> > http://www.asp.net/QuickStart/aspnet/doc/caching/SQLInvalid...
> >
> > 1.. Queries must explicitly include column names in the SELECT statement.
> > Using "SELECT *" results in a query that will not be registered with Sql
> > Server 2005 query notifications.
> > 2.. Table names in queries must include the ownername. For example, if you
> > issue a query against the authors table in the pubs database, the query must
> > reference the table as "dbo.authors".
> > 3.. The security identity running the query must have rights to register
> > queries for notification in Sql Server 2005. This right can be granted with
> > the following T-SQL command: GRANT SUBSCRIBE QUERY NOTIFICATIONS TO
> > username.
> > 4.. The security identity running the query must also have rights to send
> > query notifications from Sql Server 2005. This right can be granted with the
> > following T-SQL command: GRANT SEND ON SERVICE::SqlQueryNotificationService
> > TO username.
> >
> > --
> > Teemu Keiski
> > ASP.NET MVP, AspInsider
> > Finland, EU
> > http://blogs.aspadvice....
> >
> >
> > "masmith" <masmith@discussions.microsoft.com> wrote in message
> > news:C70BB2EE-0261-4165-B958-6CD120F5261B@microsoft.com...
> > >I have a ASP.NET web page with a SqlDataSource configured with the select
> > > pointing to a stored procedure. This works as expected and using the
> > > SqlDataSource_Selected event I test how many rows selected by:
> > > Debug.WriteLine(e.AffectedRows);
> > >
> > > When viewing the page I find the following:
> > > When EnableCaching=False it returns the expected value of 2 (2 rows of
> > > data).
> > > When EnableCaching=True and SqlCacheDependency=CommandNotification it
> > > returns 0.
> > >
> > > /***Stored procedure used by select***/
> > > set ANSI_NULLS ON
> > > set ANSI_PADDING ON
> > > set QUOTED_IDENTIFIER ON
> > > GO
> > > ALTER PROCEDURE [dbo].[sp_Test1_Sel]
> > > AS
> > > BEGIN
> > > set ANSI_NULLS ON
> > > set ANSI_PADDING ON
> > > set ANSI_WARNINGS ON
> > > set CONCAT_NULL_YIELDS_NULL ON
> > > set QUOTED_IDENTIFIER ON
> > > set NUMERIC_ROUNDABORT OFF
> > > set ARITHABORT ON
> > > Select ID, Name, ChangeDate, ChangeUserID, ListID From dbo.Test1 Order By
> > > ID
> > > END
> > > /*** ***/
> > >
> > > I also have followed the instruction to enable SQL Server 2005 to support
> > > this:
> > > Enable SQL Server 2005 for Notificatgions:
> > > (Assume you already hvae Notification Services installed)
> > >
> > > /***Enable the .NET CLR***/
> > > EXEC sp_configure 'show advanced options', '1'
> > > go
> > > reconfigure
> > > go
> > > EXEC sp_configure 'clr enabled', '1'
> > > go
> > > reconfigure
> > > go
> > > /***Enable Broker for database***/
> > > Alter DATABASE <database name> SET ENABLE_BROKER
> > > /***Grant permissions to sUBSCRIVE***/
> > > GRANT SUBSCRIBE QUERY NOTIFICATIONS TO <username>
> > >
> > > Also the SQL Server 2005 is service is using "Network Service" for the
> > > logon
> > > account.
> > >
> > > Thanks,
> > > --
> > > Mark
> >
> >
> >

microsoft.public.dotnet.framework.aspnet.caching

SqlDataSource and Caching problems

masmith

Teemu Keiski

masmith

masmith

x Login to ForumsZone