Enable Caching Error During Sql Dependency Registration
Sep 11, 2006
Hey, I can't enable caching on my local computer..I use d
aspnet_regsql -S "localhostSQLEXPRESS" -E -d books -ed
and
aspnet_regsql -S "AdminSQLEXPRESS" -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
Hello, I have caching enabled in my application and I am using SqlCacheDependency to cache my tables. Well, the cache works fine but when the table is updated the information in my cache does not update. I DO have the broker service started and running but not sure what else i am missing. I am using sql server 2005.
Probably a dumb question, but if one is using SQL Server 2000 instead of 'SQL Express' what has to change here, in order to set up caching? What changes in the paths?
Today I was asked a question I couldn't answer [it happens all the time...more than I like to admit...] concerning the performance hit of the SQL LIKE Filter.
The question was, what is the performance hit you take when you, say filter the results of a gridview control, with a LIKE FILTER in a stored procedure in SQL SERVER 2005?
Would it be preferable to create a Dependency Cache of a DataSet or a DataView object and then perform some type of filtering operation on that?
I am building a web app using VS2005 and SQL 2005 I would like to use the Caching Application Block to cache objects from my BLL. I was wondering if there is a way of utilizing the build in SQLDependency in SQL 2005 with the Caching Application Block??? Does anybody have tried this, are there any samples on the web???
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, I've just installed MSDE 2000 Release A on a Windows NT Server 4.0 w/SP6. I'm on a WinXP machine and I've installed the Enterprise Manager, and am trying to register a new server registration. However, I'm getting the error: Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. I've made sure that both the client and server are using TCP/IP for the protocol list, and I've tried using the physical server's name and it's IP address to register, but keep getting the same error. Thanks for the help.
Client found response content type of 'text/html; charset=utf-8', but expected 'text/xml'. The request failed with the error message: -- <html> <head> <title> SQL Server Reporting Services </title><meta name="Generator" content="Microsoft SQL Server Reporting Services 9.00.2047.00" /> <meta name="HTTP Status" content="500" /> <meta name="ProductLocaleID" content="9" /> <meta name="CountryLocaleID" content="1033" /> <meta name="StackTrace" content=" at Microsoft.ReportingServices.Diagnostics.RSConfiguration.Load() at Microsoft.ReportingServices.Diagnostics.RSConfiguration.Construct(String configFileName) at Microsoft.ReportingServices.Diagnostics.RSConfiguration..ctor(String configFileName, String location) at Microsoft.ReportingServices.Diagnostics.RSConfigurationManager..ctor(String configFileName, String configLocation) at Microsoft.ReportingServices.Library.Global.get_ConfigurationManager() at Microsoft.ReportingServices.WebServer.Global.StartApp() at Microsoft.ReportingServices.WebServer.Global.Application_BeginRequest(Object sender, EventArgs e)" /> <style> BODY {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE: 8pt; COLOR:black} H1 {FONT-FAMILY:Verdana; FONT-WEIGHT:700; FONT-SIZE:15pt} LI {FONT-FAMILY:Verdana; FONT-WEIGHT:normal; FONT-SIZE:8pt; DISPLAY:inline} .ProductInfo {FONT-FAMILY:Verdana; FONT-WEIGHT:bold; FONT-SIZE: 8pt; COLOR:gray} A:link {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none} A:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#FF3300; TEXT-DECORATION:underline} A:visited {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; COLOR:#3366CC; TEXT-DECORATION:none} A:visited:hover {FONT-SIZE: 8pt; FONT-FAMILY:Verdana; color:#FF3300; TEXT-DECORATION:underline}
</style> </head><body bgcolor="white"> <h1> Reporting Services Error<hr width="100%" size="1" color="silver" /> </h1><ul> <li>The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError) </li><ul> <li>Access to the path 'c:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServerRSReportServer.config' is denied.</li> </ul> </ul><hr width="100%" size="1" color="silver" /><span class="ProductInfo">SQL Server Reporting Services</span> </body> </html> --. (Microsoft.SqlServer.Management.UI.RSClient)
I have a problem that looks like it has not been discussed before inthese groups.I have a simple SQLAgent job that runs sp_who (could be anything, butlet's just say sp_who for this example). I have set the jobstep towrite to an output file "T:out.txt". If the job is owned by anadmin, it runs fine and writes the output file. If it is owned by anon-admin user, it gets the following error msg:Warning: cannot write logfile t:out.txt. Error 1059 : Circularservice dependency was specified. The step failed.I know about setting up the SQLAgent CMDExec proxy account, and havedone that. In fact, both SQLAgent and the SQLAgent cmdexec proxy usethe same domain account, which is in the administrator group of thelocal server. So, I know that security is not the issue.When a simple job runs and writes to an output file, what service orservice group could it be trying to start or modify? I looked throughthe list of Services, and could not find any circular dependencies.Is there a utility to detect this? Why would running under onecontext (as an admin) be ok while the other context (non-admin on SQL,but using the same admin domain service account) fails?Thanks in advance for any info you might have.
I am creating a report on a database where some of the table names start with the @ sign - ie @table1.
Reporting services picks this up as a parameter, instead of a table name in my query, even though I am encapsulating the table name in square brackets eg. [@table1]
I have several data sets in the report that i am using to populate valid parameters. These datasets are all variations of queries from tables that have @ as the first character.
When i then try to run the report i get an error message as follows: "The report parameter pool has a default value or a valid value that depends on the report parameter SD_POOLCONTRACTS. Forward dependencies are not valid"
This is frustrating as SD_POOLCONTRACTS is not a report parameter but one of the database tables that has @ for it's first character.
here is the query that i use to obtain the valid values for the pool report parameter that i am trying to set up.
SELECT distinct u_poolcode as Pool FROM OCRD INNER JOIN OCRG ON OCRD.GroupCode = OCRG.GroupCode INNER JOIN CRD1 ON OCRD.CardCode = CRD1.CardCode inner JOIN [@SD_POOLCONTRACT] INNER JOIN [@SD_POOLCONTRCT_LINE] ON [@SD_POOLCONTRACT].DocEntry = [@SD_POOLCONTRCT_LINE].DocEntry INNER JOIN [@SD_CONTRACTS] ON [@SD_POOLCONTRCT_LINE].U_DocNo = [@SD_CONTRACTS].DocNum ON case when len(ocrd.fathercard) = 0 then ocrd.cardcode else ISNULL(OCRD.FatherCard, OCRD.CardCode) end = [@SD_CONTRACTS].U_CardCode WHERE (OCRD.CardType = 'c') AND (OCRG.GroupName LIKE N'producer%') AND (CRD1.AdresType = 'b') ORDER BY OCRG.GroupName, FatherCard, OCRD.CardCode
Hello, I got the error: ______________________ Reporting Services Error
* For more information about this error navigate to the report server on the local server machine, or enable remote errors SQL Server Reporting Services ______________________
I saw the answer: "You could set the "EnableRemoteErrors" configuration value to True in the ReportServer.ConfigurationInfo database table."
How can I get to the ReportServer.ConfigurationInfo database table?
I am on the reportservice server and I run "reporting services configuration manager" and the "sql server managment". I didn't saw that option :(
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '.'.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
I am using Data flow object in that I take OleDb as Source and Sql Server As destination. Without enabling Package configuration everything is working fine data is moving from Source to destination.
But When i apply Package Confiiguration It throw Error And My OleDb Source object Convert in to RED Color. I cant able to track This errror
Hi All,I have an application that reads data from a very slow database link(like 10 seconds per call) though what I am looking for would be ofgeneric use for anyone who has long-running queries that arefrequently repeated.I would like to be able to cache the results of a query so that I donot have to re-execute that query if it is reissued. Ideally Ibelieve that this could be implemented by hiding the query inside aUDF and exposing the UDF through a view. The UDF could then "Checkthe cache" and only run the slow query if there wasn't a match (or ifthe match was too old). From what I understand the best way to dothis would be for the cache to be an extended stored procedure.Has anyone done or seen this? Has someone written a copy that Icould purchase? Does anyone care to offer their opinnion of how or ifthis could work?Thanks in Advance,Steven
Hello All, I want to use SQLDataSource as a base of all Gridview in my application. I have read that SQLDataSource support caching. I want to know as SQLDataSource is a child control of page class. It will get unloaded when page is unloaded. So how caching works. Like I am using SQLDataSource as base of my GridView control. When I perform paging or sorting,In theory It says that It will not hit the Datbase again. It will keep that Data in memory as DataSet, but this will stay alive upto life of page, which starts again and again when a page is requested, So as its child controls. Can anybody give me more intrinsics on this. Any Help from Microsoft is appreciated !!!! Thanks !!
I know I can use the SqlDataSource object and cache the results by setting it's enableCache property to true, but what if I am using essentially the same SqlDataSource control across multiple pages? For instance I have a States SqlDataSource on many pages in my site, if I enable caching on all of these objects won't they all be cached separately? Is the solution to not use the SqlDataSource and instead cache the datatable of results and bind the dropdownlists to that? Thanks in advance.
Hi Expert, Am I doing it right? Can you give me any suggestions please? Thank you!!I followed the way for setting up the sql cache dependency from http://quickstarts.asp.net/QuickStartv20/aspnet/doc/caching/SQLInvalidation.aspx.However, I still have the following error: When using SqlDependency without providing an options value, SqlDependency.Start() must be called prior to execution of a command added to the SqlDependency instance. IssueSummary.aspx<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="true" CodeFile="IssueSummary.aspx.vb" Inherits="IssueSummary" title="Issue Summary" %><%@ Register Assembly="AtlasControlToolkit" Namespace="AtlasControlToolkit" TagPrefix="cc2" %><%@ Register Assembly="Microsoft.Web.Atlas" Namespace="Microsoft.Web.UI" TagPrefix="cc1" %><%@ OutputCache Duration="999999" VaryByParam="None" SqlDependency="CommandNotification" %> Global.asaxSub Application_Start(ByVal sender As Object, ByVal e As EventArgs)Dim connectionString As String = ConfigurationManager.ConnectionStrings("ICTConnectionString").ConnectionStringDim needToInstall As Boolean = TrueTryDim tables() As Stringtables = SqlCacheDependencyAdmin.GetTablesEnabledForNotifications(connectionString)If (Not tables Is Nothing) ThenDim tbl As StringFor Each tbl In tablesIf (tbl.ToLower().Equals("IssueDetails")) ThenneedToInstall = FalseEnd IfNextEnd IfCatch ex As ExceptionneedToInstall = TrueEnd TryIf (needToInstall) ThenSqlCacheDependencyAdmin.EnableNotifications(connectionString)SqlCacheDependencyAdmin.EnableTableForNotifications(connectionString, "IssueDetails")End IfEnd Sub Web.config<connectionStrings><add name="ICTConnectionString" connectionString="Data Source=jamesle-3;Initial Catalog=IssueCommunicationTool;Integrated Security=True" providerName="System.Data.SqlClient"/></connectionStrings><system.web><caching><sqlCacheDependency enabled="true" pollTime="1000" ><databases><add name="ICTDB" connectionStringName="ICTConnectionString" /></databases></sqlCacheDependency></caching> SQL Server 2005 - Database - IssueCommunicationToolSELECT is_broker_enabled FROM sys.databases WHERE name = 'IssueCommunicationTool' Return = 1, it means the broker service is enabled. Also, the database has created the table ASPNET_SQLCacheTableForChangeNotification, the trigger in IssueDetails table, and some other store procedures.
I have a control that shows the top 5 best selling products. Thes list does not need to be up to date at all times. Ideally I would like to cache the data for say 24hr before pulling it again if someone accesses the control. I thought there was an easy way to do this, but I can't seem to find anything. Can anyone point me in the right direction.
Hi, I'm trying to set up a SqlCacheDependency using the Query notifications of SQL Server 2005. I haven't even got to the point of testing the notifications part. My problem is that my DataTable is not even getting stored in the cache when I insert it. The cache seems to be getting invalided as soon as I add the DataTable. Here is my code: (am trying to get a simple example working first)protected void Page_Load(object sender, EventArgs e) { DataTable results = (DataTable)HttpRuntime.Cache.Get("supplyFunctions"); if (results == null) { Response.Write("Cache Invalidated, hitting DB. TIME: " + DateTime.Now.ToString()); results = getSupplyFunctions(); } else { Response.Write("got from Cache, TIME: " + DateTime.Now); } GridView1.DataSource = results; GridView1.DataBind(); }private DataTable getSupplyFunctions() { DataTable results = new DataTable(); using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()) ) { using (SqlCommand command = connection.CreateCommand()) { command.CommandText = "schema.myStoredProc"; command.CommandType = CommandType.StoredProcedure; SqlCacheDependency dependency = new SqlCacheDependency(command); SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = command; DataSet dataset = new DataSet(); adapter.Fill(dataset); results = dataset.Tables[0]; HttpRuntime.Cache.Insert("supplyFunctions", results, dependency); }
} return results; } Also, whats weird is that when I keep refreshing the page, after like 5 to 10 minutes it works, and starts caching the data. This disturbs me-- I would like to know what is going on. I am pretty sure my stored procedure doesn't break the rules of the query notifications. Can someone help me out?!??! Thanks!
I have a page in my website that pulls content for a newsletter from a SQL Server 2000 database using a SqlDataSource and a FormView. I add a new newsletter every week. Other than that weekly addition, no changes are really ever needed or made to the data. Scott Gu says every application can and should make use of caching and I would like to add some kind of caching to improve the performance of this page. I have read about SqlCacheDependency with SQL 2000, but if I understand it correctly, it will poll the database for changes every x seconds, an expensive operation that I just don’t need; the data only changes once a week. I’ve seen some code examples using DataSets and the Cache API but I’m really just looking for a simple, mostly declarative solution using the SqlDataSource. Theoretically, I would like the data cached forever until I insert a new item. I have set the following cache-related properties on my SqlDataSource as follows:
Setting these properties seems to work great for caching the data but how do I programmatically invalidate the cache when a new item is inserted? I have a separate admin page with a FormView that I use to insert the newsletter each week. Is there some kind of code that I could put in the ItemInserted event that would invalidate the cache so that the new item will be displayed and then cached? I don't have any experience with caching; where am I going wrong? Are my expectations unrealistic? Thank you in advance to anyone who can shed some light on this issue.
I've recently deployed an ASP.Net application, with admittedly not a lot of experience with SQL Server 2005 caching.A problem that I ran into is a user searching for a case number very soon after entering it, and it not showing up. However, after a few minutes and re-running the search it appeared.I'm guessing that this is a SQL Server caching issue, but I'm not sure what to do about it.On my search screen, I have a listview for the results, a textbox for the case # input, a button and a sqldatasource. The SqlDataSource uses a select statement, and two parameters for user(ensure the case belongs to that user) and casenumber. The SqlDataSource has enablecaching set to false. Any ideas what I can do about this issue? Also, any good places I can read about sql server caching? Thanks
I'm trying to performance tune a procedure and am sort of being thwarted by caching.
When I first run the procedure, it takes a few seconds which is too long in this case. Subsequent executions in Management Studio are nearly instantaneous, though, which I imagine is due to caching and does not reflect the behavior of the procedure in production.
Is there a way to disable caching so that each execution of the procedure in Management Studio will be consistent and reflect the "first run" performance?
Is there a way to manage the cache when a file is being FTP'd from a server? It writes the file to the d: drive but caches it to c: first. Can I add a command line switch to tell it not to cache the file first?
Is it possible expire a report cache after less than one minute? I'm looking for a way to only have a report hit the database once every 10 seconds, no matter how many people are hitting it. Thanks.
If i use the caching feature of the report. is there a way i can find or set in the report itself if the user if renedered from cache or actually connected to the database.
i mean if ON the Caching, for the 1st user the data will come from DB and the report manager stores the report in cache. next user if he tries to connect should get a message that the report was rendered from cache - taken 5 min back.
I have SQL Server 2005 and I am using .Net 2.0. Is there a way to cache a query, basically I am going to do a SELECT * on a table that is called frequently. I then want to query this DataTable that is in cache. I want to invalidate the cache when the table changes. Everything I have read tells me to use the output caching directive. But, much of what I am doing doesn't end up in the output. Is there a way to invalidate the cache the same way without output caching and using the Service Broker?
Hi everyone!I tried to set my SqlDataSource's SelectCommandType to be a stored procedure. However the SqlDataSource failed to cache it. But if I just copy paste my stored procedure's content to my SqlDataSource's SelectCommand property, the cache just works. Is "StoredProcedure" as the "SelectCommandType" is not supported when caching the data? Or am I missing something here? Please help.
Hello all, I've got an application that calls a really simple stored procedure - just selects all records from the dbase. The problem is this seems to cache every now and then and as the same table is updated very frequently by other users this means the data returned isn't up to date. I thought it was Sql Server caching the results of the stored procedure, but I can do an iisreset and it will be up to date again. And unless I'm missing some point iisreset has no bearing on Sql Server. So is there an application data cache somewhere that I should be clearing to ensure the recordset is always up to date?
