Solved

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

Posted on 2008-10-15
9
331 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
 
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
bank transaction downloader ? 1 63
SQL help 5 50
MS SQL page split per second is high 19 74
Is there any Easy way to copy CSV to SQL Server using C# 3 69
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Backup & Restore 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.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

919 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now