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

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

SQL gets corrupted with 'WITH' command and rubbish when editing query containing derived table

Posted By:      Posted Date: September 24, 2010    Points: 0   Category :Sql Server

When my colleague makes any change to a query containing a derived table, the word 'WITH' followed by a lot of graphical characters appears after the alias, rendering the query unusable.  We have tried this with various existing and new, simple queries, all to no avail.  We are editing the queries in Visual Studio 2003, and he does not get this effect when using Visual Studio 2005.  I can edit the query in 2003 on my laptop with no ill effects.





View Complete Post

More Related Resource Links

Avoiding a SELECT distinct query generated by SSAS when using dimension derived from fact table

Hi, I am using a dimension derived out ot a fact table and the factt able primary key is dimension key. Issue is, there are large number of rows and so many attributes. SSAS issues distinct query and it takes large amount of time. Without the distinct statement, query takes only 3 min for 4 million rows. With the distinct, it takes 20 min. Becuase the fact primary key is the dimension key there is no need of a distinct statement. I know there is a option in the dimension to say "By Table" to avoid this. But unfortuantely, i breach the 4 GB limit for strings. Any suggestions for optimization? Thanks,  Sambath

CTE Query as Derived Table


Can a query with CTE be used as derived table? The following attempt fails. Thanks.

WITH CTE AS (SELECT ProductName=Name, Price=ListPrice, ProductID
       FROM AdventureWorks2008.Production.Product)

Kalman Toth, SQL Server & Business Intelligence Training; SQL 2008 GRAND SLAM

convert query to derived table


I have the query like that:

SELECT orderid, orderdate, custid, empid,
		  ROW_NUMBER() OVER (PARTITION BY orderdate ORDER BY orderid) rownum
FROM [TestSQL].[Sales].[Orders]

How can I do it with derived table?



Is there a work-around to create indexes for a view defined by a sub-query, derived table, or CTE?


I have three versions of an indexed view I'd like to create. I can create the view, but cannot create a clustered index on (CollectionID, ElementID, TimeID) because the definitions use variously a sub-query, CTE, or derived table, which SQL will not allow.

Has anyone figured a workaround for this? Any suggestions much appreciated.



VIEW [dbo].[aonCompositeFactView] WITH

lookup table query


we are doing our cms migration. both cms's have their own articleID/contentID, we have a lookup table that contain the article URL and its content id.  Now we want to query that table on every single click to get its respective id. 

How and where i can implement this?

Query Command to Report


I would like to generate a crystal report showing the report data based on my custom SQL query.

Where I can provide my SQL query or stored procedure.

Please give me good tutorial links on this too.

Thanks in advance.

Create Table from MDX query

Hi,I want create a table from the result of the MDX query. When I finish the wizard of import data in SQL Server Environment. at the last stage, the error is : Pre-execute (Error) Messages Information 0x4004300b: Data Flow Task 1: "component "Destination - Query" (31)" wrote 0 rows.(SQL Server Import and Export Wizard) please guid  me.Thank you a lot.Naeimeh

Application to Constantly query Table Values and Refresh with most recent value

Hello, I must apologize in advance because I am not sure if this is the correct place for this question. I am looking for ideas on how to build an application that in the most basic sense contains a field with the most current data from a table in a SQL Server database (this would be based off of a datetime timestamp in the table). I am new to this kind of problem and am unsure where to start looking for solution ideas. The table value updates variably and could possibly update every second to every hour. I would like to design an application that would be lightweight to both the server and the user's machine. This would take the place of an unneeded report on my Reporting Services server. Any and all suggestions would be welcome because I am not sure of options I even have on this. Thanks in advance for your time and consideration, Matt

Transact-sql - tricky query to get a list of people from a table where i belong too.

I've no idea how to manage and optimize this query: I've four table. - User (userId, name) - Member (memberId, name, ownerId) The ownerId = user.userId - PresentPeople (activityId, presentId, ...) so presentId = memberId - Activity (activityId, ownerId, startDate...) and ownerId = user.userId So here for an user, I need members created by this user, and for this members I need the list of presentPeople for the activity that theyjoined. But I don't know how to get this list because they are in the list. Not sure if i'm clear there, Big help would be very helpfull :)) thanks

can't execute "create table" command with Visual Web Developer express

I have a folder with a web site I'm developing which is connected to a database.Every time I move the folder to a different computer it's a pain when I have to move the database and change the connection string.I've thought to use for the first time a database in the app_data folder and move all the database inside it.Nevertheless, when I try to recreate all the tables in the new database for the first time with the query "create table ..." I get and error saying that "SQL CREATE TABLE" is not supported.Is there a way to manage this situation?--   Giuseppe

Access the SQL Command Query Value Property from the OLEDB Container within a Script Task

Hello, I'd like to Access the SQL Command Query Value Property from the OLEDB Container within a Script Task so I can audit the queries which are executing. What is the syntax for this or does anyone have a sample on how to do this? Thank you

CQRS: Command Query Responsibility Segregation

