msdb.dbo.sp_send_dbmail running in SQL Agent

Question

Hello

I have a SSIS package that all runs fine in Visual Studio, but fails when it runs in SQL Agent, most of the Tasks work and 95% of the time the package completes successfully, it just when it hits my Execute SQL task that has msdb.dbo.sp_send_dbmail in it. It will fail with this error;

Microsoft (R) SQL Server Execute Package Utility Version 14.0.2037.2 for 64-bit Copyright (C) 2017 Microsoft. All rights reserved. Started: 13:20:19 Error: 2024-10-29 13:20:58.73 Code: 0xC002F210 Source: Send Muliple Line errors to Accounts email Execute SQL Task Description: Executing the query "DECLARE @Todays_Count int DECLARE @copy_to varcha..." failed with the following error: "Failed to initialize sqlcmd library with error number -2147467259.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 13:20:19 Finished: 13:20:58 Elapsed: 39.234 seconds. The package execution failed. The step failed.

This is what is in my Execute SQL task.

DECLARE @Todays_Count int

DECLARE @copy_to varchar(max)= 'ABC@MSN.com'+';'+'DEF@MSN.com'+';'+'GHI@MSN.com'

SELECT @Todays_Count = (SELECT Count(*) as Error_Count

  FROM [DataBase].[dbo].[ABC]

  where Cast([nomval] as Money) = 0)

IF @Todays_Count > 0

  exec msdb.dbo.sp_send_dbmail @recipients = @copy_to, 

@subject = 'Invoice with Lines at zero value',

@body = 'The invoice details are below is an Invoice in Compleat that has zero value lines in the data.',

@query_result_header = 1,

@profile_name = 'Mail_Profile',

@query = N'Select description as Invoice_Error

from [DataBase].[dbo].[DEF]',

@execute_query_database = N'msdb'

I can't work out why. I have given the user that runs the Job task databasesendmail role under the MSDB, but that has not worked.

Any help would be great.

Answer 1

This is most likely because the user running the SSIS package in SQL Agent is either not a login in that server.

You should create a proxy for a user (credential for a windows account) and have that user as a login in SQL and give that login rights to send dbmail. By default an SSIS package runs under SQL Agent and by default the login for Agent is NT Service\SQLAGENT$INSTANCE or NT Service\SQLSERVERAGENT and SSIS is an external process so it cannot login outside of SQL if it has an AD account as the service account.

Fix the login issue and this should be resolved.

Answer 2

Hi @Graham Rock

Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Please troubleshoot this issue as the message suggested.

• Review the SQL query and ensure it could retrieve the expected results.
• Make sure the Result Set property of the task is set correctly.
• Ensure the connection to the database is properly established.

Besides, try run this agent job with a proxy account which is given with enough permission.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

Hello

I solved the issue, the SQL Agent windows account didn't have access to the user database I was running a query on, which stopped it from working.

So I had given it access to the MSDB, but not the user database which is [DataBase].[dbo].[DEF] in my example SQL script.

Thank you for your help.

Regards

Graham Rock