Linked server view Select query doesn't execute on production server

Question

I've build one view. I can run sys.databases from the linked server. However, when I tried to execute the following view. It is not retrieving any data from the linked server.

USE [SSISDB]

GO

/****** Object:  View [dbo].[Vw_SSISDBLog]    Script Date: 8/29/2023 1:38:03 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




CREATE     VIEW [dbo].[Vw_SSISDBLog]
AS
SELECT  [execution_id]
      ,[folder_name]
      ,[project_name]
      ,[package_name]
      ,[executed_as_name]
      ,[status]
      ,[start_time]
      ,[end_time]
      ,CONVERT (DATETIME2,[start_time]) AS start_date
    ,CONVERT (DATETIME2, [end_time]) AS end_date
    --,CONVERT(CHAR(10),[start_time],108) AS starttime
    --,CONVERT(CHAR(10),[end_time],108) AS endtime
    ,DATEDIFF(SECOND,CONVERT(DATETIME,[start_time]),CONVERT(DATETIME,[end_time])) AS 'Duration In Seconds'
    --CONVERT(,DATEDIFF(millisecond, [start_time], ISNULL([end_time], SYSDATETIMEOFFSET())))/1000 AS duration,
    
     , [process_id]
      ,[stopped_by_sid]
      ,[stopped_by_name]
      ,[server_name]
      ,[machine_name]
      ,CASE 
                    WHEN [status] = 1 THEN 'Created'
                    WHEN [status] = 2 THEN 'Running'
                    WHEN [status] = 3 THEN 'Canceled'
                    WHEN [status] = 4 THEN 'Failed'
                    WHEN [status] = 5 THEN 'Pending'
                    WHEN [status] = 6 THEN 'Ended Unexpectedly'
                    WHEN [status] = 7 THEN 'Succeeded'
                    WHEN [status] = 8 THEN 'Stopping'
                    WHEN [status] = 9 THEN 'Completed'
                   END AS [status desc]
 --, Year(CONVERT (datetime, [start_time])) As start_time_year
  --,DATEADD(DAY,-1,CONVERT (datetime, [start_time])) AS PREVIOUSDT
 --, DateName(mm, CONVERT (datetime, [start_time])) As start_time_month_name

  FROM [SSISDB].[internal].[execution_info]
  WHERE 
--  [STATUS] IN (3,4,6,8)
  --AND CONVERT (datetime, [start_time]) BETWEEN  DATEADD(DAY,-2,CONVERT (CHAR(10),[start_time],126)) And SYSDATETIME()
 --AND 
CONVERT (CHAR(10),[start_time],126)>= CAST(DATEADD(DAY, -2,SYSDATETIME()) AS DATE)
AND CONVERT(CHAR(10),[start_time],126) <= CAST(SYSDATETIME() AS DATE)
  --ORDER BY execution_id DESC
GO

Above is my view which has 216 rows. However, I'm not able to retrieve using linked server.

On the other hand, when I tried to run same database view. It works just fine.

    USE [SSISDB]
    USE [SSISDB]
    GO
    
   

     /****** Object:  View [dbo].[Vw_JobDetailsS1]    Script Date: 8/29/2023 1:43:47 PM ******/
        SET ANSI_NULLS ON
        GO
        
        SET QUOTED_IDENTIFIER ON
        GO
        
        
        
        

    CREATE     VIEW [dbo].[Vw_JobDetailsS1]
    AS
    SELECT 
     --SERVERPROPERTY('Servername') AS ServerName,
    --categories.NAME AS CategoryName,
     jobs.name
     ,SUSER_SNAME(jobs.owner_sid) AS OwnerID
     ,CASE jobs.enabled WHEN 1 THEN 'Yes' ELSE 'No'END AS Enabled
     ,CASE schedule.enabled WHEN 1 THEN 'Yes' ELSE 'No' END AS Scheduled
     ,jobs.description
     ,CASE WHEN jobs.description ='This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job.' THEN 'Yes' ELSE 'No' END AS ReportServerJob
     ,CASE schedule.freq_type
      WHEN 1 THEN 'Once'
      WHEN 4 THEN 'Daily'
      WHEN 8 THEN 'Weekly'
      WHEN 16 THEN 'Monthly'
      WHEN 32 THEN 'Monthly relative'
      WHEN 64 THEN 'When SQL Server Agent starts'
      WHEN 128 THEN 'Start whenever the CPU(s) become idle'
      ELSE ''
      END AS FrequencyType
     ,CASE schedule.freq_type
      WHEN 1 THEN 'O'
      WHEN 4 THEN 'Every ' + CONVERT(VARCHAR, schedule.freq_interval) + ' day(s)'
      WHEN 8 THEN 'Every ' + CONVERT(VARCHAR, schedule.freq_recurrence_factor) + ' weeks(s) on ' + LEFT(CASE 
          WHEN schedule.freq_interval & 1 = 1
           THEN 'Sunday, '
          ELSE ''
          END + CASE 
          WHEN schedule.freq_interval & 2 = 2
           THEN 'Monday, '
          ELSE ''
          END + CASE 
          WHEN schedule.freq_interval & 4 = 4
           THEN 'Tuesday, '
          ELSE ''
          END + CASE 
          WHEN schedule.freq_interval & 8 = 8
           THEN 'Wednesday, '
          ELSE ''
          END + CASE 
          WHEN schedule.freq_interval & 16 = 16
           THEN 'Thursday, '
          ELSE ''
          END + CASE 
          WHEN schedule.freq_interval & 32 = 32
           THEN 'Friday, '
          ELSE ''
          END + CASE 
          WHEN schedule.freq_interval & 64 = 64
           THEN 'Saturday, '
          ELSE ''
          END, LEN(CASE 
           WHEN schedule.freq_interval & 1 = 1
            THEN 'Sunday, '
           ELSE ''
           END + CASE 
           WHEN schedule.freq_interval & 2 = 2
            THEN 'Monday, '
           ELSE ''
           END + CASE 
           WHEN schedule.freq_interval & 4 = 4
            THEN 'Tuesday, '
           ELSE ''
           END + CASE 
           WHEN schedule.freq_interval & 8 = 8
            THEN 'Wednesday, '
           ELSE ''
           END + CASE 
           WHEN schedule.freq_interval & 16 = 16
            THEN 'Thursday, '
           ELSE ''
           END + CASE 
           WHEN schedule.freq_interval & 32 = 32
            THEN 'Friday, '
           ELSE ''
           END + CASE 
           WHEN schedule.freq_interval & 64 = 64
            THEN 'Saturday, '
           ELSE ''
           END) - 1)
      WHEN 16
       THEN 'Day ' + CONVERT(VARCHAR, schedule.freq_interval) + ' of every ' + CONVERT(VARCHAR, schedule.freq_recurrence_factor) + ' month(s)'
      WHEN 32
       THEN 'The ' + CASE schedule.freq_relative_interval
         WHEN 1
          THEN 'First'
         WHEN 2
          THEN 'Second'
         WHEN 4
          THEN 'Third'
         WHEN 8
          THEN 'Fourth'
         WHEN 16
          THEN 'Last'
         END + CASE schedule.freq_interval
         WHEN 1
          THEN ' Sunday'
         WHEN 2
          THEN ' Monday'
         WHEN 3
          THEN ' Tuesday'
         WHEN 4
          THEN ' Wednesday'
         WHEN 5
          THEN ' Thursday'
         WHEN 6
          THEN ' Friday'
         WHEN 7
          THEN ' Saturday'
         WHEN 8
          THEN ' Day'
         WHEN 9
          THEN ' Weekday'
         WHEN 10
          THEN ' Weekend Day'
         END + ' of every ' + CONVERT(VARCHAR, schedule.freq_recurrence_factor) + ' month(s)'
      ELSE ''
      END AS Occurence
     ,CASE schedule.freq_subday_type
      WHEN 1
       THEN 'Occurs once at ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_start_time), 6), 5, 0, ':'), 3, 0, ':')
      WHEN 2
       THEN 'Occurs every ' + CONVERT(VARCHAR, schedule.freq_subday_interval) + ' Seconds(s) between ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_start_time), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_end_time), 6), 5, 0, ':'), 3, 0, ':')
      WHEN 4
       THEN 'Occurs every ' + CONVERT(VARCHAR, schedule.freq_subday_interval) + ' Minute(s) between ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_start_time), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_end_time), 6), 5, 0, ':'), 3, 0, ':')
      WHEN 8
       THEN 'Occurs every ' + CONVERT(VARCHAR, schedule.freq_subday_interval) + ' Hour(s) between ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_start_time), 6), 5, 0, ':'), 3, 0, ':') + ' and ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), schedule.active_end_time), 6), 5, 0, ':'), 3, 0, ':')
      ELSE ''
      END AS Frequency
     --,CONVERT(DECIMAL(10, 2), jobhistory.AverageDurationInSeconds) AverageDurationSeconds
     --,CONVERT(VARCHAR, DATEADD(s, ISNULL(CONVERT(DECIMAL(10, 2), jobhistory.AverageDurationInSeconds), 0), 0), 108) AS AverageDuration
     ,CASE jobschedule.next_run_date
      WHEN 0
       THEN CONVERT(DATETIME, '1900/1/1')
      ELSE CONVERT(DATETIME, CONVERT(CHAR(8), jobschedule.next_run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), jobschedule.next_run_time), 6), 5, 0, ':'), 3, 0, ':'))
      END NextScheduledRunDate
    ,lastrunjobhistory.LastRunDate
    ,ISNULL(lastrunjobhistory.run_status_desc,'Unknown') AS run_status_desc
    ,ISNULL(lastrunjobhistory.RunTimeInSeconds, 0) AS RunTimeInSeconds
    ,CONVERT(VARCHAR, DATEADD(s, ISNULL(lastrunjobhistory.RunTimeInSeconds, 0), 0), 108) AS RunTime
    ,lastrunjobhistory.message  
    FROM msdb.dbo.sysjobs AS jobs
    LEFT JOIN msdb.dbo.sysjobschedules AS jobschedule
     ON jobs.job_id = jobschedule.job_id
    LEFT JOIN msdb.dbo.sysschedules AS schedule
     ON jobschedule.schedule_id = schedule.schedule_id
    INNER JOIN msdb.dbo.syscategories categories
     ON jobs.category_id = categories.category_id
     AND jobs.enabled=1
    
     --LEFT OUTER JOIN (
    -- SELECT sysjobhist.job_id
    --  ,(SUM(((sysjobhist.run_duration / 10000 * 3600) + ((sysjobhist.run_duration % 10000) / 100 * 60) + (sysjobhist.run_duration % 10000) % 100)) * 1.0) / Count(sysjobhist.job_id) AS AverageDurationInSeconds
    -- FROM msdb.dbo.sysjobhistory AS sysjobhist
    -- WHERE sysjobhist.step_id = 0
    -- GROUP BY sysjobhist.job_id
    -- ) AS jobhistory
    -- ON jobhistory.job_id = jobs.job_id  -- to get the average duration
    LEFT OUTER JOIN
    (
    SELECT sysjobhist.job_id
     ,CASE sysjobhist.run_date
      WHEN 0
       THEN CONVERT(DATETIME, '1900/1/1')
      ELSE CONVERT(DATETIME, CONVERT(CHAR(8), sysjobhist.run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), sysjobhist.run_time), 6), 5, 0, ':'), 3, 0, ':'))
      END AS LastRunDate
     ,sysjobhist.run_status
     ,CASE sysjobhist.run_status
      WHEN 0
       THEN 'Failed'
      WHEN 1
       THEN 'Succeeded'
      WHEN 2
       THEN 'Retry'
      WHEN 3
       THEN 'Canceled'
      WHEN 4
       THEN 'In Progress'
      ELSE 'Unknown'
      END AS run_status_desc
     ,sysjobhist.retries_attempted
     ,sysjobhist.step_id
     ,sysjobhist.step_name
     ,sysjobhist.run_duration AS RunTimeInSeconds
     ,sysjobhist.message
     ,ROW_NUMBER() OVER (
      PARTITION BY sysjobhist.job_id ORDER BY CASE sysjobhist.run_date
        WHEN 0
         THEN CONVERT(DATETIME, '1900/1/1')
        ELSE CONVERT(DATETIME, CONVERT(CHAR(8), sysjobhist.run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), sysjobhist.run_time), 6), 5, 0, ':'), 3, 0, ':'))
        END DESC
      ) AS RowOrder
    FROM msdb.dbo.sysjobhistory AS sysjobhist
    WHERE sysjobhist.step_id = 0  --to get just the job outcome and not all steps
    )AS lastrunjobhistory
     ON lastrunjobhistory.job_id = jobs.job_id  -- to get the last run details
     AND
     lastrunjobhistory.RowOrder=1
    
     --select * into #temp2 from jobinfoS1_CTE;
     --drop table #temp;
    -- select * from #temp2;
    --declare @xml nvarchar(max) 
    --set @xml = CAST(( SELECT --[ServerName] AS 'td','',
    ----[CategoryName] AS 'td','', 
    --[name] AS 'td','', 
    --[OwnerID] AS 'td','',
    --[Enabled] AS 'td','',
    --[Scheduled] As 'td','',
    --[description] As 'td','',
    --[ReportServerJob] as 'td','',
    --[FrequencyType] as 'td','',
    --[Occurence] as 'td','',
    --[Frequency] as 'td','',
    --[NextScheduledRunDate] as 'td','',
    --[LastRunDate] as 'td','',
    --[run_status_desc] as 'td','',
    --[RunTimeInSeconds] as 'td','',
    --[RunTime] as 'td','',
    --[message] as 'td'
    --FROM #temp2
    --ORdeR BY [Name] 
    --FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))
    
    --declare @body nvarchar(max)='<html><body><H3>'+@@SERVERName+' Job Detailed Information</H3>
    --<style>body {font-family:Arial; font-size:11;} table {border-collapse:collapse;}</style>
    --<table border = 1>
    
    --<tr>
    --<th> Server Name </th> <th> Category Name </th> <th> Name </th>
    --<th> OwnerId </th><th> Enabled </th><th> Scheduled </th><th> Description </th>
    --<th> ReportServerJob </th> <th> FequencyType </th> <th> Occurence </th>
    --<th> Frequency </th> <th> NextScheduledRunDate </th> <th> LastRunDate </th>
    --<th> run_status_desc </th> <th> RunTimeInSeconds </th> <th> RunTime </th>
    --<th> message </th>
    --</tr>'    
    
    --SET @body = @body + @xml +'</table></body></html>'
    
    --set @body = '<table cellpadding="2" cellspacing="2" border="1">'
              
    --          + replace( replace( @body, '&lt;', '<' ), '&gt;', '>' )
    --          + '</table>'
    
    
    
    
    GO

Also, I'm using following select statement to retrieve data from linkedserver.

'select * from SSISDB.dbo.Vw_SSISDBLog')

SELECT * FROM OPENQUERY([ETL],
'select name,OwnerId,Enabled,Scheduled,description,ReportServerJob,FrequencyType,
Occurence,Frequency,NextScheduledRunDate,LastRunDate,run_status_desc,
runTime,runTimeInSeconds,message from SSISDB.dbo.Vw_JobDetailsS1')

Any advise would be appreciated