Search code examples
sqlemailsql-server-2000

xp_sendmail using table variable as @query SQL Server 2000

I am trying to send a resultset via email using xp_sendmail. I need to send the email when an earlier executed query has any results.

Got the query results into a table variable/temp table and then in xp_sendmail, using 

Declare @table_var table(...)

..query execution..

EXEC master.dbo.xp_sendmail @recipients = 'xx@xx.com', 
@query = 'select * from @table_var'

it gives error saying that

@table_var must be declared.

Even if I use temporary table, the message I get is

cannot reference object in tempdb database.

Any ideas on this?

Thanks in advance

Solution

  • You'll need to use a real table for this. Try..

     If exists (select * from sys.tables where name = 'mytable')
      drop table mytable
    Create Table mytable table(...)


    ..query execution..

    EXEC master.dbo.xp_sendmail @recipients = 'xx@xx.com', 
    @query = 'select * from mydatabase.dbo.mytable'