title: SQL Script Errors - Conversion Failed
body: <p>I'm using SQL Server 2008 R2. I've gotten below SQL script from Internet which is used to monitor SQL log shipping, see below: </p>
  
  <p><div class="snippet" data-lang="js" data-hide="false">
  <div class="snippet-code">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>USE [master]
  GO
  
  begin
      set nocount on
   DECLARE @Recipients varchar(275)
   DECLARE @Subject varchar(275)
   DECLARE @Body varchar(MAX)
   DECLARE @Server varchar(25)   
      DECLARE @idx int, @Status varchar(25), @DB varchar(25), @LastCopy varchar(6), @LastCFile varchar(250),
    @LastRestore varchar(6), @LastRFile varchar(250), @Latency varchar(3), @Threshold varchar(3)
      declare @retcode int
                  ,@primary_id uniqueidentifier
                  ,@primary_server sysname
                  ,@primary_database sysname
                  ,@backup_threshold int
                  ,@is_backup_alert_enabled bit
                  
                  ,@secondary_id uniqueidentifier
                  ,@secondary_server sysname
                  ,@secondary_database sysname
                  ,@restore_threshold int
                  ,@is_restore_alert_enabled bit
                  ,@last_copied_file nvarchar(500)
                  ,@last_copied_utc datetime
                  ,@time_since_last_copy int
                  ,@last_restored_file nvarchar(500)
                  ,@last_restored_utc datetime
                  ,@time_since_last_restore int
                  ,@last_restored_latency int
  
                  ,@prev_primary_server sysname
                  ,@prev_primary_database sysname
                  
                  ,@monitor_server sysname
                  ,@monitor_server_security_mode int
                  ,@is_monitor_local bit
                  ,@curutcdate datetime
                  ,@linkcmd nvarchar(4000)
  
  SET @Recipients = 'you@company.com'
  SET @Body = ''
  SET @Server = @@SERVERNAME
  SET @Subject = @Server + ' :: Daily Transaction Log Shipping Status' 
  SET @Body = '&lt;p style="font-size:12px;font-family:Verdana"&gt;&lt;b&gt;' + @Server + ': &lt;/b&gt;&lt;/font&gt;&lt;hr style="width: 100%; height: 1px;"&gt; ' 
      create table #log_shipping_monitor
      (
    idx int identity(1,1)
          ,status bit null
          ,is_primary bit not null default 0
          ,server sysname 
          ,database_name sysname
          ,is_backup_alert_enabled bit null
          ,time_since_last_copy int null
          ,last_copied_file nvarchar(500) null
          ,time_since_last_restore int null
          ,last_restored_file nvarchar(500) null
          ,last_restored_latency int null
          ,restore_threshold int null
          ,is_restore_alert_enabled bit null
          ,ts timestamp not null
          ,primary key (is_primary, server, database_name)
          ,unique (ts)
      )
  
      --
      -- create other tables we will use
      --
      create table #secondary_monitor
      (
          secondary_server sysname not null,
          secondary_database sysname not null,
          secondary_id uniqueidentifier not null,
          primary_server sysname not null,
          primary_database sysname not null,
          restore_threshold int not null, 
          threshold_alert int not null, 
          threshold_alert_enabled bit not null, 
          last_copied_file nvarchar(500) null, 
          last_copied_date datetime null, 
          last_copied_date_utc datetime null, 
          last_restored_file nvarchar(500) null, 
          last_restored_date datetime null, 
          last_restored_date_utc datetime null, 
          last_restored_latency int null, 
          history_retention_period int not null, 
          primary key (secondary_id, secondary_database)
      )
  
      create table #primary_monitor 
      (
          primary_id uniqueidentifier primary key not null,
          primary_server sysname not null,
          primary_database sysname not null,
          backup_threshold int not null,  
          threshold_alert int not null, 
          threshold_alert_enabled bit not null, 
          last_backup_file nvarchar(500) null, 
          last_backup_date datetime null, 
          last_backup_date_utc datetime null, 
          history_retention_period int not null,
          unique (primary_server, primary_database)
      )
  
      --
      -- get current time
      --
      select @curutcdate = getutcdate()
      --
      -- Enumerate the primary entries
      --
      declare #hcprimaries cursor local fast_forward for
          select 
              primary_id
              ,primary_server 
              ,primary_database 
              ,backup_threshold 
              ,threshold_alert_enabled
          from msdb.dbo.log_shipping_monitor_primary with (nolock)
          order by primary_server, primary_database
          
      open #hcprimaries
      fetch #hcprimaries into @primary_id, @primary_server, @primary_database, @backup_threshold, @is_backup_alert_enabled
      while (@@fetch_status != -1)
      begin
          --
          -- we have a primary entry
          --
          insert into #log_shipping_monitor (
              status
              ,is_primary
              ,server
              ,database_name
              ,is_backup_alert_enabled)
          values (
              1
              ,@primary_server
              ,@primary_database
              ,@backup_threshold
              ,@is_backup_alert_enabled)
          --
          -- process secondaries
          --
          if (upper(@primary_server) = upper(@@servername))
          begin
              --
              -- we are on primary server
              -- get monitor server information
              --
              select @monitor_server = monitor_server
                          ,@monitor_server_security_mode = monitor_server_security_mode
              from msdb.dbo.log_shipping_primary_databases
              where primary_id = @primary_id
              select @is_monitor_local = case when (upper(@monitor_server) = upper(@@servername)) then 1 else 0 end
              --
              -- enumerate the secondaries listed on primary
              --
              declare #hcprimarysecondaries cursor local fast_forward for
                  select secondary_server, secondary_database
                  from msdb.dbo.log_shipping_primary_secondaries with (nolock)
                  where primary_id = @primary_id
  
              open #hcprimarysecondaries
              fetch #hcprimarysecondaries into @secondary_server, @secondary_database
              while (@@fetch_status != -1)
              begin
                  --
                  -- add this primary secondary to result set
                  --
                  insert into #log_shipping_monitor (is_primary ,server, database_name)
                      values (0, @secondary_server, @secondary_database)
                  select @secondary_id = NULL
                  --
                  -- Enumerate this secondary from msdb.dbo.log_shipping_monitor_secondary
                  --
                  if (@is_monitor_local = 1)
                  begin
                      --
                      -- local monitor
                      --
                      select  
                          @secondary_id = secondary_id
                          ,@restore_threshold = restore_threshold
                          ,@is_restore_alert_enabled = threshold_alert_enabled
                          ,@last_copied_file = last_copied_file
                          ,@last_copied_utc = last_copied_date_utc
                          ,@last_restored_file = last_restored_file
                          ,@last_restored_utc = last_restored_date_utc
                          ,@last_restored_latency = last_restored_latency
                      from msdb.dbo.log_shipping_monitor_secondary
                      where primary_server = upper(@primary_server)
                          and primary_database = @primary_database
                          and secondary_server = upper(@secondary_server)
                          and secondary_database = @secondary_database
                  end -- local monitor
                  else
                  begin
                      --
                      -- remote monitor
                      --
                      if (@monitor_server_security_mode = 0) and (suser_name() != SUSER_SNAME(0x01))
                      begin
                          --
                          -- execute as proxy
                          --
                          exec @retcode = sys.sp_MSproxylogshippingmonitorhelpsecondary
                                  @monitor_server = @monitor_server
                                  ,@p1 = @primary_server
                                  ,@p2 = @primary_database
                                  ,@p3 = @secondary_server
                                  ,@p4 = @secondary_database
                                  ,@p5 = @secondary_id output
                                  ,@p6 = @restore_threshold output
                                  ,@p7 = @is_restore_alert_enabled output
                                  ,@p8 = @last_copied_file output
                                  ,@p9 = @last_copied_utc output
                                  ,@p10 = @last_restored_file output
                                  ,@p11 = @last_restored_utc output
                                  ,@p12 = @last_restored_latency output                                
                      end
                      else
                      begin
                          delete #secondary_monitor
                          select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.master.sys.sp_help_log_shipping_monitor_secondary '
                                  ,@retcode = 0
                          begin try
                              insert into #secondary_monitor
                              exec @retcode = @linkcmd 
                                          @secondary_server = @secondary_server
                                          ,@secondary_database = @secondary_database
                          end try
                          begin catch
                              select @retcode = 1
                                      ,@secondary_id = NULL
                          end catch
                          
                          if (@retcode = 0)
                          begin
                              select @secondary_id = secondary_id
                                      ,@restore_threshold = restore_threshold
                                      ,@is_restore_alert_enabled = threshold_alert_enabled
                                      ,@last_copied_file = last_copied_file
                                      ,@last_copied_utc = last_copied_date_utc
                                      ,@last_restored_file = last_restored_file
                                      ,@last_restored_utc = last_restored_date_utc
                                      ,@last_restored_latency = last_restored_latency 
                              from #secondary_monitor
                              where upper(primary_server) = upper(@primary_server)
                                  and primary_database = @primary_database
                          end
                          else
                          begin
                              raiserror(32031, 10, 1, @secondary_server, @secondary_database, @monitor_server)
                          end
                      end
                  end -- remote monitor
                  --
                  -- do we have data on this secondary
                  --
                  if (@secondary_id is not null)
                  begin
                      --
                      -- yes we do - update the entry
                      --
                      select @time_since_last_copy = datediff(minute, @last_copied_utc, @curutcdate)
                              ,@time_since_last_restore = datediff(minute, @last_restored_utc, @curutcdate)
                      update #log_shipping_monitor
                      set
                          status = case when (@time_since_last_restore &gt; @restore_threshold or @last_restored_latency &gt; @restore_threshold) then 1 else 0 end
                          ,time_since_last_copy = @time_since_last_copy
                          ,last_copied_file = @last_copied_file
                          ,time_since_last_restore = @time_since_last_restore
                          ,last_restored_file = @last_restored_file
                          ,last_restored_latency = @last_restored_latency
                          ,restore_threshold = @restore_threshold
                          ,is_restore_alert_enabled = @is_restore_alert_enabled
                      where upper(server) = upper(@secondary_server)
                          and database_name = @secondary_database
                  end -- update secondary data
                  --
                  -- fetch next primary secondary
                  --
                  fetch #hcprimarysecondaries into @secondary_server, @secondary_database
              end
              close #hcprimarysecondaries
              deallocate #hcprimarysecondaries
          end -- we are on primary server processing primaries
          else
          begin
              --
              -- we are on monitor server
              -- get details of the secondaries from msdb.dbo.log_shipping_monitor_secondary
              -- if the same monitor is being used by secondaries
              --
              insert into #log_shipping_monitor (
                  status 
                  ,is_primary 
                  ,server  
                  ,database_name 
                  ,time_since_last_copy 
                  ,last_copied_file 
                  ,time_since_last_restore 
                  ,last_restored_file 
                  ,last_restored_latency 
                  ,restore_threshold 
                  ,is_restore_alert_enabled)
              select 
                  case when (datediff(minute, last_restored_date_utc, @curutcdate) &gt; restore_threshold
                                          or last_restored_latency &gt; restore_threshold) then 1 else 0 end
                  ,0
                  ,secondary_server
                  ,secondary_database
                  ,datediff(minute, last_copied_date_utc, @curutcdate)
                  ,last_copied_file
                  ,datediff(minute, last_restored_date_utc, @curutcdate)
                  ,last_restored_file
                  ,last_restored_latency
                  ,restore_threshold
                  ,threshold_alert_enabled
              from msdb.dbo.log_shipping_monitor_secondary (nolock)
              where primary_server = upper(@primary_server)
                  and primary_database = @primary_database
  
          end -- we are on monitor server processing primaries
          fetch #hcprimaries into @primary_id, @primary_server, @primary_database, @backup_threshold, @is_backup_alert_enabled
      end -- while cursor for hcprimaries
      close #hcprimaries
      deallocate #hcprimaries
      --
      -- Enumerate the secondary entries
      -- minus existing secondary entries in resultset
      --
      declare #hcsecondaries cursor local fast_forward for
          select 
              secondary_server
              ,secondary_database
              ,secondary_id uniqueidentifier
              ,primary_server
              ,primary_database
              ,restore_threshold
              ,threshold_alert_enabled
              ,last_copied_file
              ,last_copied_date_utc
              ,last_restored_file
              ,last_restored_date_utc
              ,last_restored_latency
          from msdb.dbo.log_shipping_monitor_secondary with (nolock) 
          where not exists (select * from #log_shipping_monitor
                                  where upper(server) = upper(secondary_server)
                                      and database_name = secondary_database
                                      and is_primary = 0)
          order by primary_server, primary_database
          
      open #hcsecondaries
      fetch #hcsecondaries into @secondary_server, @secondary_database, @secondary_id,
                  @primary_server, @primary_database, @restore_threshold, @is_restore_alert_enabled, 
                  @last_copied_file, @last_copied_utc, @last_restored_file, @last_restored_utc, @last_restored_latency
      while (@@fetch_status != -1)
      begin
          --
          -- Have we processed the primary for this secondary
          --
          if not (upper(@primary_server) = upper(@prev_primary_server)
              and @primary_database = @prev_primary_database)
          begin
              --
              -- No - Try to get the details of this primary
              --
              select @primary_id = null
              if (upper(@secondary_server) = upper(@@servername))
              begin
                  --
                  -- we are on secondary
                  -- get monitor server information
                  --
                  select @monitor_server = monitor_server
                          ,@monitor_server_security_mode = monitor_server_security_mode
                  from msdb.dbo.log_shipping_secondary with (nolock)
                  where secondary_id = @secondary_id
                  select @is_monitor_local = case when (upper(@monitor_server) = upper(@@servername)) then 1 else 0 end
                  if (@is_monitor_local = 1)
                  begin
                      --
                      -- local monitor
                      --
                      select @primary_id = primary_id
                          ,@backup_threshold = backup_threshold
                          ,@is_backup_alert_enabled = threshold_alert_enabled
                      from msdb.dbo.log_shipping_monitor_primary with (nolock)
                      where primary_server = upper(@primary_server)
                          and primary_database = @primary_database
                  end
                  else
                  begin
                      --
                      -- remote monitor
                      --
                      if (@monitor_server_security_mode = 0) and (suser_name() != SUSER_SNAME(0x01))
                      begin
                          --
                          -- execute as proxy
                          --
                          exec @retcode = sys.sp_MSproxylogshippingmonitorhelpprimary
                                  @monitor_server = @monitor_server
                                  ,@p1 = @primary_server
                                  ,@p2 = @primary_database
                                  ,@p3 = @primary_id output
                                  ,@p4 = @backup_threshold output
                                  ,@p5 = @is_backup_alert_enabled output
                      end
                      else
                      begin
                          delete #primary_monitor
                          select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.master.sys.sp_help_log_shipping_monitor_primary '
                                  ,@retcode = 0
                          begin try
                              insert into #primary_monitor
                              exec @retcode = @linkcmd 
                                          @primary_server = @primary_server
                                          ,@primary_database = @primary_database
                          end try
                          begin catch
                              select @retcode = 1
                                      ,@primary_id = NULL
                          end catch
  
                          if (@retcode = 0)
                          begin
                              select @primary_id = primary_id
                                      ,@backup_threshold = backup_threshold
                                      ,@is_backup_alert_enabled = threshold_alert_enabled
                              from #primary_monitor
                          end
                          else
                          begin
                              raiserror(32030, 10, 1, @primary_server, @primary_database, @monitor_server)
                          end
                      end -- processing remote
                  end
              end -- processing on secondary
              else
              begin
                  --
                  -- we are on monitor server
                  --
                  select @primary_id = primary_id
                      ,@backup_threshold = backup_threshold
                      ,@is_backup_alert_enabled = threshold_alert_enabled
                  from msdb.dbo.log_shipping_monitor_primary with (nolock)
                  where primary_server = upper(@primary_server)
                      and primary_database = @primary_database
              end -- processing on monitor server
              --
              -- insert primary details if available
              --
              if (@primary_id is not null)
              begin
                  select @prev_primary_server = @primary_server
                          ,@prev_primary_database = @primary_database
                  insert into #log_shipping_monitor (
                      status
                      ,is_primary
                      ,server
                      ,database_name
                      ,is_backup_alert_enabled)
                  values (
                  1
                      ,@primary_server
                      ,@primary_database
                      ,@backup_threshold
                      ,@is_backup_alert_enabled)
              end -- primary data available
          end -- process the primary
          --
          -- Insert the secondary
          --
          select @time_since_last_copy = datediff(minute, @last_copied_utc, @curutcdate)
                  ,@time_since_last_restore = datediff(minute, @last_restored_utc, @curutcdate)
          insert into #log_shipping_monitor (
              status 
              ,is_primary 
              ,server  
              ,database_name 
              ,time_since_last_copy 
              ,last_copied_file 
              ,time_since_last_restore 
              ,last_restored_file 
              ,last_restored_latency 
              ,restore_threshold 
              ,is_restore_alert_enabled)
          values (
              case when (@time_since_last_restore &gt; @restore_threshold or @last_restored_latency &gt; @restore_threshold) then 1 else 0 end
              ,0
              ,@secondary_server
              ,@secondary_database
              ,@time_since_last_copy
              ,@last_copied_file
              ,@time_since_last_restore
              ,@last_restored_file
              ,@last_restored_latency
              ,@restore_threshold
              ,@is_restore_alert_enabled)
          --
          -- get the next secondary
          --
          fetch #hcsecondaries into @secondary_server, @secondary_database, @secondary_id,
                      @primary_server, @primary_database, @restore_threshold, @is_restore_alert_enabled, 
                      @last_copied_file, @last_copied_utc, @last_restored_file, @last_restored_utc, @last_restored_latency
      end -- while cursor for hcsecondaries
      close #hcsecondaries
      deallocate #hcsecondaries
      --
      -- return resultset
      --
  SET @Body = RTRIM(@Body) + '&lt;table cellspacing="0" cellpadding="0" width="100%"&gt;'
  
      WHILE EXISTS (SELECT TOP 1 idx FROM #log_shipping_monitor)
      BEGIN
    SELECT TOP 1 @idx = idx, @Status = (CASE WHEN status  = 0 THEN 'Good' ELSE 'Bad' END),
     @DB = database_name, @LastCopy = time_since_last_copy, @LastCFile =  last_copied_file,
     @LastRestore = time_since_last_restore, @LastRFile = last_restored_file,
     @Latency = last_restored_latency, @Threshold = restore_threshold 
    FROM #log_shipping_monitor   order by ts
    
    SET @Body = RTRIM(@Body) 
    + '&lt;tr&gt;&lt;td nowrap style="font-size:11px;font-family:Verdana"&gt;'
     + '&lt;b&gt;Database&lt;/b&gt;:' + RTRIM(@DB) + '
  '
     + CASE WHEN @Status = 'Good' THEN
      '&lt;b&gt;Status&lt;/b&gt;:' + RTRIM(@Status) + '
  '
      ELSE '&lt;b&gt;Status&lt;/b&gt;:&lt;font size="+1" color="red"&gt;' + RTRIM(@Status) + '&lt;/font&gt;
  ' END   
     + '&lt;b&gt;Last Copied File&lt;/b&gt;:' + RTRIM(@LastCFile) + '
  '
     + '&lt;b&gt;Time since last Copy (min)&lt;/b&gt;:' + RTRIM(@LastCopy) + '
  '
     + '&lt;b&gt;Last Restored File&lt;/b&gt;:' + RTRIM(@LastRFile) + '
  '
     + '&lt;b&gt;Time since last Restore (min)&lt;/b&gt;:' + RTRIM(@LastRestore) + '
  '
     + '&lt;b&gt;Restore Latency (min)&lt;/b&gt;:' + RTRIM(@Latency) + '
  '
     + '&lt;b&gt;Restore Threshold (min)&lt;/b&gt;:' + RTRIM(@Threshold) + '
  '
     + '&lt;/td&gt;&lt;/tr&gt;'
     + '&lt;hr style="width: 100%; height: 1px;"&gt; ' 
    DELETE FROM #log_shipping_monitor WHERE idx = @idx
   END
  
  SET @Body = RTRIM(@Body) + '&lt;/table&gt;'
    EXECUTE msdb.dbo.sp_send_dbmail @recipients = @Recipients 
      ,@subject = @Subject
      ,@body = @Body
      ,@body_format = 'HTML'
  
  
  
  end
  
  DROP Table #log_shipping_monitor
  DROP TABLE #primary_monitor
  DROP TABLE #secondary_monitor</code></pre>
  </div>
  </div>
  </p>
  
  <p>I've run this script on my secondary database and it works OK.</p>
  
  <p>However, when I tried to run on another separated system which has a secondary database, I get this error:</p>
  
  <blockquote>
    <p>Msg 245, Level 16, State 1, Line 127<br>
    Conversion failed when converting the nvarchar value 'DBCLUSTER' to data type bit.</p>
  </blockquote>
  
  <p>Appreciate if you could help to fix the error.</p>
  
  <p>Thank you.</p>
  
  <ul>
  <li>Peter</li>
  </ul>