the mail runs from the msdb database, so you have to fully qualify everything, like

EXEC msdb..sp_send_dbmail

@profile_name = 'reports',

@recipients = 'test@test.com',

@subject = 'Monthly Job Creation',

@query = 'EXEC Production.dbo.udsp_rpt_adm_monthly_jobs'

i think if you are using a query, there are other parameters required to decide where the results of the query is going to go, like as an attachment or int he body of the email;

this is the example i tend to like a lot:

declare @body1 varchar(4000)

set @body1 = 'Sample Email from SQL2008 ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +

' '

EXEC msdb.dbo.sp_send_dbmail

@profile_name='gMail Example',

@recipients='lowell@somedomain.net',

@subject = 'SQl 2008 email test',

@body = @body1,

@body_format = 'HTML',

@query = 'SELECT top 3 * from sysobjects where xtype=''U''',

@query_result_header = 0,

@exclude_query_output = 1,

@append_query_error = 1,

@attach_query_result_as_file = 1,

@query_attachment_filename = 'qry.txt',

@query_result_no_padding = 1