Jason Gerard and I were talking about document databases the other day, and he said "Have you heard about CQRS?"  This was news to me, so I started doing some research.  Wikipedia, which usually provides good information, said the following "CQRS is simply the creation of two objects where there was previously only one. The separation occurs based upon whether the methods are a command or a query (the same definition that is used by Meyer in Command and Query Separation, a command is any method that mutates state and a query is any method that returns a value)."Ok, that was clear as mud. Maybe a picture will help.  This diagram is from Udi Dahan's excellent presentation on CQRS at the London .NET User Group.  Udi is an amazing presenter & his presentation is well worth the watch.  He is also the guy behind NServiceBus.Here are the basics as I understand them:In a multi-user environment, data on the screen is always stale. Due to this fact, we don't need a complicated ORM to pull "live" data out of our OLTP database.  Instead, we can use a persistent view model to pre-cache the data needed by the UI.The user interface needs to capture the user's intent, not just their input.  It can then build up commands that are submitted asynchronously to the services layer.  This is a more imperative way of doing things and provides the oppo

sql server query get a list of a table

Hi guys, I've a hard query to manage ... I need to get for a report the number of required people to an appointment, but this query works but just for the regarding person related to the appointment. SELECT CRMAF_FilteredContact.OwnerID ,CRMAF_FilteredContact.OwnerIDname ,SU.businessunitidname ,SU.eu_reporthubname -- ,SU.eu_reportcountryidname ,CRMAF_FilteredContact.ContactID ,CRMAF_FilteredContact.FullName ,CRMAF_FilteredContact.invoke_tiername ,CRMAF_FilteredAppointment.ActivityID ,CRMAF_FilteredAppointment.RegardingObjectID ,CRMAF_FilteredAppointment.Subject ,AP.ActivityPartyID ,AP.PartyID ,case when CRMAF_FilteredAppointment.ActivityID is null then null else CRMAF_FilteredContact.ContactID end as s_contactsvisited -- # of contactsvisited FROM FilteredContact CRMAF_FilteredContact JOIN FilteredSystemUser SU ON CRMAF_FilteredContact.ownerid = SU.SystemUserID <strong> LEFT JOIN FilteredActivityParty AP ON ( AP.PartyID = CRMAF_FilteredContact.ContactID or AP.PartyID = CRMAF_FilteredContact.OriginatingLeadId) AND AP.participationtypemask = '8' -- regarding</strong> LEFT JOIN FilteredAppointment CRMAF_FilteredAppointment ON AP.ActivityID = CRMAF_FilteredAppointment.ActivityID AND CRMAF_FilteredAppointment.ownerid = SU.SystemUserID AND CRMAF_FilteredAppointment.stat

How to recover from Derived Column Transformation Editor corrupting table metadata?

Hi there, In attempting to use the DCTE to replace the value in a field with a trimmed version (SSN = trim(SSN)), it seems that my meta-data has become corrupted by the Derived Column drop-down list. As a result, #1: I no longer see my incoming SSN field in the "columns" tree, and any reference to it is deemed "invalid", even though it's value does make it out of the Data Transformation process. How can I get back the reference to this field without redoing this entire task? And, #2: in the process, DCTE created new columns with "SSN" prefixed by the task name, such as "trim character fields.SSN". How can I delete these? It seems that one slip of the mouse in this form can lead to irreversible corruption of the meta-data, which the "debugger" then references and uses to "invalidate" subsequent work. I have tried everything I can think of to refresh this, including using the "Advanced Editor" and reloading the entire package. Any ideas? Thanks, Karl Kaiser

Efficiency: new column in source query or derived column task?

Hi All, I've just started working on an SSIS package that pulls data from an OLE DB Source by a query. A new column needs to be added based on the value of a queried column. I was wondering if it's better to do that in the query or with a derived column? A simple example: I have a table that contains CustomerName and CustomerCode (this one can be V /valid/ or I /invalid/). I need to store the CustomerCodeDesc in a separate column in the destination table. Is it better to alter the query like this: SELECT CustomerName, CustomerCode, CASE WHEN CustomerCode = 'V' THEN 'Valid' ELSE 'Invalid' END AS CustomerCodeDesc FROM CustomerTable Or is it better to use a DerivedColumn task in the DataFlow? Or maybe it doesn't really matter...

T-SQL 2005 table query

Hi, I have SQL 2005 table like below one declare @OrderTable TABLE (OrderID varchar(10),OrderValue decimal(10,2),OrderDate DateTime) INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',25.22,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',455.85,'2010-01-24') INSERT INTO @OrderTable VALUES('P06',866.45,'2010-01-25') INSERT INTO @OrderTable VALUES('P06',749.61,'2010-01-28') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-02-23') INSERT INTO @OrderTable VALUES('P01',755.61,'2010-03-23') Final result I am looking for is SET NULL value in SELECT query Whenever Same OrderID and OrderDate is found... OrderID  OrderValue   OrderDate P06         25.22   2010-01-24 P06        NULL             2010-01-24 P06      NULL             2010-01-24 P06        NULL             2010-01-24 P06      NULL              2010-01-24 P06 &

Tricky SELECT query from a Single Table

Hi I have a 2 rows of data in a table as mentioned below   Table1             ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 NULL 40 NULL 20 4 9 2010 400 NULL 200 NULL  I need a SELECT query to get the output in a single row as ElementID Month Year Planned Cost UnplannedCost PlannedExpense UnPlannedExpense 4 9 2010 400 40 200 20 Could anybody help me in writing a query for this? Thanks
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