View Complete Post
I'm trying to execute the following:
WHERE STATE_CODE5 IN ('IL','NV','VA','NH','HI')
WHERE STATE_NAME8 = 'HAWAII'
ORDER BY STATE_CODE5;
In both 2005 and 2008 versions of the SQL Server the query parses ok, but on execute I get the following error:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
If I remove the the 'ORDER BY' clause, I am able to execute the query with no problem.
I've searched the forum and I know this error comes up often in various circumstances, however I didn't see anything similar to this. Please advise, maybe I'm writing the query incorrectly? Thanks
I have a query which has a paging functionality and for which i'm using ROW_NUMBER() function. The order by clause in my rownumber function is dynamic and gets changed all the time when ever the sp is called. It works fine for few columns but for one column
which is of datetime datatype, it takes lot of time. Amazing thing is with when i sort by the same column in descending order, the query runs in a sec but ascending order takes lot of time :(.
I tried creating a non clustered index(as i already i have a clustered index on that table) on that datetime column but it did not help me.
Could you please suggest what i can do to improve the performance.
We are migrating from SQL Server 2000 to SQL Server 2008 R2 and our web application having thousands of SQL Statement with "Column Alias in ORDER BY Clause ".It is very difficulty to change all
One of article says that SQL Server 2005/SQL Server 2008 not support
"Column Alias in ORDER BY Clause and we have to replace with column name" because of this issue If we change Alias name to column name, we have do enter application/integrity testing.
it is going to be big deal.
our client Decided to stop migration, Please some help is there any other alternative.
I have copied 2 queries and their execution plans below.first one taking long time to execute and the second one finishes with in a second.can u guys pls explain what is happening inside sql server engine.
haregistration r inner join
inner join screening_event se
on se.eventid=ses.eventid order by EventDate
|--Nested Loops(Inner Join, WHERE:([microsoft].[dbo].[HARegistration].[ScreeningSlotID] as [r].[ScreeningSlotID]=[microsoft].[dbo].[screening_event_slot].[SlotId] as [ses].[SlotId]))
|--Nested Loops(Inner Join, OUTER REFERENCES:([se].[EventId], [Expr1006]) WITH ORDERED PREFETCH)
| |--Index Scan(OBJECT:([microsoft].[dbo].[screening_event].[IX_EventDate] AS [se]), ORDERED FORWARD)
| |--Index Seek(OBJECT:([microsoft].[dbo].[screening_event_slot].[AK_screening_event_slot] AS [ses]), SEEK:([ses].[EventId]=[microsoft].[dbo].[screening_event].[EventId] as [se].[EventId]) ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([microsoft].[dbo].[HARegistration].[PK_HAR
i want create a UD Aggregate like Row_Number () for using with 'Over (Order By ...) ' (not like sum() over (part....)
is it possible?
I want to use two order by clause in one statemet or i just want to show one column in ascending order in other column in descending order without using T-SQL Statement
Please Suggest Me .
Thanks In Advance
Any ideas how I can order inside the subquery? I get the following error. I have tried ORDER BY MAX([Count]) but it doesn't like this much either... Also, I don't want to use TOP in the subquery as I need to use all the records!
COUNT(*) AS [Count]
GROUP BY System_User_ID
ORDER BY [Count]) l
INNER JOIN aspnet_Membership m
ON CAST(m.UserId AS VARCHAR(36)) = l.System_User_ID
OK. Enough complexity in the subject line, here are the details
I have a table with many columns, two of them are ManagerId INT and DesignationCode VARCHAR(30), Now the following query
SELECT EmpId, EmpName FROM Employee WHERE DesignationCode='SWENG' AND ManagerId=3
A friend of mine has been told by an MVP that in this case integer comparison should be the first one in order and varchar should be the second to make it perform a lot better.
I tried googling for 15 min, found no evidence. Do you know of any performance suggestion ?
My friend says that his MVP friend says that SQL Server's engine is designed to process it faster this way. So the WHERE clause should be changed to WHERE ManagerId=3 AND DesignationCode='SWENG'
Do you guys know anything about it ?
Is it possible with SQL sorted in descending order according to the word?In Table I shrajeno Mastercard, Visa, Diners etc ...All Visa are the first in the order, another Mastercard, etc. ...Something does not work: ORDER BY PaymentType = 'Visa';I would not want to work in one field PaymentId (111,222,33, etc. ..) and then use the ORDER BY.Is this possible in such a way?
I have never tried a group by with linq, now I do and I'm stuck.
I try to convert this sql statement to its linq counterpart :
SELECT COUNT(tb_Mailer_Smses.Id) AS [cnt], tb_Mailer_mailing.Id As [Id]
,SUM( CASE WHEN tb_Mailer_smses.DLRStatus = 200 THEN 1 ELSE 0 END) As [Delivered]
,SUM(CASE WHEN DLRStatus BETWEEN 300 AND 499 THEN 1 WHEN DLRStatus BETWEEN 800 AND 899 THEN 1 END) AS [Errors]
FROM tb_Mailer_mailing INNER JOIN
tb_Mailer_Smses ON tb_Mailer_mailing.Id = tb_Mailer_Smses.MailingId
WHERE tb_Mailer_mailing.ApplicationId = 1
GROUP BY tb_Mailer_mailing.Id
Is there some one who can me show me the convertion process ?
Edited by Nancy Michell
MSDN Magazine May 2005
MSDN Magazine January 2004
MSDN Magazine March 2002
MSDN Magazine February 2002
XML has quickly become the new data structure standard for everything from database access to cross-platform computing. XML is typically considered to be a vehicle for data exchange, dynamic data presentation, and data storage. However, the potential of XML far surpasses those limited applications. This article examines one new use: the gathering of data across a number of forms in an ASP.NET Beta 1 framework application. The sample program is a Web app used for ordering pizza. It uses ASP and C# to gather order information and then stores it in XML. To build the application, several concepts are explained, including data collection, order persistence using cookies, grouping input forms, and formatting the data for display.
MSDN Magazine September 2001