sql issue urgent pls

[pardhiveswar]

HI

CAn any one suggest how to send email the following results

Declare @Spid TAble(
StoredProcedure varchar(1000),
string varchar(3999),
session_id varchar(100),
start_time varchar(200))
declare @Result varchar(5000)
insert into @Spid
select object_name(objectid, r.database_id)StoredProcedure ,
text, session_id,start_time
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(r.sql_handle)
where DATEdiff(minute,start_time,GETDATE()) > 1
set @Result=' select ss.storedprocedure,ss.string,ss.session_id,ss.start_time,
s.spid
from master.dbo.sysprocesses s
inner join @Spid ss
on s.spid=ss.session_id
where DATEdiff(MINUTE,login_time,GETDATE()) > 1
and loginame like ''%microstrl%'''

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'par...@herbalife.com',
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Success Message' ,
@query=@Result

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-performance/200905/1

[RickNZ]

The scope of the table variable doesn't extend into the dynamic SQL. Your
query should include all required data. It might be easier to use a stored
procedure instead.

--
Check out my upcoming book: ASP.NET: Building Ultra-Fast and Ultra-Scalable
Websites Using ASP.NET and SQL Server