email results from stored proc 1

[ksbrace]

Hello,
I have some stored procs that I would like to have executed every 2 hours. That part is fine, but I would also like the results emailed to me. Thanks for any advice in advance.

 

[mrdenny]

Use xp_cmdshell and put the stored procedure in the @Query parameter.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ksbrace]

mrdenny,
Thanks for hte reply. However, I'm having difficulty using xp_cmdshell with @query.
This generates an error: Invalid Column Name on the set @query line. getBufferCache is the name of the sp.

declare @query varchar(1000)
set @query = getBufferCache
exec xp_cmdshell @query

This is what I have been working on, but it just returns 0 or 1, which is not what I want.

declare @body1 varchar(1000)
exec @body1 =  getBufferCache --'Server :'+@@servername+ ' My First Database Email '
EXEC msdb.dbo.sp_send_dbmail @recipients='kb@myemail.com'
    @subject = 'My Mail Test',
    @body = @body1,
    @body_format = 'HTML' ;

Thanks in advance for any help.

**I had previously posted in sql programming and have some responses there. I don't know how to remove one or the other.

 

[mrdenny]

You have to put the procedure name in single quotes, like this.

declare @query varchar(1000)
set @query = 'exec databasename.dbo.getBufferCache'
EXEC msdb.dbo.sp_send_dbmail @recipients='kb@myemail.com'
    @subject = 'My Mail Test',
    @execute_query_database = @query
    @body_format = 'HTML' ;

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[ksbrace]

mrdenny,
Thanks, but that is not working. I get a blank email.

exec databasename.dbo.getBufferCache gives me the results in query analyzer.

 

[ksbrace]

ok,
I got it:

use angelsql
go
declare @querystring nvarchar(4000)
set @querystring = 'exec DBNAME.dbo.getBufferCache'
EXEC msdb.dbo.sp_send_dbmail @recipients='kb@myemail.com',
    @subject = 'My Mail Test',
    @query = @querystring,
    @body_format = 'HTML' ;

Thanks for your guidance.

 

[mrdenny]

No problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com