Cannot create a row of size 8077

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 2000 Forums

SQL Server Development (2000)

Cannot create a row of size 8077

Author

Topic

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-11-10 : 17:07:13

How can I run this???

Declare @oName as varchar(50), @cText as varchar(7500)
Select o.name, c.text
Into #MyProcs
From sysobjects o Inner Join syscomments c on o.id = c.id
Where crDate >= Cast('11/01/2004' as DateTime) and left(name, 3) Not in('sys','dt_') and xtype = 'P'

Select Top 1 @oName = name, @cText = text from #MyProcs
while Len(@oName) > 0
BEGIN
Print '--' + @oName
Print '--' + @cText
Print '--********************************************************************************'
Print '--********************************************************************************'
Delete from #MyProcs Where Name = @oName
Select Top 1 @oName = name, @cText = text from #MyProcs
END
Drop Table #MyProcs

Server: Msg 511, Level 16, State 1, Line 5
Cannot create a row of size 8077 which is greater than the allowable maximum of 8060.
The statement has been terminated.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-10 : 17:58:42

I've got a better question: WHY are you running this?? You don't need to create a new table for these results.

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-11-10 : 18:17:05

All I would really like to see is the full detail of my proc's... It does'nt really need to go to a table. I have sp's written like
Set @qry = 'select * from table A where '
Set @qry = @qry + 'WHERE COLA = 1'

We have many sp's that are refering to columns with aliases that are columns ie Column(s) jobopp_type, job_type but jobopp_type is is being aliased as job_type... I need to standardize the names in the table/sp/view to identify areas of confusion and miscommunication with the users.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-10 : 18:33:59

You can use sp_helptext to give you the actual text of the stored procedure. It only does one procedure at a time, but it's easy to generate the SQL to give you all of them:

SELECT 'exec sp_helptext ''' + name + '''' from sysobjects
Where crDate >= Cast('11/01/2004' as DateTime) and left(name, 3) Not in('sys','dt_') and xtype = 'P'

Copy those results into another QA window and run them. If you want to search procedure text, you can use this script:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32319

Or you can generate the procedures to a text file and search them any way you like (Word, notepad, find/findstr)

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-11-10 : 19:08:50

Cool that should be a lot of help, great link! You know when I try to search on this site I can only look through the title field, if I search body I time out.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-10 : 19:52:02

That's the Snitz forum code, not SQL Server. I had to do a forum search earlier that kept timing out, but when I ran it from query analyzer it came back in about 2 seconds!

Of course, changing Snitz code is a little like defusing a bomb...during a hurricane...while perched on top of a telephone pole...that is falling to the ground...covered in molten lava...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources