.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Query Designer question

Posted By:      Posted Date: September 04, 2010    Points: 0   Category :Sql Server
I am not sure where to post this question, so I guess it is here: I have a VERY large query that joins quite a few tables. I am using the graphical designer to get the tables all linked together. However the graphical designer seems to place the order of the tables in a random order resulting in a hodge podge of left and right joins.   In the graphical designer, is there a way to order the from clause?

View Complete Post

More Related Resource Links

MDX query using linked server - performance question

Hi, I'm having a strange performance behavior. When I’m running a mdx query on management studio, it runs for 10 minutes (its mainly calc members on a large cube) When I run the same query via linked server and openquery, it runs for 20 minutes. Is the SQL from some reason does a parsing or running the query twice? The server is sql2008 UP1 on 64bit. and the "allow in process" is checked. its the default in sql2008 Thanks in advance, Yoav  

BIDS Query Designer very slow in VS 2008

I am in the process of upgrading my Report Server projects from 2005 to 2008.  These reports use an Analysis Services 2005 data source and have queries that are anywhere from 20 to 100 lines of MDX.  After upgrading a report, when I open or save a dataset in the Query Designer, it takes up to 20 seconds.  In 2005, this would happen almost instantly. Running SQL Server Profiler, I see that there is an extensive number (600+) of Discover events going on with the Analysis Services server while the Query Designer loads. My version of Visual Studio is 9.0.30729.1 SP and I am running Windows XP SP2. Thanks, Mitch

BIDS Query Designer very slow

BIDS Query Designer seems to be very slow while saving the query or opening the query designer when you've got quite a lot of Query Parameters. When I remove all the query parameters, the query designer seems to be running smoothly. Editing a query with about 10 to 15 query parameters takes ages this way! Is there something I can do about this?!

MDX works fine in SSMS but not in query designer

Hi all,   I am trying to include this query in my query designer in SSRS. The query below is as follows:WITH MEMBER [Time].[Fiscal Month].[All].[ Subtotal] AS ' AGGREGATE( EXISTING { [Time].[Fiscal Month].[All].CHILDREN }) ', SOLVE_ORDER = 1000  SELECT NON EMPTY { { { { [Time].[Fiscal Month].[All].CHILDREN }, { [Time].[Fiscal Month].[All].[ Subtotal] } } * { { [Measures].[SKU Distinct Count] } } } } ON COLUMNS , NON EMPTY { [Sales Force].[Retailer Code].[All].CHILDREN } ON ROWS  FROM [Sales]CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL The error i am getting isThe query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..Please let me know where am i going wrong.Thanks and regards,ram

Question about SQL query

Hello everyone, I am having a difficulty making a query. This is the situation: I have two tables: Persons and Work Persons: ID, LastName, FirstName Work: ID, PresonID, WorkType, Start, End WorkType in Work table is nvarchar type and Start and End is datetime type. There is 14 of possible combinations in WorkType I need to make a query to get all the persons and the summed number of minutes (DATEDIFF(minute,Start, End)) by every WorkType. Thanks in advance

SQL query question

I am struggling with a query from a table that looks like this:ID | Rep     | Account---------------------------1  | Bob     | Safeway2  | Bob     | Krogers3  | Chris   | Krogers4  | Maria  | Safeway5  | Sam    | AlbertsonsI am trying to query all of a rep's teammates across all accounts. For example, Bob works on both Safeway and Krogers accounts. I want a query that outputs all reps who work on those accounts. If Bob is the input, Bob, Chris, and Maria would be the output.My stab is:SELECT DISTINCT tblUserRelationships.RepFROM tblUserRelationships INNER JOIN vwRelatedAccounts ON tblUserRelationships.Account = vwRelatedAccounts.Accountwith vwRelatedAccounts looking like:SELECT        AccountFROM            dbo.tblUserRelationshipsWHERE        (Rep = N'bob')The problem is I can't paramatize vwRelatedAccounts and I can't figure out how to nest second statement into the first statement.Any suggestions?Thanks!Bradley

Query Designer - Pane - Results grayed-out?

In Query Designer, right click, select pane -- the option for Results is unavailable (grayed-out)?

SSRS Query Designer (in BIDS) does not generate ANSI SQL

Hello, I have a problem when using SQL Server 2005 BIDS (Business Intelligence Development Studio) when connected to an Oracle data source.  The Query Designer generates the old Oracle SQL syntax instead of ANSI SQL.  This is with the Oracle 10g client installed. So, I would really appreciate it if anybody knows how to get the Query Designer to generate the ANSI SQL.   Thanks, Stuart

Generic Query Designer displaying non standard syntax

I've just discovered a problem with the Generic Query Designer on my machine. When I do a simple join of two tabels it does not display in the SQL pane as standard syntax, so instead of SELECT a.1 from ainner join b on a.1=b.1It does SELECT a.1from a, bwhere a.1 = b.1I am doing this against an oracle 10g database, it will run the query, but I want the query designer to out put at standard SQLI am presuming that it is a setting in SSBIDS, but I don't know why it is doing this. Any suggestions?

Troubleshooting query question?

