Example of dynamic SQL forcing global cursor (this one is from a metafunction library, but it demonstrates the idea).
exec sp_lib_KillCursor 'fetch_cursor'
SELECT @S = 'DECLARE fetch_cursor CURSOR FOR ' +
'SELECT c.name, c.xtype, c.status, c.IsComputed,
FROM syscolumns c, sysobjects b, systypes t
WHERE t .xtype = c.xtype
AND t .xusertype = c.xusertype
AND c.id = b.id
and b.name = ''' + @TableName + ''''
For session based SPROCS the global cursor name has the sessionID appended so as to not cause clashes.
And confirming: you cannot use temp tables in views ?
Hence if you have a query referencing 15 variously nested views and some of the bottom ones are causing the problems, you have to discard all (or most of) the views and go to a mega-query comprising the temp tables and the remaining raw tables to get your result ?
This is my problem: if forced to choose between views and performance (here, specifically temp tables), I'll take the views (not my choice really, it's a matter of cost-benefit). The OP is asking if I can possibly have both.
As it happens, in this case it appears I can have both by upgrading to SQL2005.
Further ramifications (if any) of that upgrade will no doubt become apparent.