ORDER BY clause exceeds 8000 bytes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ORDER BY clause exceeds 8000 bytes

Author

Topic

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-19 : 08:27:51

I am getting this error message while executing the below mentioned query. If I remove the order by clause. It runs fine. But when I adds order by it fails and give the error message.

How to fix this.???

Warning: The query processor could not produce a query plan from the optimizer because the total length of all the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes.

SELECT 0 as [id],
replace(pm.vendname,' DO NOT PAY','') as [Participant Name],
case isnull(FRSTNAME,'') when '' then substring(replace(pm.vendname,' DO NOT PAY',''),charindex(' ',replace(pm.vendname,' DO NOT PAY',''))+1,len(replace(pm.vendname,' DO NOT PAY','')))
else FRSTNAME end as first_name,
case isnull(LASTNAME,'') when '' then left(pm.vendname,charindex(' ',replace(pm.vendname,' DO NOT PAY',''))) else LASTNAME end as last_name,
'Approved' status,
zs.vendorid
FROM zsvendor zs join pm00200 pm on zs.vendorid = pm.vendorid where zs.vendorid
not in (select vendorid FROM ref_participant
where id_org = 1 and approved_status = 1
)

order by [Participant Name]

Karthik

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-19 : 08:36:00

is pm.[vendname] huge?

If so -- order by something else or


ORDER BY
    LEFT(100, [Patricipent Name])

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-19 : 08:40:25

it still gives the same error
ORDER BY
LEFT(100, pm.vendname)

The pm.vendname field datatype char 65

Karthik

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-19 : 08:45:03

I guess you must have some value in one of your returned columns that is huge.

Does it make a difference if you order by something else? like vendorID?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-19 : 08:47:19

No I change it to First_name and last_name it gives the same result. only if i remove the order by it works fine else it fails.

Karthik

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-03-19 : 08:59:08

What happens if you add


option(ROBUST PLAN)

to the end of your query?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-19 : 09:24:21

It returns the same result when I add this also.
option(ROBUST PLAN)
Any Idea???
Karthik

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-19 : 10:10:29

Replace this line of code

replace(pm.vendname,' DO NOT PAY','') as [Participant Name],

with this:

convert(varchar(65),replace(pm.vendname,' DO NOT PAY','')) as [Participant Name],

CODO ERGO SUM

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-19 : 10:30:30

I changed
convert(varchar(65),replace(pm.vendname,' DO NOT PAY','')) as [Participant Name],

but
the error occurs exactly arround this part.

when i comment this part every thing is fine.

when '' then substring(replace(rtrim(pm.vendname),' DO NOT PAY',''),charindex(' ',replace(rtrim(pm.vendname),' DO NOT PAY',''))+1,len(pm.vendname))
else
FRSTNAME
end as first_name,

case isnull(LASTNAME,'')
when '' then left(rtrim(pm.vendname),charindex(' ',replace(rtrim(pm.vendname),' DO NOT PAY','')))
else
LASTNAME
end as last_name

Karthik

BJM RAO
Starting Member

20 Posts

Posted - 2009-03-19 : 10:49:02

pse send zsvendor,pm00200 & ref_participant tables (usded ones) structure ...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-19 : 11:29:01

quote:
Originally posted by karthik_padbanaban

I changed
convert(varchar(65),replace(pm.vendname,' DO NOT PAY','')) as [Participant Name],

but
the error occurs exactly arround this part.

when i comment this part every thing is fine.

when '' then substring(replace(rtrim(pm.vendname),' DO NOT PAY',''),charindex(' ',replace(rtrim(pm.vendname),' DO NOT PAY',''))+1,len(pm.vendname))
else
FRSTNAME
end as first_name,

case isnull(LASTNAME,'')
when '' then left(rtrim(pm.vendname),charindex(' ',replace(rtrim(pm.vendname),' DO NOT PAY','')))
else
LASTNAME
end as last_name

Karthik

OK, so that is a different problem than the first one you posted.

If you want help, you might think about posting the error message.

CODO ERGO SUM

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2009-03-20 : 01:15:06

It is just the part of my first post. when I comment these lines the query works fine with order by clause.

else it through the error message.

Warning: The query processor could not produce a query plan from the optimizer because the total length of all the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes.

some problem is in this part.

case isnull(FRSTNAME,'') when '' then substring(replace(pm.vendname,' DO NOT PAY',''),charindex(' ',replace(pm.vendname,' DO NOT PAY',''))+1,len(replace(pm.vendname,' DO NOT PAY','')))
else FRSTNAME end as first_name,

case isnull(LASTNAME,'') when '' then left(pm.vendname,charindex(' ',replace(pm.vendname,' DO NOT PAY',''))) else LASTNAME end as last_name,

Karthik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources