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

64-bit SQL Server, DTS and Mail

Greetings al
Any feedback on 64-bit SQL Server? Is anyone using it? Any positive results or problems experienced
I read in the BOL that DTS componenets are not included in the current release. I see this as a major hurdle to overcome - yes packages can be run from a 32-bit server, but this would have an assoicated downside.
Also, SQL Mail is not supported. Can anyone at MS explain why SQL Agent Mail is supported but not SQL Mail? And why is Outlook Express the mail client and not Outlook
Finally, will DTS and SQL Mail be fully supported in future releases on the 64-bit platformThe primary reason for no SQLMail support is that SQLMail requires Extended
MAPI which was not ported to 64bit.
Agent Mail is supported because we did some work on it to support Simple
MAPI which is included with Outlook Express. It is not possible to run
Simple MAPI inside SQL Server as an XP hence this solution does not work
for SQLMail.
The reasons for Outlook Express and not Outlook are:
1/ There is no 64bit version of Outlook, hence no 64bit Simple/Extended MAPI
included with it.
2/ Even under 32bit once Extended MAPI is installed Simple MAPI no longer
works in a manner which allows it to be used by Agent Mail.
We aim to resolve these in a future release.
-Euan
Please reply only to the newsgroup so that others can benefit. When posting,
please state the version of SQL Server being used and the error number/exact
error message text received, if any.
This posting is provided "AS IS" with no warranties, and confers no rights.
"Elliot Wien" <anonymous@.discussions.microsoft.com> wrote in message
news:B0C44188-50B4-4461-9E54-D53AE943B019@.microsoft.com...
> Greetings all
> Any feedback on 64-bit SQL Server? Is anyone using it? Any positive
results or problems experienced?
> I read in the BOL that DTS componenets are not included in the current
release. I see this as a major hurdle to overcome - yes packages can be run
from a 32-bit server, but this would have an assoicated downside.
> Also, SQL Mail is not supported. Can anyone at MS explain why SQL Agent
Mail is supported but not SQL Mail? And why is Outlook Express the mail
client and not Outlook?
> Finally, will DTS and SQL Mail be fully supported in future releases on
the 64-bit platform?
>