Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Question Unanswered: 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:
    Code:
    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"?
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    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.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •