May 9, 2021 at 3:49 am
Hi
I have a job running with a stored procedure that contains this code:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'dbMailProfile',
@recipients = 'recipents@scrubbed.com
@subject = 'MTC Scrap Transactions',
@query = N'SELECT [Transaction Type]
,[Schedule Number]
,[OLSN]
,[Release_Number]
,[Production Model Name]
,[Trans Qty in Std UM]
,[Unit of Measure]
,[Transaction Detail Code]
,[Trans_Time]
,[Plant_Number]
FROM [QA_Scrap].[dbo].[THE_TABLE]',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Scrap Transactions.txt',
@query_result_header=1;
The job always fails with the message: " Failed to initialize sqlcmd library with error number -2147467259"
When I run the stored procedure manually under the sa account, it works fine with no errors. But when I run it using the SQL Agent, I get the message above. The SQL Agent account does have read/write/execute permissions on msdb database. I have stopped and re-started both the SQL Server and also the Agent, but no success. Anyone have an idea on the cause of this issue? This is SQL 2016.
May 9, 2021 at 3:50 pm
I tried copying your code as-is into Azure Data Studio - it immediately shows some problems. Redgate's SQL Prompt won't even format the code due to these problems. You are missing single-quotes and commas throughout the code.
Reformatting the code - adding in the missing commas and single-quotes:
Execute msdb.dbo.sp_send_dbmail
@profile_name = 'dbMailProfile'
, @recipients = 'recipents@scrubbed.com'
, @subject = 'MTC Scrap Transactions'
, @query = N'
SELECT [Transaction Type]
, [Schedule Number]
, [OLSN]
, [Release_Number]
, [Production Model Name]
, [Trans Qty in Std UM]
, [Unit of Measure]
, [Transaction Detail Code]
, [Trans_Time]
, [Plant_Number]
FROM [QA_Scrap].[dbo].[THE_TABLE];'
, @attach_query_result_as_file = 1
, @query_attachment_filename = 'Scrap Transactions.txt'
, @query_result_header = 1;
Looking at the command - you need to change the context of the database where the code will be executed:
Execute msdb.dbo.sp_send_dbmail
@profile_name = 'dbMailProfile'
, @recipients = 'recipents@scrubbed.com'
, @subject = 'MTC Scrap Transactions'
, @execute_query_database = 'QA_Scrap'
, @query = N'
SELECT [Transaction Type]
, [Schedule Number]
, [OLSN]
, [Release_Number]
, [Production Model Name]
, [Trans Qty in Std UM]
, [Unit of Measure]
, [Transaction Detail Code]
, [Trans_Time]
, [Plant_Number]
FROM [dbo].[THE_TABLE];'
, @attach_query_result_as_file = 1
, @query_attachment_filename = 'Scrap Transactions.txt'
, @query_result_header = 1;
Of course - this appears to have been 'sanitized' which may be hiding the actual problem.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 9, 2021 at 10:37 pm
Thank you for looking at this. The formatting errors you mentioned above were due to my sanitization of the code before posting. I made the changes that you suggested, but am still receiving the same error message.
I feel that my SQL Agent service account is missing a permission since it runs OK when I login as sa, but fails under job execution. The service account does have DatabaseMailUserRole on msdb, but maybe it needs other permissions?
May 10, 2021 at 9:08 am
Hi,
who is the job owner of your job, if it fails?
Is this your privat account, or have you ever tried with sa account?
Kind regards,
Andreas
May 10, 2021 at 2:05 pm
I feel that my SQL Agent service account is missing a permission since it runs OK when I login as sa, but fails under job execution. The service account does have DatabaseMailUserRole on msdb, but maybe it needs other permissions?
That would have been valuable information to post with the original issue. If it works when the job is owned by sa - why not just leave it that way?
To understand this type of issue, you need to understand how SQL Server agent authenticates. When the owner of the job is a sysadmin - the job is executed in the context of the user running the agent. When the job is owned by a non-sysadmin it is run in the context of the owner of the job.
Just because a login has access to the agent - does not mean that login has access to the specific database. Almost certainly this is a permissions issue and that is causing the problems.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply