I created a SQL mail job on Thursday and it runs hourly. It ran fine until Sunday at 3 for which it has been failing with the following error.
Executed as user: NT AUTHORITY\SYSTEM. Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050) Query execution failed: Msg 229, Level 14, State 5, Server PRxxxxxxx3\Xxxx, Line 1 The SELECT permission was denied on the object 'MFGITMTRK', database 'xxxx', schema 'LIVE'. [SQLSTATE 42000] (Error 14661). The step failed.
SSIS is giving us the following error;
Execute SQL Task:Error: Executing the query "exec [dbo].[Dup_WO_Tracking_email]" failed with the following error: "Error formatting query, probably invalid parameters". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly
Here is the proc I am running via SSIS. It runs fine manually in SSMS and in VS so it appears to be some kind of permissions issue. Not sure why it would start all of a sudden.
ALTER PROCEDURE [dbo].[Dup_WO_Tracking_email] AS DECLARE @recordcount INT SELECT @recordcount = ISNULL(COUNT(*),0) FROM LIVE.MFGITMTRK WHERE CREDAT_0 >='2019-11-08 00:00:00.000' AND CREUSR_0='GE002' GROUP BY MFGNUM_0,CREDAT_0 HAVING COUNT(MFGNUM_0)>1 IF (@recordcount > 0) BEGIN EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL Mail', @recipients = 'email@example.com;firstname.lastname@example.org ;email@example.com;Bill.firstname.lastname@example.org ;email@example.com', @query = 'Select MFGNUM_0 FROM [PRODYCHDBX3\X3V7].[x3v7].[LIVE].[MFGITMTRK] WHERE CREDAT_0 >="2019-11-08 00:00:00.000" AND CREUSR_0="GE002" GROUP BY MFGNUM_0,CREDAT_0 HAVING COUNT(MFGNUM_0)>1', @subject = ' Duplicate Work Order Production Tracking', -@attach_query_result_as_file = 1, @body =0;
Not sure why it stopped all of a sudden. Thanks in advance.