Many users seems have problem with the sqlSitemapProvider and me too!  

Problem 1:
If I add the line:  sqlCacheDependency="CommandNotification" in my web.config within the sitemapnode the whole treemenu disappears.
If I remove it the treemenu shows up again, why this do you think?

Problem 2:
If I change add a node in the database the treemenu won't update after I refresh the page.
Is it because problem nr. 1?

The code I'm using:

Web.config 

<add name="SqlServer" connectionString="Data Source=*********;Initial Catalog=******; uid=******;
      pwd=*******; Connect Timeout=10;" providerName="System.Data.SqlClient"/>

 <siteMap enabled="true" defaultProvider="MySiteMapProvider">
            <providers>
                <add name="MySiteMapProvider"
                     type="SqlSiteMapProvider"
                     connectionStringName="SqlServer"
                     securityTrimmingEnabled="true"
                     sqlCacheDependency="CommandNotification" />
            </providers>
        </siteMap>
        
        <caching>
            <sqlCacheDependency enabled="true"/>
        </caching>

Default.aspx

 <asp:TreeView ID="TreeView1"
        runat="server"
        DataSourceID="SiteMapDataSource1">
</asp:TreeView>

 <asp:SiteMapDataSource ID="SiteMapDataSource1" runat="server" />

You surely have configured the DataTable for use with SqlCacheDependency? If not, do it while using the aspnet_regsql.exe in the framework\version folder eg.

aspnet_regsql -S [servername\instancename] -E -d [DataBase] -ed -et -t [TableName]

where -S is the name of the SqlServer, -E stands for windows authentication, -t is the used DataBaseTable and -d is the name of the DataBase. To see all command arguments type aspet_regsql -?

 The MSDN is a good entry point into the use of SqlCacheDependency

Do I really have to do that?

Sorry, I didn't mentioned that I'm using sql server 2005 on a hosted server.

If I have to use the aspnet_regsql.exe, can I run it from my computer to the sql on the hosted server?

I ran the command against the hosted server!
------------------------------------
Enabling the database for SQL cache dependency.
.
Finished.

Enabling the table for SQL cache dependency.

Finished.

 ---------------------------------------

But it didn't make any different!

If I add CommandNotification in the Web.config the menu disappears.
And if I update the database with for example a new menurow, it won't show up! Crap!

doggelito:

Do I really have to do that?

If you dont trust me, you may trust the MSDN and read here.

MSDN:

For this class to work correctly when using table-based notifications, the database and any tables that you want to make dependencies on must have notifications enabled. You can enable notifications by using the methods of the SqlCacheDependencyAdmin class or the Aspnet_regsql.exe command-line tool. Also, the proper configuration settings must be included in the application's Web.config file.

doggelito:

If I have to use the aspnet_regsql.exe, can I run it from my computer to the sql on the hosted server?

Never tested, could'nt say anything about.

I think I found the problem!

My stored procedure was collecting the menu from a view and not directly from the table.
It seems that not acceptible.

doggelito:

I think I found the problem!

Glad to hear

doggelito:

My stored procedure was collecting the menu from a view and not directly from the table.

AFAIK the SP must use direct a table.