0

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 = 'xxx.xxxxx@cxxx.com;xxx.xxxxx@cxxx.com
    ;xxx.xxxxx@cxxx.com;Bill.xxx.xxxxx@cxxx.com
    ;xxx.xxxxx@cxxx.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.

  • Is there any change in SSIS agent service? Maybe the user that runs the service? BTW, you're using and old version of X3, it is currently on version 12. – McNets Nov 11 at 22:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.