title: OPENQUERY "Deferred prepare could not be completed."
body: <p>Im trying to run OPENQUERY on a linked server without much success. I have 2 queries that are using a UNION operator. They run fine seperately and together when running them as a normal query. I am getting the following message when i try and run them via open query.</p>
  
  <pre><code>OLE DB provider "SQLNCLI11" for linked server "IIS900011" returned message "Deferred prepare could not be completed."
  Msg 8180, Level 16, State 1, Line 1
  Statement(s) could not be prepared.
  Msg 102, Level 15, State 1, Line 35
  Incorrect syntax near 'time'.
  Msg 102, Level 15, State 1, Line 13
  Incorrect syntax near 'Monday'.
  </code></pre>
  
  <p>My query is as follows:</p>
  
  <pre><code>    SELECT *
      FROM OPENQUERY(IIS900011, 
      'select
      sysjobs.name job_name
      ,sysjobs.enabled job_enabled
      ,sysschedules.name schedule_name
  ,sysschedules.freq_recurrence_factor
  ,case
   when freq_type = 4 then ''Daily''
  end frequency
  ,
  ''every '' + cast (freq_interval as varchar(3)) + '' day(s)''  Days
  ,
  case
   when freq_subday_type = 2 then '' every '' + cast(freq_subday_interval as varchar(7)) 
   + '' seconds'' + '' starting at ''
   + stuff(stuff(RIGHT(replicate(''0'', 6) +  cast(active_start_time as varchar(6)), 6), 3, 0, '':''), 6, 0, '':'')
   when freq_subday_type = 4 then '' every '' + cast(freq_subday_interval as varchar(7)) 
   + '' minutes'' + '' starting at ''
   + stuff(stuff(RIGHT(replicate(''0'', 6) +  cast(active_start_time as varchar(6)), 6), 3, 0, '':''), 6, 0, '':'')
   when freq_subday_type = 8 then '' every '' + cast(freq_subday_interval as varchar(7)) 
   + '' hours''   + '' starting at ''
   + stuff(stuff(RIGHT(replicate(''0'', 6) +  cast(active_start_time as varchar(6)), 6), 3, 0, '':''), 6, 0, '':'')
   else '' starting at '' 
   +stuff(stuff(RIGHT(replicate(''0'', 6) +  cast(active_start_time as varchar(6)), 6), 3, 0, '':''), 6, 0, '':'')
  end time
  
  from msdb.dbo.sysjobs
  inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
  inner join msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id
  where freq_type = 4')
  
  UNION  
  
  SELECT *
  FROM OPENQUERY(IIS900011,
  'select
   sysjobs.name job_name
  ,sysjobs.enabled job_enabled
  ,sysschedules.name schedule_name
  ,sysschedules.freq_recurrence_factor
  ,case
   when freq_type = 8 then ''Weekly''
  end frequency
  ,
  replace
  (
   CASE WHEN freq_interval&amp;1 = 1 THEN ''Sunday, '' ELSE '' END
  +CASE WHEN freq_interval&amp;2 = 2 THEN ''Monday, '' ELSE '' END
  +CASE WHEN freq_interval&amp;4 = 4 THEN ''Tuesday, '' ELSE '' END
  +CASE WHEN freq_interval&amp;8 = 8 THEN ''Wednesday, '' ELSE '' END
  +CASE WHEN freq_interval&amp;16 = 16 THEN ''Thursday, '' ELSE '' END
  +CASE WHEN freq_interval&amp;32 = 32 THEN ''Friday, '' ELSE '' END
  +CASE WHEN freq_interval&amp;64 = 64 THEN ''Saturday, '' ELSE '' END
  ,'', ''
  ,''
  ) Days
  ,
  case
   when freq_subday_type = 2 then '' every '' + cast(freq_subday_interval as varchar(7)) 
   + '' seconds'' + '' starting at ''
   + stuff(stuff(RIGHT(replicate(''0'', 6) +  cast(active_start_time as varchar(6)), 6), 3, 0, '':''), 6, 0, '':'') 
   when freq_subday_type = 4 then '' every '' + cast(freq_subday_interval as varchar(7)) 
   + '' minutes'' + '' starting at ''
   + stuff(stuff(RIGHT(replicate(''0'', 6) +  cast(active_start_time as varchar(6)), 6), 3, 0, '':''), 6, 0, '':'')
   when freq_subday_type = 8 then '' every '' + cast(freq_subday_interval as varchar(7)) 
   + '' hours''   + '' starting at ''
   + stuff(stuff(RIGHT(replicate(''0'', 6) +  cast(active_start_time as varchar(6)), 6), 3, 0, '':''), 6, 0, '':'')
   else '' starting at '' 
   + stuff(stuff(RIGHT(replicate(''0'', 6) +  cast(active_start_time as varchar(6)), 6), 3, 0, '':''), 6, 0, '':'')
  end time
  
  from msdb.dbo.sysjobs
  inner join msdb.dbo.sysjobschedules on sysjobs.job_id = sysjobschedules.job_id
  inner join msdb.dbo.sysschedules on sysjobschedules.schedule_id = sysschedules.schedule_id
  where freq_type = 8
  order by job_enabled desc')
  </code></pre>
  
  <p>I think im having a bit of a brain melt here as i cant see where the syntax error is, or if it is indeed a red herring. I havent used openquery much before so its probably something obvious i've missed.
  EDIT: I had missed some punctuation. Think im sorted now.</p>
  
  <pre><code>CASE WHEN freq_interval&amp;1 = 1 THEN ''Sunday, '' ELSE '''' END
  +CASE WHEN freq_interval&amp;2 = 2 THEN ''Monday, '' ELSE '''' END
  +CASE WHEN freq_interval&amp;4 = 4 THEN ''Tuesday, '' ELSE '''' END
  +CASE WHEN freq_interval&amp;8 = 8 THEN ''Wednesday, '' ELSE '''' END
  +CASE WHEN freq_interval&amp;16 = 16 THEN ''Thursday, '' ELSE '''' END
  +CASE WHEN freq_interval&amp;32 = 32 THEN ''Friday, '' ELSE '''' END
  +CASE WHEN freq_interval&amp;64 = 64 THEN ''Saturday, '' ELSE '''' END
  </code></pre>