Enable table dependency in AdventureWorks db?

I have been having trouble with enabling table dependencies using the aspnet_regsql utility on the Product table in the AdventureWorks database. It is in the Production in the normal dbo schema. When I run the utility to enable the table dependency for SQL cache notifications it fails because it tries to prefix the reference to the table with dbo.

Now I know it just adds a trigger for INSERT, UPDATE and DELETE where it runs a stored procedure to update the main table dependency table, but if I do that manually it requires that the trigger also run in the Production schema. I am not sure if this will still work with SQL query notifications.

http://msdn2.microsoft.com/en-us/library/ms124719.aspx

Please reply if you can help.

Hi Offwhite,

Based on your description, I understand that you need to register the

Production.Product table for Sql Cache Dependency. However, because of the

schema name is not dbo, the registration failed.

Based on my research, this is by design in aspnet_regsql.exe tool. The stored

procedure generated for AspNet_SqlCacheRegisterTableStoredProcedure, has

hardcoded dbo as the schema name in it.

To change this behavior, we have to manually modify the stored procedure, by

changing the full name of the trigger it is creating. Here is an article

about how to do this.

http://www.feedshow.com/show_items-feed=0461b14d31d1ce82afaef7eac4e0ed1a

HTH. If this does not answer you question, please feel free to mark it as Not Answered and post your reply. Thanks!

|||

Thanks! That is gotten me a little farther but I am now having some more issues. I would appreciate some help. I have posted my question and progress here...

http://forums.asp.net/thread/1594211.aspx

enable caching error during sql dependency registration

Hey, I can't enable caching on my local computer..I use d

aspnet_regsql -S "localhost\SQLEXPRESS" -E -d books -ed

and

aspnet_regsql -S "Admin\SQLEXPRESS" -E -d books -ed

to try to start but i get a login error...

cannot open database "books" requested by login. The login failed. Login failed for user Computer2/Admin
unable to connect to SQL database for cache dependency registration

I tested connection with vwd and sql express management and it connects so I don't know what i'm doing wrong since I followed the direction and I googled for answer but not coming up with anything that's helping me..anyone have this problem and know how to resolve so i can cache some data output on my project

So you're encountering connection failure when you tried to connect to SQL Express instance in aspnet_regsql, right? I see you use Windows Authentication to connect to SQL, so first make sure there is a database named books on the SQLEXPRESS instance, and the login you're using (Windows logon account in this case) has permissions on the books database (you can simply add the account to the db_owner role on the books database).

BTW, why not aspnet_sql without parameter? Then you have a friendly GUI and the registration will become much easier.

|||

Well..I'm not sure how it worked out but this is what i basically did...attached database to sql express management...then detached...then called it like before but with its full map of c:/website/blah/books.mdf and that worked but I don't think it should have...and to answer post above i just followed examples located on microsoft.com...i don't know about asp_sql gui...didn't know they had one in cmd lines...anyhow..this is my first windows server so it's a bit of a learning curve..still learning how to use ashx and httphandlers...well..to the next problem now...weeeeeeeeeeeee