Hi All, We have below query which is working perfectly fine:-  ;WITH CTEA AS ( SELECT ....... FROM TABLEA LEFT OUTER JOIN TABLEB ON TABLEA.COLUMN1 = TABLEB.COLUMN2 ), CTEB AS ( SELECT ........ FROM CTEA -------------- record count of CTEA is 2 million records EXCEPT SELECT ... FROM TABLEC ), CTEC AS ( SELECT ..... FROM CTEB GROUP BY ........ ) SELECT * ----------------------- records count of CTEC is 30-40K records, execution time 2 mins FROM CTEC  But we have to do below query instead of "SELECT * FROM CTEC":- SELECT ......... FROM CTEA INNER JOIN CTEC ON CTEA.COLUMN0 = CTEC.COLUMN0 AND CTEA.COLUMN1 = CTEC.COLUMN1 AND CTEA.COLUMN2 = CTEC.COLUMN2 AND CTEA.COLUMN3 = CTEC.COLUMN3 AND CTEA.COLUMN4 = CTEC.COLUMN4 But above is not giving us any result for more than 30 mins, finally we have to kill the query, we tried it is not doing any cartesian product, so can anybody help us to troubleshoot where I'm doing mistake and what is wrong? Please feel free more questions for doubts, Also my 5 columns where I'm doing join operation are VARCHAR columns. Any Help ? Also let me know if their any other way to write above query instead of JOIN, main purpose is to reduce the execution time. Thanks Regards, Kumar

Adding a table or view multiple times when building a query with BI Report Designer and Report Build

How can I add a table/view multiple times to a single query by using BI Report Designer or Report Builder 3.0? In Report Designer I managed (workaround) to add one table multiple times by creating multiple Named Queries and having the same SELECT. Still I don't know how to add one table multiple times in Report Builder 3.0.

Report Builder 3.0 for Oracle with Graphical Query Designer

Is there a possibility to have Graphical Query Designer in Report Builder 3.0 in case the connection is made to an Oracle database (through .NET Provider for Oracle for example)? I need to offer to the end users the means to build their own reports. Every user has associated a group of database objects and as soon as users are added new database objects are added. Therefore I cannot create a model because it needs to be changed frequently and the model in this case would contain objects to which the user does not have access. The only option is that the user should create his report query in a friendly manner. So the query text editor is of no good in this case.   I've found in documentation that for Oracle and other databases there is no Graphical Query Designer: In Report Builder 2.0, to specify a query for data source types Oracle, OLE DB, ODBC, and Teradata, you must use the text-based query designer (http://msdn.microsoft.com/en-us/library/dd220607(SQL.100).aspx). Is there a way to have Graphical Query Designer in Report Builder 3.0 or should I look for other products? In case Report Builder cannot solve this problem can you make some product recommendations - ad-hoc web reporting for .NET?

General question: Using the VS Designer vs. the Source Code

I just finished Sams Teach Yourself ASP.Net in 24 Hours. I am now going through the Data tutorials here. I am a beginner at ASP.Net. One thing that I am having a little bit of trouble with is the idea that I am using the Designer in Visual Studio for just about everything except for a few code tweaks. I used to do a lot of HTML, JavaScript and CSS. I even did some programming (PowerBuilder). So, I am used to writing/typing code instead of using a Designer. (I always liked writing web pages in a text editor.) When I look at the code generated by the Designer in Visual Studio, I can understand what is going on there. But, to sit down and write that code instead of using the Designer to me seems impossible. There is just so much there. From what I understand, ASP.Net and Visual Studio were created to help streamline the code writing process. I suppose if I was used to writing VB code to create applications I might look at this from a different angle. But, I am learning ASP.Net using the VS Designer. Any comments or suggestions from experienced coders? I really don't want to have a battle between people who use the Designer and the Source Code editor. I am just hoping for some comments that will help newbies. Thanks.

Windows Designer Question


I am new to WPF and i have downloaded the trial versions of the Microsoft C# Express.
I created a new project and on the Windows i tried to drag and drop more than more control it would not allow me to.
But when i added a Grid Control it lets me add more than one control to the Window ? Why is this so ?

Also whenever i runt the application and return to the designer pane it shows me a message saying that "An assembly or related document has been updated
which requires the designer to be reloaded.Click here to reload". Can somebody explain this to me.

Any help is greatly appreciated cos i am just learning WPF.

Query Designer Toolbar


I have added the Query Designer toolbar to my Management Studio environment.  After open a new query window this toolbar stays greyed out.  How can I use this toolbar with my queries?


Mdx query question.



In the next query, I expected the same value for TotalSubCategory and Total1SubCategory calculated measures.

Why are these values distincts?





MEMBER [measures].[TotalSubCategory] AS ([Product].[subCategory].[All],[Measures].[Sales Amount])
MEMBER [measures].[Total1SubCategory] AS ([Product].[subCategory].[All],[Product].[Category].currentmember,[Measures].[Sales Amount])



   [Measures].[Sales Amount]

 } ON 0





   ) ON 1

FROM [Adventure Works];

Query Performance Question



I have a view , it gets the data from almost 20 joins , couple case statements and some subqueries and it is not materialized.

I am loading this view's data into a table so it takes 80 minutes to load with select into clause.

there are almost 100 milyon rows in that view.

here is the view , it will be great if I can take 80 min down to 20-30 min.

 , l =
  case when cd.a is not null then (select name from bbb where dd = caz.dd)
    when cd.a is not null then (select name from cn where d_id = caud.d_id)
    when cd.a is not null then (select name from cn where di_id = cap.d_id)
    else null
 ,m= case when cd.a is not null then (select name from bbb where dd = caz.dd)
    when cd.a is not null then (select name from cn where d_id = caud.d_id)
    when cd.a is not null then (select name from cn where di_id = cap.d_id)
    else null
  cd inner join  cpcs on
  cd.c = cpcs.c
 left outer ........
 left outer ........
 and cdid = (select id from c where class = 'bbbbe' and

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend