August 12, 2011 at 3:06 pm
It seems like this should be simple, but I must be missing something really basic.
Everything works fine except the part about trying to stuff EXEC sp_databases into a variable. Can this be done easily somehow?
DECLARE @messageBody VARCHAR(MAX)
SELECT @messageBody = EXEC sp_databases
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLDBA' ,
@recipients = 'myEmail@myDomain.com' ,
@body = @messageBody ,
@subject = 'List of Databases'
August 12, 2011 at 3:09 pm
jpSQLDude (8/12/2011)
It seems like this should be simple, but I must be missing something really basic.Everything works fine except the part about trying to stuff EXEC sp_databases into a variable. Can this be done easily somehow?
DECLARE @messageBody VARCHAR(MAX)SELECT @messageBody = EXEC sp_databases
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLDBA' ,
@recipients = 'myEmail@myDomain.com' ,
@body = @messageBody ,
@subject = 'List of Databases'
Try
exec @messageBody = sp_databases
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
August 12, 2011 at 3:15 pm
If you do this, the value of @messageBody is 0, rather than a list of servers.
DECLARE @messageBody VARCHAR(MAX)
EXEC @messageBody = sp_databases
SELECT @messageBody
August 12, 2011 at 3:23 pm
Here are two options to do that:
--option 1
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLDBA' ,
@recipients = 'myEmail@myDomain.com' ,
--@body = @messageBody ,
@subject = 'List of Databases'
,@query = 'Exec sp_databases'
go
--option 2
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLDBA' ,
@recipients = 'myEmail@myDomain.com' ,
--@body = @messageBody ,
@subject = 'List of Databases'
,@query = ' select
DATABASE_NAME = db_name(s_mf.database_id),
DATABASE_SIZE = convert(int,
case -- more than 2TB(maxint) worth of pages (by 8K each) can not fit an int...
when convert(bigint, sum(s_mf.size)) >= 268435456
then null
else sum(s_mf.size)*8 -- Convert from 8192 byte pages to Kb
end)
from
sys.master_files s_mf
where
s_mf.state = 0 and -- ONLINE
has_dbaccess(db_name(s_mf.database_id)) = 1
group by s_mf.database_id'
The second option is merely stripping the guys out of that proc as an example of another means to accomplish what you need.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2011 at 3:36 pm
Nice! Those both really work great, I didn't realize there was that @query parameter.
I guess a third option would be to create a #tmp table and save whatever complex query into that, then just do a SELECT * FROM #tmp as your @query parameter. Or even create a View containing your query and just select from that.
Thanks!
August 12, 2011 at 3:38 pm
Yup, all of those are viable options as well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 15, 2011 at 12:41 pm
Those are nice, thanks.
Ed Watson aka SQLGator
Microsoft SQL Server MVP
Follow me on Twitter!
Go Gators!
August 15, 2011 at 12:55 pm
Here is a follow-up blog post about these as well.
http://jasonbrimhall.info/2011/08/15/send-dbmail/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply