Sql-server – sp_help_jobsteplog fails when run as a @query of sp_send_dbmail

sql-server-2005

I have a SQL 2005 stored proc that sends an email with some job history:

msdb.dbo.sp_send_dbmail 
 @recipients =   'me@here.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:

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

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!

Best Answer

Did you try making the query 'EXEC msdb.dbo.sp_help_jobsteplog ''...'';' ? Chances are that internally there are other commands injected before the stored procedure is called, and if the procedure call is not the first in the batch, it must use EXEC or EXECUTE. I wish they would banish the ability to just say sp_anything without a preceding EXEC, it would make our lives far easier than the current savings of 5 keystrokes does.

Related Question