September 19, 2011 at 8:06 am
Hi
I am running SS2008R2 and getting this error from a SQL Agent job step that is using sp_send_dbmail with the @Query parameter. The step is currently set to 'Run as: domain\user'.
Without 'Run as' the step fails with: "servername\agentaccount. Error formatting query, probably invalid parameters".
In either case (i.e. with or without 'Run as') a message will be sent if I omit the @Query.
The domain\user account is a member of sysadmin. If I logon to the server from SSMS using the domain\user account (with Windows Authentication), then copy + paste the job step into an SSMS query it runs fine - I get an email with the results of the query.
Can anyone offer any suggestions, please?
September 19, 2011 at 2:11 pm
I'd look at security. When we first saw that error at my last job, it was because the SQL Agent account wasn't a sysadmin and didn't have read rights in the database that was used in the query. It's a really cryptic message.
September 20, 2011 at 12:28 pm
What is the default database of login domain\user ?
Are you setting @execute_query_database in your call to sp_send_dbmail, or are you database-qualifying all object names in your query?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 22, 2011 at 4:51 am
The default database for domain\user is the database that is being queried. We've tried both @execute_query_database and specifying the database in the @query.
September 22, 2011 at 6:50 am
Post the job script.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 22, 2011 at 8:24 am
Here's the script of the appropriate job step
---------------------------
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Email Send',
@step_id=18,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'/*Routine to check disk space and date of update
*/
use master
-- Find date of DB
exec msdb.dbo.sp_send_dbmail
@profile_name = N''xxxx xxxx'',
@recipients = N''xxxx.xxxx@xxxx.xxx.xx'',
@subject = N''dbname update date'',
@execute_query_database = N''dbname'',
@query = N''select * from tablename''
use dbname
-- Send Disk Space
exec msdb.dbo.sp_send_dbmail
@profile_name = N''xxxx xxxx'',
@recipients=N''xxxx.xxxx@xxxx.xxx.xx'',
@subject=N''dbname space used'',
@query=''dbname..sp_spaceused ''',
@database_name=N'master',
@database_user_name=N'domain\user',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
---------------------------
Thanks for your time and assistance
September 22, 2011 at 9:06 am
Sure, HTH.
At first glance I thought maybe you needed an EXEC before the proc call in the second email attempt, but that rule only applies if there is more than one statement in a batch.
My next guess is the login used does not have proper access to a user database one of your queries reference.
Try this in an SSMS query window and see how it goes:
EXECUTE AS LOGIN = 'domain\user'; -- simulates job step @database_user_name setting
GO
USE [dbname] -- because of @execute_query_database
GO
select * from tablename -- simulate first mail query in specific DB and security context
GO
USE dbname -- here only because it's in the job step...it should not make much difference since sp_spaceused is DB-qualified
GO
dbname..sp_spaceused -- simulate second mail query in specific DB and security context
GO
REVERT
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
December 2, 2011 at 10:20 am
I recently had a similar situation with the same error
Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.
in a sql server 2008 sp2 installation job that had been running in other servers for years.
As opc.three rightly proposed, the error message is not indicative of the underlying error but seems to be permissions related and not query related.
The way I resolved it was on the job step, Advanced page for the job step, to change the Run as user to one that had access to both the user database and the msdb database.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply