OK,

Here is my code:

ALTER  PROC dbo.sy_capture_auditdata
-- @parms			parmtype
(@startdate         datetime
,@stopdate          datetime
,@debug			int = 0
)

AS

SET NOCOUNT ON
SET XACT_ABORT ON

-- SQL CODE STARTS HERE
declare @view sysname

declare hCForEach cursor global
for
select user_name(uid) + '.' + object_name(id)
from sysobjects o
where type = 'V' and o.category & 0x0002=0
	and name LIKE 'aud%'

open hCForEach

FETCH hCForEach INTO @view

WHILE @@FETCH_STATUS >= 0
BEGIN

	EXEC sy_write_audit_data @view,@startdate,@stopdate

	FETCH hCForEach INTO @view
END

close hCForEach
deallocate hCForEach

RETURN

After the EXEC the FETCH returns this error:

Server: Msg 16917, Level 16, State 2, Procedure sy_capture_auditdata, Line 32
Cursor is not open.

I had problems with the last week to when working on something. I ended up changeing to a cusorless loop where I just select progresivly higher PK's.

But, I think after I installed SP 3 is when this broke. And I crazy?

I initially was going to use a modified sp_MSforeachtable (for views) but I got the error.

Then, I tried to run a sample in a SQL Pro article that uses for each, here is the code:

sp_MSforeachtable
 "print '--------------------------------------'
  print 'Triggers defined on ?'
  print '--------------------------------------'",
 "?",
 "SELECT tr.name,'FOR INSERT' from sysobjects so
  join sysobjects tr on tr.id=so.instrig
  where so.id=OBJECT_ID('?')
 UNION
 SELECT tr.name,'FOR UPDATE' from sysobjects so
  join sysobjects tr on tr.id=so.updtrig
  where so.id=OBJECT_ID('?')",
 "++UNION
 SELECT tr.name,'FOR DELETE' from sysobjects so
  join sysobjects tr on tr.id=so.deltrig
  where so.id=OBJECT_ID('?')",

  @whereand='AND (instrig+updtrig+deltrig)>0'
 GO

When I run this code I get:

Server: Msg 16917, Level 16, State 2, Procedure sp_MSforeach_worker, Line 140
Cursor is not open.
Server: Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 142
Cursor is not open.

So, did SP3 break something major or do I just have some setting or something message up.

Help!

BOb