Home > email > Sending Email from SQL Server Agent scheduled job

Sending Email from SQL Server Agent scheduled job

November 19Hits:2
Advertisement

I've scheduled a job in SQL Server Agent that sends the result of a query in an email. If I execute the stored procedure from SSMS, it works fine. But the SQL Server Agent is not able to send emails.

Here is the error I received.

The following account is scheduled as owner of SQL Server Agent in the SQL Server Configuration Manager: NT AUTHORITY\NETWORK SERVICE.

What can I do to assign privileges to this user?

Message

Executed as user: NT AUTHORITY\NETWORK SERVICE. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

Answers

You can also try granting access to xp_sysmail_format_query to NT AUTHORITY\NETWORK SERVICE:

USE master
--Create user in master if necessary
CREATE USER [nt authority\system] FOR LOGIN [nt authority\system]
GO
--Grant execute to the stored procedure that's failing
GRANT EXECUTE ON [xp_sysmail_format_query] TO [NT AUTHORITY\NETWORK SERVICE]
GO

Best thing, though, is to run the SQL Agent service under the context of a domain user that has the appropriate access to your network resources. Doing so is much more portable than using local accounts.

Tags:email, sql, agent

Related Articles

  • Sending Email from SQL Server Agent scheduled jobNovember 19

    I've scheduled a job in SQL Server Agent that sends the result of a query in an email. If I execute the stored procedure from SSMS, it works fine. But the SQL Server Agent is not able to send emails. Here is the error I received. The following accoun

  • Send Email when sql server agent job failsJuly 12

    How can I send an e-mail when the sql server job fails. I added my e-mail as operator and then in the job properties -> notification -> enabled the email. But it is not working. Any ideas? --------------Solutions------------- For 2005 and 2008 you n

  • Replace the server name in email sender address when sending email from SQL Server 2008

    Replace the server name in email sender address when sending email from SQL Server 2008December 16

    I'm sending emails directly to Microsoft Outlook accounts from SQL Server 2008. Everything is fine, email was sent. Only problem is that in the header of the email the SQL Server name is embedded. I've tried specifying the @from_address when executin

  • SQL Server Agents jobs and turning off the serverDecember 17

    I'm really new to SQL Agent jobs, but I am attempting to build up a maintainance regime for a server that will be turned off and on again at unknown intervals. It may run without being shutdown for a month, or it might only be turned on 9-5... we don

  • How to check Schedule item is running in List Schedule SQL server Agent Job C#January 20

    I am using SQL server Agent in C#. My job has 3 Schedule, i set one Schedule runs ahead. How to check name of Schedule running. Thanks you. This is code: private void timer1_Tick(object sender, EventArgs e) { JobSQL.Refresh(); if (JobSQL.CurrentRunSt

  • Who changed the job schedule on a SQL Server Agent Job (SQL 2000)February 1

    Someone (or some unknown process) changed the job schedule on a SQL Server Agent Job on a SQL 2000 database. The original start time was 6:00, but was changed to 4:45; that much I can determine. Is there a way to find the history of changes involving

  • I'm using the SQL Server Agent to schedule even non-database tasks - is this a bad idea?December 21

    Since I'm a DBA (and in many cases, the de-facto sysadmin), SQL Server is installed on pretty much every server I have to work with regularly. I realized recently that I've been using the SQL Agent as the job scheduler in pretty much every case, rath

  • Should SQL Server backups be scheduled in SQL Server Agent or as a Scheduled Task?January 22

    I have inherited a system where a system where our database is backed up by identical scripts, one set up in SQL Server Agent and one set up as a Scheduled Task. Both are running at the same time, so I'd like to disable one of them. Apart from my own

  • SQL Server Agent 2012 Job Schedule Report October 20

    I am looking for a report that will display graphically what jobs are scheduled on the server and when. My goal is to very quickly be able to glance at it and find a time where a new job will not interfere with other jobs. I have been looking at the

  • SQL Server Agent Job - Schedule daily except 1 day per monthMarch 12

    We need to run a daily job using SQL Server Agent (SQL 2000) but we don't want it to run on the 22nd of each month as there are other processes which only run on that day which will clash. The recurring settings in Agent do not provide any exclusion

  • How do I run a batch file from a scheduled Job via SQL Server AgentDecember 13

    I'm attempting to run a (Windows) batch file from within a SQL Server agent job under SQL Server 2008 R2. In the Job Steps, I've created a new step, set its type to "Operating system (CmdExec)". I initially tried to put just the batch file name

  • Is it safe to restart Sql Server Agent?May 25

    I've found myself in the tenuous role of a developer tasked to manage a SQL server. I've set up some jobs to run on a schedule, but would like to receive email notification on completion. I've set up Database Mail and successfully sent a test message

  • SQL Server Agent shutting down minute after startingAugust 28

    I'm a noob :) to SQL , so please don't judge me to harsh When I start SQL Server Agent it's shutting down minute after starting . I tried to set Alerts to send email. After I configured Database Mail , I was able to send test email , however after th

  • Stopping SQL Server Agent: Effect on runnings jobsMay 27

    On a server with MS SQL Server 2008 installed, we use the SQL Server Agent to schedule a large amount of SSIS-packages, all with unique schedules throughout the day. Now and then, the server needs to be restarted (patching etc). Is it safe to right-c

  • SQL server agent SSIS error

    SQL server agent SSIS errorJune 20

    I get the following error when I execute my package as a SQL server agent job. It is an SSIS 2008 package running on a SQL Server 2008 instance. My package security is DontSaveSensitive. I don't even know how to begin fixing this error. Where should

  • SQL Server 2008 - scheduled copy of production database into test and development databasesMay 7

    I wish to schedule a copy from my production database into two other databases. The databases are located on same server (instance?). The databases are currently backed up to server hard drive every night and basing a solution on this backup would be

  • Chaining SQL Server Agent JobsJuly 1

    I have several work packages for each I have setup up a job in sql server agent. Sometimes I should run all together. What is the best way to run them all in a given sequence? I'm a bit surprised that sql server agent is not able to include jobs as j

  • How do I backup SQL Server Agent configuration?September 23

    I've tried googling for this. The problem is that the all the results are about using SQL Server Agent to schedule automatic backups. How do I backup the SQL Server Agent configuration itself? If my database machine fails, I will need to recreate all

  • Why Isn't the SQL Server Agent Service Set to Automatic Startup by Default?November 15

    After a default install of SQL Server 2008 R2, the SQL Server Agent process seems to be set to manual startup. What is the reason for this? Is setting to automatic a bad idea or pointless for some reason? --------------Solutions------------- During t

  • Make SQL Server Agent retry failed script with Try/CatchNovember 30

    I have a script that has a try catch in it so that if it fails I can roll back the transaction. It runs on a job scheduled through SQL Server Agent. But when it fails SQL Server Agent does not see that it failed, I'm assuming because I catch the erro

Copyright (C) 2018 ceus-now.com, All Rights Reserved. webmaster#ceus-now.com 14 q. 0.735 s.