asked on

Creating a Report Model locks up or get a Timeout message out when getting counts

When creating new Report Models in VS2005, the Report Model Wizard gets to the point where it is states that it is "Calculating column uniqueness" and it either gets locked up there or we get a message stating that the query timed out and it shows this huge query where it is counting a bunch of records from one of the tables from the Data Source View. I need to figure out how to get around this problem and move forward with creating models.

The actual error message states:

An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Command:
SELECT
      COUNT([Computed_DryHoleCost]),
      COUNT(DISTINCT [Computed_DryHoleCost]),
      COUNT([Computed_WellworkCost]),
      COUNT(DISTINCT [Computed_WellworkCost]),
      COUNT([Computed_TotalCost]),
      COUNT(DISTINCT [Computed_TotalCost]),
      COUNT([Computed_CompletionCost]),
      COUNT(DISTINCT [Computed_CompletionCost]),
      COUNT([Computed_FinalDrillingCosts]),
      COUNT(DISTINCT [Computed_FinalDrillingCosts]),
      COUNT([Computed_FinalSuspendedCosts]),
      COUNT(DISTINCT [Computed_FinalSuspendedCosts]),
      COUNT([Computed_TotalActualCosts]),
      COUNT(DISTINCT [Computed_TotalActualCosts]),
      COUNT([Computed_ActualDrillingCosts]),
      COUNT(DISTINCT [Computed_ActualDrillingCosts]),
      COUNT([Computed_ActualCompletionCosts]),
      COUNT(DISTINCT [Computed_ActualCompletionCosts]),
      COUNT([Computed_ActualWellworkCosts]),
      COUNT(DISTINCT [Computed_ActualWellworkCosts]),
      COUNT([Computed_FinalWellworkCosts]),
      COUNT(DISTINCT [Computed_FinalWellworkCosts]),
      COUNT([Computed_FinalCompletionCosts]),
      COUNT(DISTINCT [Computed_FinalCompletionCosts])
FROM [dbo].[AFE] t

James Murrell

http://www.ironspeed.com/designer/5.0.1/webhelp/Part_VI/Timeout_expired_The_timeout_period_elapsed_prior_to_completion.htm might help

bbaldwin

ASKER

I looked for a timeout parameter but cannot find one. Someone had suggested that it was in the Data Source but I could not find it there. When you create a Model - you just 1. Create the Data Source. 2. Create the Data Source View which is just like creating a view in SQL. and the 3. Create the Model and don't see any timeout parameters to set.

James Murrell

on server

In the Management Studio, from the Tools menu > select Options, then click "Designers". There is an option called "Override connection string time-out value for table designer updates:" In the "Transaction time-out after:" box, you will see the magic 30 seconds.

bbaldwin

ASKER

I found an option there in the Business Intelligence Designers (only there if you install SQL tools) and there was a timeout setting of 15. I raised it to 600 and am trying again. It takes a long timeout for the other one to timeout. I am interested to see how long this takes.

bbaldwin

ASKER

Unfortunately, this did not help. Suggestions?

ASKER CERTIFIED SOLUTION

James Murrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bbaldwin

ASKER

The database does not have a log since we have it set to "Simple".

bbaldwin

ASKER

We just started creating the models against a database with a small number of records and everything started working. I will give you the points for trying. Thanks!

bbaldwin

ASKER

We ultimately just ran the model against a database with a small number of records and everything started to work.