I have a SQL 2005 stored proc that sends an email with some job history:
msdb.dbo.sp_send_dbmail @recipients = 'email@example.com', @Subject = 'job history', @Body = 'check this out...', @Profile_name = 'PublicEmail', @query = 'EXEC msdb.dbo.sp_help_jobsteplog ''F85B07F2-BC89-4934-B997-...''', @attach_query_result_as_file = 1
(that's a full GUID, I just cut it off for the code window) I know there are other ways to get job history, but this method has worked until recently, and now I get the following error:
Msg 22050, Level 16, State 1, Line 0 Error formatting query, probably invalid parameters Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495 Query execution failed: Sqlcmd: Error: Internal error at GetDataRowset (Reason: Not enough storage is available to complete this operation). Sqlcmd: Error: Internal error at FormatRowset (Reason: Not enough storage is available to complete this operation).
I can run the
sp_help_jobsteplog query all by itself with no problems. There is no shortage of space on the server, so I think the "not enough storage" part of the error message is probably not really what's going on. The documentation for sp_help_jobsteplog says
By default, members of the sysadmin fixed server role can execute this stored procedure. Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:
So when I combine that with the fact that
db_send_dbmail runs @QUERY statements in a separate session, I'm worried that its actually a permissions issue.
My question is: what user does
sp_send_dbmail try to use to run its @Query? (I'm running sp_send_dbmail itself as a sysadmin, and if it carried through those credentials, it would work, right?) Does it depend on the profile used?
Any other ideas what might be going wrong here?
EDIT: So I still have no idea why this was happening, but I rebooted the server, and now everything appears to be back to its (normal) working state!