I have a question regarding a proble with two dimensions I built.
The first is named Account and contains approx 40k records. The second dimension is named Contact and contains the emps from the Account dim and contains approx 58k records. In the cube I also have two measures, One is a count of courses a Contact has taken. The second measure is a count of certifications a Contact may have earned.
The Account dim table has an AccountKey primary key and the Contact dim table has a ContactKey primary key and an AccountKey foreign key to the Account table. The key fields are not operational keys. They are surrogates. Both Contact and Account dim tables have as the first 10 or so records values that are used as parent groupings in the Cube dimension. For instance.
key = 1, name value = 'A-C'
Each proceeding value has the parent grouping's key value set as its parentkey.
The fact table contains both the AccountKeys and ContactKeys and an ItemId that corresponds to a specific course or certification. This itemid is used for the measures in the Cube
That's the background... here's my problem.
Using BIDS or Mgmt Studio, whenever I add the root dimAccount level (actual account names) as a row and then add the root Contact level (Contact names) as another row and drill down to a specifc Accounts contacts, everything locks up. I have only one measure in the data pane. The fact table only has about 20k records in it. I would think this should return data instantly. If I browse the cube with any other comination of dimensions besides the Contact and Account dimensions, the cube runs fine. It is just the combination of Account and Contact. I am getting really frustrated as I cannot figure this out.
I am rusty at SSAS so forgive me if I left out any pertinent info.
Hi I have a query which executes in 1 second and returning 14 rows whenever I execute it through Management Studio but when I execute it from a web page I am getting a timeout message and the stack trace is pointing towards the line which calls the stored procedure. The query is quite complex and there quite a few joins on tables and views including one to a lookup table, whenever I take out join onto the look up table the web page runs ok without timing out. I join the Lookup table (Parts) using a Left Outer Join similar to below. The syntax of the Join is fine but I cant find why it would cause the query to time out when executed from a webpage but is fine when I execute it from Management Studio select * from tables/view LEFT OUTER JOIN Parts on Parts.PartNumber=tables/View.PartNumber I've tried recompiling the Parts table but it didnt make any difference. Any help would be much appreciated. Cheers, Frankie
I use SQL Server to provide data to asp web pages and have recently started to get ODBC time outs throughout the day.
The environment is as follows: Server with dual PII processors & 512MB RAM running: - SQL Server 7 - IIS
I have a number of asp based web sites hosted on this box, but only 1 of them seems to be affected by the time out problem. I have checked the resources on the server (NT Task Mgr - Memoey & Processor) and everything seems fine - in fact the resources are hardly being touched!!! Within a few minutes the problem disappears completely without me doing anything.
Am I missing something here? Should I look elsewhere other than SQL - maybe IIS ??? Any suggestions / pointers would be very much appreciated.
We are using asp's and tables sucessfully but when we click a link to an exe on a page it is slow, to the point of timing out. Very slow. Any help would be appreciated. Email me tperry@kpmg.com
What could be the reason for my view to timeout? I thought it was because of the number or records, but i guessed wrong. The view is grabbing data from a UDF i have created.
I am trying to use statistics to get the time it takes to run a sql function. When I use SET STATISTICS TIME ON it returns multiple results (one for each insert statement in my loop). Is there any way to get results for the ENTIRE function? Here is the loop that I am timing.--> (It simply populates a calendar table)
SET NOCOUNT ON DECLARE @Counter INT DECLARE @ActualDateDATETIME DECLARE @FirstDateDATETIME SET @Counter = 1 SET @FirstDate = '1/1/1900' SET @ActualDate = @FirstDate WHILE @Counter < 43830 BEGIN INSERT INTO Calendar(ActualDate) values(@ActualDate) SET @ActualDate = DATEADD(day, @Counter, @FirstDate) SET @Counter = @Counter + 1 END
Can anyone tell me as to how I can Timeout a transaction. For example I have transactiopn that pulls in data from a remote source. So if the time of extraction exceeds sometime say 1hr..I need to rollback the transaction.
Cannot use Set Lock_Time out because it can be used only for timing out a waiting process. Here I want to timeout the executing process
Hi. I'm new to SQL Server programming. I'm using it at my new job for the first time and catching on quickly. I've run into a problem where I run a request via interactive query and it times out. It's really annoying because it seems to happy a lot. Is this just a memory issue with my machine (it has 2.8 GB of RAM) or there a way around it?
Hi all,i have 97 honda CR-V with about 100,000 KM on it. i checked a doc onHonda web site and it said that 60,000 miles is point to change timingbelt. compared with other Honda cars, it is pretty low. is it correct? ifyes, do you know the reason??thanks for your help,jj
I wrote a query using the query analizer and tested it before turning it into a Stored Procedure. It worked fine an the execution time was acceptable (25 secs, since there was a lot of data to analize)
When I executed the recently created stored procedure, the execution time happened to be three or four times higher. (1 min, 38 secs)
It was the exact same code, i was logged in the same database server, the parameters were the same in btoh cases. So, my question is as follows:
Why is it that executing a script and executing a stored procedure with the exact same script differ so much in timing?
I am creating an index on a table wit 35 million records but I get the error 'TT_ObjPerformance' table- Unable to create index 'IX_TT_ObjPerformance_CACode'. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. How can I get the index created? ThanksSQL Server newbie
I have a site that is experiencing the issue described in this doc. I'm unsure if the fix is going to blow anything up as I am unfamiliar with anything they are recommending.
http://support.microsoft.com/kb/931279
exec sp_configure 'affinity mask', 0x00000003 GO reconfigure GO
Hopefully, this is an easy. Does SQL Server use the Windows system dateand time? Or does it keep track of what day/time it is on its own?We were just discussing this in the office today, because we'replanning for the changes in Daylight Savings Time in the United Statesthis year. It's going to start a month earlier and end a month laterthan it used to. Microsoft is apparently putting out patches forWindows so the OS will know to adjust the time by an hour on the rightdays, and we're trying to determine if our SQL and Oracle databases aregoing to be affected at all.--Richard
I wrote a simple VB.NET utility program that uses a While...End While loop to go through all of the records in a table. Each time throgh that loop, an UPDATE command is assembled and assigned to the .CommandText property of an SqlCommand object. The command updates a record in another table by an ID field--based on the contents of the first table as well as the result of a lookup in another table. The update command is then executed each time within the loop via the .ExecuteNonQuery() method of the SqlCommand object. Again, it's important to point out that the update command updates only *one* record each time it is executed.
This program had been working fine until yesterday--when the query began timing out on the .ExecuteNonQuery() command. I have a counter within the loop and have been able to determine that the timeout occurs on the 3,359th time the query is called. The specific message is:
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Re-running the program causes the same result at the same record.
I copied the query (whose ID updates the same 3,359th record) and executed it in SQL Server Management Studio; it timed out there, too. When I set the "Execution time-out" value to 0 (i.e., unlimited), the query ran for over an hour before I manually canceled it.
I can update all other tables in this database. I can also view (i.e., SELECT) the record that the update query is timing out on. I can also update any of the first 3,358 records in the SQLMS. However, the query continues to time out on the 3,359th record--and on any other record after that point.
The program was written on, and is running on, the SQL Server computer. The server has plenty of drive space. Both the database file and the log file for this database seem properly set to autogrow. I cycled the MSSQLSERVER service, but the query still times out at the same point. Rebooting the server did not resolve the problem, either. I don't see any errors in the Windows event logs.
What other methods can I use to try to resolve this problem?
At one seemingly inoccuous step in my CLR stored procedure, execution stops and the query times-out.
I've tried debugging the stored proc by stepping into it from within VS. When I do, I get to the code in question, but then simply get this message:
WARNING: Debugger was accessing T-SQL variables while managed code was not suspended. Waiting until the access is done to continue T-SQL execution. Continueing T-SQL execution.
And these messages appear to repeat indefinitely. I'm running SQL Server locally on my machine, but this also happens on out development SQL Server server.
The place in the code it appears to happen is when returning back results from a lower-level CLR stored proc called within the higher-level CLR stored proc -- when piping the result set, I suppose.
This is to do with HTTPS merge replication. We were getting an error when downloading the initial snapshot. The snapshot was about 25 meg, over an average link. It returned an error after a few minutes - it did start to download the .cab file (it was compressed).
By not compressing the cabinet file, it worked fine - I can only presume it was some sort of timeout error. The downside was that the amount of data was much greater, with it not being compressed.
Questions therefore are: a) is this time of timeout a known condition and if so what can I do about it
b) slightly unrelated- can an alternate location be given for the snapshot, like you can in non-HTTPS anonymous subscriptions.
Main properties in creating the subscription is as follows:
We have a problem with one of our MS SQL 2000 databases and some stored procedures.
I'm not sure exactly what the problem is, but these are the symptons....
The stored procedure runs without problems for a period of time. Abruptly, without warning it begins to time out when called from our web application.
Calling it through the query analyzer it runs within a second.
Forcing the stored procedure to recompile allows the web application to start calling it again without it timing out.
We have a DTS package that runs over night and imports a number of records (not sure on the exact numbers, but definately enough to make a difference to indexes) so this could be part of the problem although when I force a recompile I do not do any update stats or anything else.
I wrote a test script to call the stored procedure when it was timing out to ensure it wasn't a web application problem and the procedure continued to time out until the forced recompile. So I don't think the problem is there.
The stored procedure returns multiple results sets and when it starts timing out it is while it is returning the second results sets.
The code for the second results set is...
Select avg(round(p.PricingValue, 5)) as Average, stdev(round(p.PricingValue, 5)) as StdDev, min(p.CaptureDate) as FromDate, max(p.CaptureDate) as ToDate From Pricing p Inner Join Security s On p.SecurityID = s.SecurityID Left Outer Join Issuer i On s.IssuerID = i.IssuerID WHERE p.PricingTypeID = @PricingType And p.TenorTypeID = @TenorType And p.CaptureDate Between @DateFrom And @DateTo AND p.SecurityID IN ( SELECT SecurityId FROM UserResult ur WHERE ur.UserResultSelected = 1 AND ur.UserID = @userID )
Does anyone have any idea what might be going on here?
I have an SSIS package that takes data from a table in Access and puts it into a fact table in SS2005. Very little data manipulation is done. It processes approximately 1.5 million rows when it runs weekly. The process is run in an SSIS package that is called by a parent package, and all of that (including the use of the config files and accessing the parent variables) is working fine.
The issue is there is one field in the Access table that must be put into a different SS2005 fact table.
When I run the data flow task that loads the first fact table, it completes in less than two minutes. However, if I either (a) put a multicast step in the dataflow task to redirect a copy of the key data and remaining field to the second fact table, or (b) copy that step in the package to have it perform the same tasks with the different target (and using just the key and the remaining field), the execution time suddenly jumps to 30 minutes. In the case of (b), it remains true whether the copied step remains in the package or is executed in its own package, and also remains true if the package is loading against a table that starts out empty or with data already in it.
Has anyone ever bumped into a situation like this?
We are trying to troubleshoot some website performance issues and found some queries taking 2 to 3 seconds when the request comes from the web, and captured by the Profiler.
The same queries, when run in the Query Analyzer take 0 seconds.
What could be the reasons for this difference, I mean why it takes 2 - 3 seconds shown by the Profiler, when it's 0 second in Analyzer?
I am using a sql server 2000 database to log the results from a monitorthat I have running - essentially every minuite, the table describedbelow has a insert and delete statements similar to the ones below runagaint it.Everything is fine for a few weeks, and then without fail, all accessesto the table start slowing down, to the point where even trying toselect all rows starts timing out.At that point, the only way to make things right that I have found, isto delete the table and recreate it.Am I doing something specific that sql server really doesn't like? Isthere a better solution then deleting and recreating the table?CREATE TABLE [www2] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[stamp] [datetime] NULL CONSTRAINT [DF_www2_stamp] DEFAULT (getdate()),[success] [bit] NULL ,[report] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[level] [int] NULL ,[iistrace] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOINSERT INTO [www2] ([Report],[Success],[Level],[iistrace],[Stamp])VALUES ('Error on: <ahref="http://www2.klickit.com/include/asp/system_test.asp">http://www2.klickit.com/include/asp/system_test.asp</a><br><br>The operation timedout<br><br>(Test Activated From: Lynx/2.8.2rel.1libwww-FM/2.14)',0,1,'',getDate())DELETE FROM [www2] WHERE (Stamp<getDate()-3) AND (Success=1) AND (ReportNot Like 'ResetThanks in advance,Simon Withers*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
The first time I run it in the SQL query analyzer it sits there for about 30 seconds and then gives me "Timeout expired (error - 2147217871)." After the first query attempt I can run it as many times as I want and it will work fine (no errors) ... But if I wait for about 30 minutes and then try it again, it will give the error again just on the first try. I've tried using search words that exist and ones that don't exist in the db and they both give the same error, so it's not that it's trying to return too many rows.
I'm using Microsoft SQL Server 2005. The code I'm writing is pretty basic so maybe it's the way that the database is set up or the way I initiated the full text tables? Any help would be greatly appreciated. Thank you.
I have a SQL Server database running on a local PC which will eventually be scaled up once everything is working.
The Database takes data from an Access database, then the SQL Server aggregates this data into several other tables.
I have used a trigger to run this in SQL Server, once a table in SQL Server is appended with a specific value.
I have tested the trigger to do a simple task, and this works.
I have tested the aggregation query which create 18 seperate tables as well. It takes around 25 minutes to run. These are huge tables
When I use Access to append the final value to start the SQL Server trigger it freezes and eventually times out. I assume this is because it is running the 25minute trigger, and Access has to wait until this is completed before it can proceed.
I was hoping it would trigger SQL Server to run the trigger, then Access could go off and do something else!
I am running some new queries in SQL 2012 (in SSMS) and, while slow, they do run. If I try to use the same query to create a view it persists in timing out in about 30 seconds. I see very little on this subject via google.
I'm running into a problem with Full-Text searching. I have a procedure which uses a full-text search. When I run it in SQL query analyzer €“ it runs immediately. I exec this procedure from my ASP page and it returns timeout error. After the first query attempt I can run it (executing the ASP-page) as many times as I want with different search words and it will work fine (no errors) ... But if I wait for about 30 minutes and then try it again, it will give the error again just on the first try. I've tried using search words that exist and ones that don't exist in the db and they both give the same error, so it's not that it's trying to return too many rows.
I'm using Microsoft SQL Server 2005. Any help would be greatly appreciated. Thank you.
I cannot understand how this was allowed to happen, but I can recreate the situation all day long. Someone, please tell me that I have missed an obvious solution to this;
With a fresh install of SQL 2005 Express (SP2) I have 4 databases installed and two service-based applications running. One of the service applications uses/requires three of the four databases and the second service application uses/requires the fourth database.
On boot, with the above described services set to depend on SQL Server service startup I get repeated failures on initial database access. Looking at the event log reveals why (in sequence):
SQL Server Startup...
Event: Server local connection provider is ready to accept connection on...
Event: Server named pipe provider is ready to accept connection on...
Event: Server is listening on [ 'any' <ipv4> 1911]
Application Service Startup...
Event: SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again
Moments Later:
Event: SQL Server is now ready for client connections. This is an informational message; no user action is required.
Now, its clear that the SQL service has started, and that this opens the floodgates for dependant services to start, each of which is told - essentially - to go away and come back once the SQL server has **really** started. With the accompanying suggestion to 'look for the informational message that indicates that SQL Server is ready' in the event log.
Am I reading the situation right so far?
In response to this, with my developer way of thinking, I could probably script the startup of application services rather than have them start automatically at boot or some such insane idea. But its not really the way that I had planned to spend the next few hours of my life, and it seems to me that I should not have to do such a thing.
Our in house service application (one of the two described) is in fact robust enough (just enough) to survive and to try the connection periodically, accomplishing a successful startup even with no database available.
The second of the two services is external and is unfortunately unable to start with no database available (Business Objects XI CMS). The normal means of making this service robust against database outages is the service restart procedure setting. But since the service doesnt ever start successfully, this doesnt work (learn something new every day - the restart is literal. It does not provide an initial startup retry and the service must have been started and fail for the retry to kick in)
Now, im not trying to bash anyone over this situation and regardless of the inflexible nature of the external service application and the general sillyness of the SQL server startup sequence - i mean c'mon... we are started but not ready? How about dont report startup until after you are ready? - the end result is that I need to come up with a solution to this problem.
I cant imagine that this is the first time that this behaviour has been questioned and I have to believe that there will be a simple, reliable workaround. I will keep looking for myself, but if any of this sounds familiar and you have a suggestion, your response will be greatly appreciated.
