Prev: US-MD-Silver Spring: SQL VMWare TV Network DBA
Next: ado
From: John Bell on 1 May 2010 05:01
On Fri, 30 Apr 2010 13:31:01 -0700, jam772
<jam772(a)discussions.microsoft.com> wrote:

>I simply issued a create statement and specified to store the table in
>tempdb. After reading the info under the link you sent this may not have
>been the best idea. I changed my code to use the # nomenclature for temp
>tables but when the email tries to run the @query statement I get an error
>message that says:
>
>Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 357
>Query execution failed: Msg 208, Level 16, State 1, Server HBE01, Line 1
>Invalid object name '#EmailDataCollector'.
>
>I suspect that this has something to do with the fact that the @query
>statement runs in it's own session.


Hi

sp_send_dbmail will execute the query a different session, so local
temporary tables will not work. I was wanting to check that you wasn't
using one. If you are using a local table make sure that you cater for
two processes accessing it together.

With a user table what is the full error message/number you are
getting?

John
From: jam772 on 6 May 2010 13:07
It's the same message as I posted before which is:

Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 357
Query execution failed: Msg 208, Level 16, State 1, Server HBE01, Line 1
Invalid object name 'ESI.dbo.EmailDataCollector'.
--
Thanks!

- Jim


"John Bell" wrote:

> On Fri, 30 Apr 2010 13:31:01 -0700, jam772
> <jam772(a)discussions.microsoft.com> wrote:
>
> >I simply issued a create statement and specified to store the table in
> >tempdb. After reading the info under the link you sent this may not have
> >been the best idea. I changed my code to use the # nomenclature for temp
> >tables but when the email tries to run the @query statement I get an error
> >message that says:
> >
> >Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 357
> >Query execution failed: Msg 208, Level 16, State 1, Server HBE01, Line 1
> >Invalid object name '#EmailDataCollector'.
> >
> >I suspect that this has something to do with the fact that the @query
> >statement runs in it's own session.
>
>
> Hi
>
> sp_send_dbmail will execute the query a different session, so local
> temporary tables will not work. I was wanting to check that you wasn't
> using one. If you are using a local table make sure that you cater for
> two processes accessing it together.
>
> With a user table what is the full error message/number you are
> getting?
>
> John
> .
>
From: John Bell on 6 May 2010 18:39
On Thu, 6 May 2010 10:07:01 -0700, jam772
<jam772(a)discussions.microsoft.com> wrote:

>It's the same message as I posted before which is:
>
>Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 357
>Query execution failed: Msg 208, Level 16, State 1, Server HBE01, Line 1
>Invalid object name 'ESI.dbo.EmailDataCollector'.


As the message says that the object is missing it does seem that it
doesn't exist and not that the permissions are incorrect.

Are you sure that the schema is correct?

The only other suggestion is to post the actual code.

John
 | 
Pages: 1
Prev: US-MD-Silver Spring: SQL VMWare TV Network DBA
Next: ado