How to reference a local temporary table in the @query parm of xp_sendmail

Hi,
Can anyone tell me if a local temporary table can be referenced in the @.quer
y
parameter of the extended procedure xp_sendmail and if so, how?
When I execute the following statements in Query Analyzer, I get the error
message provided below.
CREATE TABLE #TestEmailFile (TestEmailRow varchar(500) null)
INSERT INTO #TestEmailFile VALUES( 'Hello')
EXEC master.dbo.xp_sendmail @.recipients = 'EmailAddress',
@.query = 'SELECT * FROM #TestEmailFile',
@.subject = 'You have mail',
@.message = 'See attachment',
@.attach_results = 'TRUE',
@.width = 500
ODBC error 208 (42S02) Invalid object name '#TestEmailFile'.
Please help!
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1The sendmail executes in a different execution context than the other
connection. If you can guarantee > 1 connection will execute the statement,
then you can use a global temp table (## prefix). You could implement a
locking mechanism using app locks or a semaphore table if you had too, but a
better way to go would be to create a permanent table instead of a temp
table, include the spid in the primary key and everything should work better
(assuming the structure of the table is stable:
use db
go
create table dbo.testEmailFile
(
spid int,
key int,
otherCols int
)
Then
delete from dbo.testEmailFile where spid = @.@.spid --just in case something
fails
--and second delete doesnt happen
insert into dbo.testEmailFile
select @.@.spid, etc
EXEC master.dbo.xp_sendmail @.recipients = 'EmailAddress',
@.query = 'SELECT * FROM db.dbo.TestEmailFile',
@.subject = 'You have mail',
@.message = 'See attachment',
@.attach_results = 'TRUE',
@.width = 500
delete from dbo.testEmailFile where spid = @.@.spid --good data steward
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Cismail via webservertalk.com" <u14416@.uwe> wrote in message
news:5c5275146e3b0@.uwe...
> Hi,
> Can anyone tell me if a local temporary table can be referenced in the
> @.query
> parameter of the extended procedure xp_sendmail and if so, how?
> When I execute the following statements in Query Analyzer, I get the error
> message provided below.
> CREATE TABLE #TestEmailFile (TestEmailRow varchar(500) null)
> INSERT INTO #TestEmailFile VALUES( 'Hello')
> EXEC master.dbo.xp_sendmail @.recipients = 'EmailAddress',
> @.query = 'SELECT * FROM #TestEmailFile',
> @.subject = 'You have mail',
> @.message = 'See attachment',
> @.attach_results = 'TRUE',
> @.width = 500
>
> ODBC error 208 (42S02) Invalid object name '#TestEmailFile'.
> Please help!
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200602/1|||Very helpful. Thanks indeed!
Louis Davidson wrote:
>The sendmail executes in a different execution context than the other
>connection. If you can guarantee > 1 connection will execute the statement
,
>then you can use a global temp table (## prefix). You could implement a
>locking mechanism using app locks or a semaphore table if you had too, but
a
>better way to go would be to create a permanent table instead of a temp
>table, include the spid in the primary key and everything should work bette
r
>(assuming the structure of the table is stable:
>use db
>go
>create table dbo.testEmailFile
>(
> spid int,
> key int,
> otherCols int
> )
>Then
>delete from dbo.testEmailFile where spid = @.@.spid --just in case something
>fails
> --and second delete doesnt happen
>insert into dbo.testEmailFile
>select @.@.spid, etc
>EXEC master.dbo.xp_sendmail @.recipients = 'EmailAddress',
> @.query = 'SELECT * FROM db.dbo.TestEmailFile',
> @.subject = 'You have mail',
> @.message = 'See attachment',
> @.attach_results = 'TRUE',
> @.width = 500
>delete from dbo.testEmailFile where spid = @.@.spid --good data steward
>----
-
>Louis Davidson - http://spaces.msn.com/members/drsql/
>SQL Server MVP
>"Arguments are to be avoided: they are always vulgar and often convincing."
>(Oscar Wilde)
>
>[quoted text clipped - 17 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200602/1