Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2015
    Posts
    3

    Question Unanswered: SQL Script Errors - Conversion Failed

    Hi Gurus,

    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:

    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 = '<p style="font-size:12px;font-family:Verdana"><b>' + @Server + ': </b></font><hr style="width: 100%; height: 1px;"> ' 
    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 > @restore_threshold or @last_restored_latency > @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
    --
    I've run this script on my secondary database and it works OK.

    However, when I tried to run in on a another separated system which has a seconday database, it's giving some errors as shown below:
    Msg 245, Level 16, State 1, Line 127 Conversion failed when converting the nvarchar value 'DBCLUSTER' to data type bit.

    Appreciate if you could help to fix the error.

    Thank you.


    - Peter

  2. #2
    Join Date
    Jan 2015
    Posts
    3

    Continues ...

    Code:
                    --
                    -- 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) > restore_threshold
                                            or last_restored_latency > 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
            --

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In management studio, if you double-click the error message, it often highlights the offending piece of code.
    Can you narrow it down for us?
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Using the query you posted as the problem area...

    Looking at the CREATE TABLE statement for #log_shipping_monitor:
    Code:
    is_primary bit not null default 0
    server sysname
    database_name sysname
    is_backup_alert_enabled bit
    And the variable declarations:
    Code:
    @primary_server sysname
    @primary_database sysname
    @backup_threshold int
    @is_backup_alert_enabled bit
    Spot the difference(s)?
    George
    Home | Blog

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •