sp_send_dbmail using local variables (or not!)
-
Sorry if this has been gone over a million times, but i cant find anything in the search at all.
When using sp_send_dbmail with the @query parameter you cant call local variables in the select statement.
To quote BOL
[ @query = ] 'query'
Is a query to execute. The results of the query can be attached as a file, or included in the body of the e-mail message. The query is of type nvarchar(max), and can contain any valid Transact-SQL statements. Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.
Firstly this seems a really odd step back from xp_sendmail, but secondly, there must be a workaround to this?
-
Try building a query string with your vars first and then passing the query as a single var.
I just tried this and it works:
declare @sql as nvarchar(500),
@dbname as varchar(50)
set @dbname = 'dbstats'
set @sql = 'select * from master.sys.sysdatabases where name = ''' + @dbname + ''''
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Admin',
@recipients = 'you@yourdomain.com',
@query = @sql ,
@subject = 'Work Order Count',
@attach_query_result_as_file = 1 ;
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Works a treat, cheers. Dont know why i didnt think of that though 🙂
-
It's all good. Glad I could help.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply