Solved

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

Posted on 2008-10-15
9
334 Views
Last Modified: 2011-10-19
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
0
Comment
Question by:bbaldwin
  • 6
  • 3
9 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 22723791
0
 
LVL 2

Author Comment

by:bbaldwin
ID: 22723827
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.
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 22723949
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.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 2

Author Comment

by:bbaldwin
ID: 22724031
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.
0
 
LVL 2

Author Comment

by:bbaldwin
ID: 22733934
Unfortunately, this did not help. Suggestions?
0
 
LVL 31

Accepted Solution

by:
James Murrell earned 500 total points
ID: 22734054
just seen this @ http://www.windows-tech.info/15/b8b48048e18d376e.php
try with this code before your query

Code Snippet

Your Query
go
dump tran "You database" with no_log
0
 
LVL 2

Author Comment

by:bbaldwin
ID: 22734205
The database does not have a log since we have it set to "Simple".
0
 
LVL 2

Author Comment

by:bbaldwin
ID: 22754773
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!
0
 
LVL 2

Author Closing Comment

by:bbaldwin
ID: 31506410
We ultimately just ran the model against a database with a small number of records and everything started to work.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question