Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
user45
Starting Member
10 Posts |
 Posted - 2012-02-17 : 05:48:24
|
| Hi I have set up a database mail account on my server to send emails. The test worked fine so I wrote my own script to send the results of an SQL statement as a .txt file to an email account:EXEC msdb.dbo.[sp_send_dbmail]@recipients=N'test@test.com',@body='Message Body', @subject ='Message Subject',@profile_name ='DBMailProfile',@query ='select TOP 20(productcode) as ProductCode from [Test Database].dbo.product',@attach_query_result_as_file = 1,@query_attachment_filename ='Results.txt'This works when I run it as a query in SSMS. But what I want to be able to do is have this executing as a job every night so the results of the query are sent to the recipient test@test.comUsing SQL Server Agent I tried creating a job to execute the statement but this didnt work. Giving an error:"Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed."I'm pretty lost so any help would be appreciated!! |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-02-17 : 06:10:29
|
| The account in you are running the job - is it explicitly set in SQL Server - with the relevant admin privileges?Jack Vamvas--------------------http://www.sqlserver-dba.com |
 |
|
|
user45
Starting Member
10 Posts |
Posted - 2012-02-17 : 06:27:57
|
| I'm not sure I fully understandDo you mean when I set up the database mail account? I set the account as a public profile so shouldn't this mean that it can be accessed by any users of the mail host db |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-02-18 : 11:11:38
|
| i had a similar problem , where i hadn't explicitly added the sql server agent account and assigned specific permissions. Once i added the account and assigned permissions , this solved the problemJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
user45
Starting Member
10 Posts |
Posted - 2012-02-20 : 03:44:35
|
| Where exactly can I assign permissions? |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-02-21 : 01:34:36
|
| Which account are you using to run the SQL Server agent job? Is it the domain account ? Have you added the domain account as a SQL Server Logon with explicit permissions?Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
user45
Starting Member
10 Posts |
Posted - 2012-02-21 : 09:07:44
|
| Yes i'm running the job as with the domain account and gave this account sysadmin permissions but it still did not work.I figured out what to do. I had to open SQL Sever Configuration Manager, right click SQL Server-->properties-->Log on as "my account" and do the same for SQL Server Agent. I was logged on as a local service account for SQL Server and not "my account" so this is why the job was failing.This is probably what you meant in your post but I didnt know about SQL Sever Configuration Manager. Thanks for your help |
|
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-02-22 : 01:43:55
|
| Yes, we're talking about the same thing . i.e explicit logon of the account with relevant permissionsJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|
|
SSMS Database Mail