I suspect this to be some sort of security permission issue
I've been working on correcting this issue for quite (Months) a while when sending DB Mail from a SQL job. I created one SQL job executing a stored procedure that queries another servers database & tables. Then the results are stored in a variable and emailed to users daily.
Here is my setup:
- Linked server A to linked server B using a SQL user named 'xxxdba'
- Server type SQL Server in Linked Server Properties
- On both servers I'm using "Be made using this security context"
- The user has the database role "DatabaseMailUserRole"
- Job owner is NT Service/SQLSERVERAGENT
- The job is ran as xxxdba in the job step advanced area
On both servers A & B the user has read/write user mappings to the tables in the query which I have supplied below. Under the database itself, the user has execute to the stored procedure that I created.
Job step properties:
- Step name
- Type T-SQL
- Run As (Blank)
- Database (Win)
- Command: Execute dbo.procedure_db_mail
Procedure that was created:
CREATE PROCEDURE PROCEDURE_DB_MAIL
AS
Declare @Qry varchar(5000) = 'EXEC XXXXXX.WIN.DBO.[Table_Roll_View]' --VIEW
SET @Qry = N'SELECT [Previous Day Table Roll Information] --Stored Procedure
From MSDB.DBO.sysjobs'
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'Jwendt@xxxxxxx.com;',
@Query = @Qry,
@query_result_header = 1,
@exclude_query_output = 0,
@execute_query_database = 'Win',
@subject = 'Table Roll Data',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Table Roll Data.txt',
@append_query_error = 1,
@profile_name = 'SQL Mail Alert'
--------------------------------------------
View Information:
CREATE VIEW Table_Roll_View
AS
SELECT DISTINCT a.Timestamp, a.DocGamingDate,--, t1.ExternalGameDay, t1.InternalGameDay,
a.Document_ID, a.DocType, --t1.ExternalStatus,
t1.InternalStatus, a.DocStatus, a.DocNumber, b.Name, t1.Description, T1.XXXXGameName, b.Location_ID, T1.PitID, T1.TableID
From OPENQUERY([XXXXITDB], 'Select DISTINCT b.PitID, b.TableID, b.Description, a.GameCode, b.XXXXGameName, b.XXXXTableName, b.InternalStatus --, b.ExternalStatus
, b.ExternalGameDay, b.InternalGameDay
From [XXXXIT].[dbo].[CurrentTableConfig] a
INNER JOIN [XXXXIT].[dbo].[Tables] b
on a.SAKEY = b.TableSAKEY'
) t1
INNER JOIN [Win].[dbo].[XX_Location] b
on b.Name = t1.XXXXTableName
INNER JOIN [Win].[dbo].[XX_Document] a
ON a.Location_ID = b.location_ID
Where a.DocGamingDate = CAST(GETDATE() -1 AS Date) --Looking at yesterday's table processes
--OR a.DocGamingDate = GETDATE() --Including today's table openers
and t1.description NOT LIKE '%zz%'
--------------------------------------------------
Execute the job (I'm a sys admin on all servers)
SQL History Message:
Executed as user: xxxdba. Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed.
Windows Application Log:
SQL Server Scheduled Job 'Previous Day Table Roll Information' (0x74F50053DC25DD439C6438B586359845) - Status: Failed - Invoked on: 2021-02-24 10:19:46 - Message: The job failed. The Job was invoked by User XXXXXXXXXXX\jwendt. The last step to run was step 1 (Execute and email).
Additional notes:
- I made the linked server credential a system admin for testing purposes and got the same error.
- I get the same result if I execute the SP by itself outside of the job
- If I run as myself, the results are the same.
- Shared Memory, Named Pipes and TCP/IP are enabled on both servers
- Data Access, RPC, RPC Out, Use Remote Collation and Enable Promotion of Distributed Transaction is set to True in the linked server properties
----------------------------------------------------------
----------------------------------------------------------
I have the same issue on another server and here is where it gets VERY confusing. I have the same setup and permissions for the user above. The difference is, I have an email that goes out preload and post load.
Management gets an email for a point multiplier that customers earn playing Bingo on Sunday's. This email works correctly (Preload). Once the post load time has passed, the email fails. If the local server time is 10AM then management would already have received their email at 7AM. If the post load time is set to 11AM and the local server time >= 11AM the job fails with the SQLCMD Library error.
