Also posted in Stack Overflow. Any feed back would be much appreciated.
I have a need for a SQL job to send multiple emails, and I have come across this error that I am unable to find any solutions for. When I send emails this way is succeeds every time I have tested.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AzureManagedInstance_dbmail_profile',
@recipients = '*Valid Email Address*',
@body = 'test',
@subject = 'Testing';
When I add a query to the email occasionally the job will fail.
I have pulled out a snippet and simplified the SELECT query that replicates the issue.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AzureManagedInstance_dbmail_profile',
@recipients = '*Valid Email Address*',
@body = 'test',
@query =
'
SELECT GETDATE()
',
@subject = 'Testing';
When I run this it successfully sends me an email with the date time, if I run the code again I get the generic error, but not always.
Failed to initialize sqlcmd library with error number -2147467259.
I did some digging with SQL Server Profiler and came across this error message
The connection has been dropped because the principal that opened it subsequently assumed a new security context, and then tried to reset the connection under its impersonated security context. This scenario is not supported. See "Impersonation Overview" in Books Online.
I am currently using a SA account but started with minimal viable privileges.
I have tried running the job under the generic email profile, with the same results.
The job that runs this code will succeed and fail for no apparent reason that I can find. There doesn't seem to be a pattern to the job running successfully or not either.
Has anyone come across this issue and managed to resolve it? Or is anyone able to point me in a direction to solve this?
