Error When executing cursor?

[thegeezza]

Afternoon,

I get the following error when executing the code below.

Server: Msg 510, Level 16, State 2, Line 9
Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint.

declare @routine_catalog 	varchar(255)
	declare @routine_name	 	varchar(255)
	declare @routine_definition 	varchar(255)
		
	--Create a cursor to cycle through all of the columns specified in the user supplied table.
	declare	cXursor cursor for
		select	routine_catalog,routine_name,routine_definition
		from	information_schema.routines option(ROBUST PLAN)
		open	cXursor 
		fetch	next
		from	cXursor
		into	@routine_catalog,@routine_name,@routine_definition

Any advice with this would be greatly appreciated.

cheers,
TheGeezza

 

[Joulius]

information_schema.routines is a table or a view? How many rows does it contains? The user/login that executes your query has write rights on tempdb? Is there any size limitation on tempdb?

 

[thegeezza]

Hi Joulius,

Information_Schema.routines is a view (of the syscomments table).

There are 150 rows in the syscomments table.
There will not be a permissions issue.
Tempdb has plenty of available space and is set to autogrow.

I think this is a problem with the row size returned.
routine_definition will be the largest column returned as it contains the stored_procedure defintion text. However just selecting routine_definition works fine.

Regards,
theGeezza

 

[Joulius]

Could you post the view definition?

 

[gmmastros]

I don't understand what you are trying to accomplish. You have a comment about cycling through the columns on a table, but you're using Information_Schema.Routines.

Information_Schema.Routines contains information about UDF's and Stored Procedures.
Information_Schema.Routine_Columns has a record for each parameter of your stored procedures.
Information_Schema.Tables has a record for each table in the database.
Information_Schema.Columns has a record for each field of each table in the database.

If I understood what you were trying to accomplish, I (or someone else) may be able to help a little better.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[thegeezza]

Ignore the comment, it is not meant to be there.

I got around the problem by accessing the tables in question directly and modifying the select query slightly.

The reason I am doing all of this is because I want to write a stored procedure that will list all of the stored procedures within a given database and also list the variable declarations that are defined within them.
I have now completed this.

Thank you for your help.

Regards,
TheGeezza

FYI,

declare	cXursor cursor for
		select	B.text,A.name
		from	sysobjects A
			inner join syscomments B on
				A.id=B.id
			where A.xtype='P' and PATINDEX('CREATE PROCEDURE%',B.text)>=1 
		
		open	cXursor 
		fetch	next
		from	cXursor
		into	@routine_definition,@routine_name

 

[Joulius]

Here's something I've done in the past and it's very similar with what you need:

create table #x (rowid int identity(1,1), txt text, name varchar(255))
declare @index int, @sp_id int, @sp_name varchar(255), @buff varchar(8000)

declare p_cursor cursor
for	select id, name
	from sysobjects
	where type IN ('P', 'V', 'FN', 'TR') and status >= 0

open p_cursor

	fetch next from p_cursor
	into @sp_id, @sp_name

	while @@fetch_status = 0

		begin

			declare @ptrtxt varbinary(16)

			insert into #x (txt, name)
			select '', @sp_name

			select @index = @@identity
			select @ptrtxt = textptr(txt) from #x where rowid = @index

			declare r_cursor cursor
			for 	select text
				from syscomments
				where id = @sp_id 
			
			open r_cursor
			
				fetch next from r_cursor
				into @buff
			
				while @@fetch_status = 0
				begin
					updatetext #x.txt @ptrtxt NULL NULL @buff
			
					fetch next from r_cursor
					into @buff
				end
			
			close r_cursor
			deallocate r_cursor
		
			fetch next from p_cursor
			into @sp_id, @sp_name
		
		end
close p_cursor	
deallocate p_cursor

select 	1 	as Tag,
	NULL	as Parent,
	NULL	as [backup!1!element],
	NULL	as [row!2!rowid],
	NULL	as [row!2!name],
	NULL	as [row!2!!cdata]
union all
select 	2 	as Tag,
	1	as Parent,
	NULL	as [backup!1!element],
	rowid	as [row!2!rowid],
	name	as [row!2!name],
	txt	as [row!2!!cdata]
from #x
order by Tag, [row!2!rowid]
for xml explicit

drop table #x

I hope this helps!