Search by Stored Procedure size

Question

Search by Stored Procedure size

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Farrell Keough SSCoach Points: 17414 More actions · Answer 1

I saw that in BOL, but I must have been brain-dead thinking this was pulling in all the text within the SP into one long string... Either way, I am still getting the same exact error...

I wasn't born stupid - I had to study.

Sergiy SSC Guru Points: 110208 More actions · Answer 2

But it works!

Any of these scripts does not fail on any of servers I can access.

Try to find out what's wrong with your server.

Do some test exersises like this:

SELECT MAX(LEN(TEXT)) from syscomments

Try to lacate the reason of the error.

P.S. /1024.0 will give you more accurate result. After you have your Server fixed, of course.

_____________
Code for TallyGenerator

Farrell Keough SSCoach Points: 17414 More actions · Answer 3

It is 4,000. But I still get the error I have been posting, (along with the exact code I run) when I try and do this...

I will check further tomorrow.

Thanks!

I wasn't born stupid - I had to study.

noeld SSC Guru Points: 96590 More actions · Answer 4

Sorry for the delay Yes I goofed when writing the post but it si easy to fix:

select object_name(id) n, sum(datalengh(text)) char_cnt

from syscomments

group by object_name(id)

order by char_cnt desc

I tested it by generating

select replicate('*',8000) several times and got character values over 45000 .. yes fourty five thousand!!!

* Noel

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

sql 2k - sp4 - get the exact same error msg as farrell does..

**ASCII stupid question, get a stupid ANSI !!!**

sushila SSC-Dedicated Points: 35293 More actions · Answer 6

okay - change the column from "text" to "ctext"..

**ASCII stupid question, get a stupid ANSI !!!**

sushila SSC-Dedicated Points: 35293 More actions · Answer 7

but WHY the difference ?!?! Has it anything to do with our db collations ?!?!

**ASCII stupid question, get a stupid ANSI !!!**

noeld SSC Guru Points: 96590 More actions · Answer 8

try appending ... OPTION(ROBUST PLAN) ... to the query

I don't have sp4. This error tends to happen with "text" columns and merge joins!!

Cheers,

* Noel

Sergiy SSC Guru Points: 110208 More actions · Answer 9

My SQL Server is SP4.

And everything works perferct.

I have SP with size 104kb. And I can see it in the resultset.

I don't know how collation may affect this query because there is no any string operation, LEN(TEXT) gives you integer values at the entrance, tables are joined by ID.

Error is somewhere else.

What if you run the same query without ORDER BY clause?

_____________
Code for TallyGenerator

noeld SSC Guru Points: 96590 More actions · Answer 10

I am pretty sure that is not the order by is the JOIN TYPE!!! Can you post the query plan you are getting? Merge joins require ordered input or at least an index in that column

Cheers,

* Noel

Sergiy SSC Guru Points: 110208 More actions · Answer 11

sysobjects O

inner join syscomments S on O.ID = S.ID

Group by O.name

Order by Sum(Len(S.Text)) Desc

|--Sort(ORDER BY: ([Expr1002] DESC))

|--Compute Scalar(DEFINE: ([Expr1002]=If ([Expr1007]=0) then NULL else [Expr1008]))

|--Stream Aggregate(GROUP BY: ([O].[name]) DEFINE: ([Expr1007]=COUNT_BIG(len(.[text])), [Expr1008]=SUM(len(.[text]))))

|--Sort(ORDER BY: ([O].[name] ASC))

|--Merge Join(Inner Join, MERGE: ([O].[id])=(.[id]), RESIDUAL: (.[id]=[O].[id]))

|--Clustered Index Scan(OBJECT: ([DB].[dbo].[sysobjects].[sysobjects] AS [O]), ORDERED FORWARD)

|--Compute Scalar(DEFINE: (.[text]=Convert(If (Convert(syscomments.[status])&2=Convert(2)) then uncompress(syscomments.[ctext]) else Convert(syscomments.[ctext]))))

|--Clustered Index Scan(OBJECT: ([DB].[dbo].[syscomments].[syscomments] AS ), ORDERED FORWARD)

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 12

I've got the same error when I ran this query against master db!

I modified script a little and error has gone:

select O.name , SumLen

from sysobjects O

INNER JOIN (

select Id, SUM(Len(Text)) as SumLen

from syscomments

GROUP BY ID ) S on O.Id = S.Id

ORDER BY SumLen DESC

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 13

It's about size of joined rows.

Len(text)*2 + len(name)*2 + other fixed sizes must be < 8095

You can identify problermatic objects with this query:

select O.Name, S.Text, LEN(S.Text), len(O.Name) + LEN(S.Text)

from sysobjects O

inner join syscomments S on O.ID = S.ID

where len(O.Name) + LEN(S.Text) > 4041

_____________
Code for TallyGenerator

msaxe SSC Enthusiast Points: 148 More actions · Answer 14

Won't this work?

select objName=object_name(sc.id), Length=len(sc.text)

into #tmpX

from syscomments sc

where OBJECTPROPERTY(sc.id,'IsProcedure') = 1

select objName, Length=sum(length)

from #tmpX

group by objName

order by Length desc

--drop table #tmpX

Farrell Keough SSCoach Points: 17414 More actions · Answer 15

THANK YOU ALL!

This works great and now I can assign work for these big suckers!

I wasn't born stupid - I had to study.