A curious error message, local temp vs. global temp tables?!?!?

Hi all,

Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.

However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.

Here is the code that works:SET NOCOUNT ON

CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @.cmd varchar(256)
DECLARE @.LargestEventSize int
DECLARE @.Width int, @.Msg varchar(128)
SELECT @.LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl

SET @.cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @.LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @.Width = @.LargestEventSize + 1
SET @.Msg = 'Here is the junk you asked about' + CHAR(13) + '---------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@.WhoKnows.com',
@.query = @.cmd,
@.no_header= 'TRUE',
@.width = @.Width,
@.dbuse = 'MyDB',
@.subject='none of your darn business',
@.message= @.Msg
DROP TABLE ##MyTempTbl

The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.

Any insight anyone? Or is BOL just full of...well..."stuff"?I would still like to hear if anyone knows anything different, but while looking into Des' sendmail problem, I found this lil' tidbit in BOL for xp_sendmail If query is specified, xp_sendmail logs in to SQL Server as a client and executes the specified query. SQL Mail makes a separate connection to SQL Server; it does not share the same connection as the original client connection issuing xp_sendmail. I suspect the "separate connection to SQL Server" is the issue here?!?!?! Hmmmm...perhaps the local temp table can be seen by child processes called by the proc that creates the table EXCEPT in xp_sendmail, etc.|||You hit the problem right on the head... xp_sendmail does execute the query in a different context, meaning that it can't see local variables, settings, or temp tables. You can think of it almost as though xp_sendmail were cranking up OSQL.EXE to execute your query (that isn't what actually happens, but it is logically pretty close).

-PatP