3

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

msdb.dbo.sp_send_dbmail 
 @recipients =   '[email protected]',
 @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!

Improve this question
3
  • Have you tried with a different profile? Have you tried without the attach as file parameter? How many rows does the query return when you run it in SSMS? Also are you on 2005 SP2, SP3, SP4? Perhaps there are fixes for this (there may be other causes, but here is one that was fixed in a post-SP1 hotfix: support.microsoft.com/kb/920794).
    – Aaron Bertrand
    Commented Jun 30, 2011 at 18:14
  • Fails with same error if @attach_query_result_as_file = 0. Query returns about 1700 characters, which shouldn't cause any overflow issues. Haven't tried a different profile, let me do that.... Nope, fails similarly on any other valid profile.
    – BradC
    Commented Jun 30, 2011 at 18:16
  • @Aaron We're on SQL 2005 SP3. Will be applying SP4 in July. Just weird that this worked before and isn't now. This is on an active-active cluster, but I can't imagine that's relevant.
    – BradC
    Commented Jun 30, 2011 at 18:22

2 Answers 2

Reset to default
2

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.

Improve this answer
2
  • Good idea, but not the problem in this case (the original had EXEC and was calling a custom stored proc that itself contained the call to sp_help_jobsteplog, I was just simplifying for the post, since I was able to replicate it by calling it directly).
    – BradC
    Commented Jun 30, 2011 at 18:09
  • Okay, I suggest modifying the sample code in the question because I won't be the last one who immediately suspects that (before reading the answers).
    – Aaron Bertrand
    Commented Jun 30, 2011 at 18:10
1

The problem is probably because you don't have enough free space in your TempDB log to execute the query. This kind of query manipulate a lot of data and need temp space to retain some data during the request execution time. Validate the log file for space. If you think that your log file is alright, it might be your disk space that is low.

Hope this help you to find why it stops.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.