SQL Script Errors - Conversion Failed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SQL Script Errors - Conversion Failed

Author

Topic

Peter2012
Starting Member

27 Posts

Posted - 2015-01-19 : 22:22:50

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:

------------------------------
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 = '' + @Server + ': <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
--
-- 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
--
-- 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 > @restore_threshold or @last_restored_latency > @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) + '<table cellspacing="0" cellpadding="0" width="100%">'

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)
+ '<tr><td nowrap style="font-size:11px;font-family:Verdana">'
+ 'Database:' + RTRIM(@DB) + '
'
+ CASE WHEN @Status = 'Good' THEN
'Status:' + RTRIM(@Status) + '
'
ELSE 'Status:' + RTRIM(@Status) + '
' END
+ 'Last Copied File:' + RTRIM(@LastCFile) + '
'
+ 'Time since last Copy (min):' + RTRIM(@LastCopy) + '
'
+ 'Last Restored File:' + RTRIM(@LastRFile) + '
'
+ 'Time since last Restore (min):' + RTRIM(@LastRestore) + '
'
+ 'Restore Latency (min):' + RTRIM(@Latency) + '
'
+ 'Restore Threshold (min):' + RTRIM(@Threshold) + '
'
+ '</td></tr>'
+ '<hr style="width: 100%; height: 1px;"> '
DELETE FROM #log_shipping_monitor WHERE idx = @idx
END

SET @Body = RTRIM(@Body) + '</table>'
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
------------------------------

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 127Conversion failed when converting the nvarchar value 'DBCLUSTER' to data type bit.

Appreciate if you could help to fix the error.

Thank you.

- Peter

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-20 : 07:47:41

It looks like your problem is as shown below in red. Your temp table has the "is_primary" column defined as a bit but you are attempting to set it to the "@primary_server" value which is defined as sysname.

--
-- 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)

Peter2012
Starting Member

27 Posts

Posted - 2015-01-20 : 09:16:58

Hi mandm,

Thank you for your reply and help.

Yes, I think you've spotted the problem.

The question is, how should it be fixed?

Appreciate for your help.

- Peter

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-20 : 09:28:42

Well there are a lot of ways to fix it but without analyzing the whole script probably the quickest and safest would be as follows.

1) Declare new variable @is_primary bit
2) Before the insert statement set the new variable

 
  SELECT @is_primary = CASE WHEN @primary_server IS NULL
                            THEN 0
                            ELSE 1
                       END

3) Replace the @primary_server in the values with the new @is_primary variable.

Peter2012
Starting Member

27 Posts

Posted - 2015-01-20 : 09:46:37

Hi mandm,

Thank you for your reply and help.

I've tried to follow you suggestion, but ended up with some errors.

I don't have much experiences in dealing with SQL queries.

Could you help to amend to codes that I've posted earlier?

Thanks so much.

- Peter

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-20 : 09:50:56

Sure. Give me a minute.

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-20 : 09:58:19

I have grabbed the two relevant sections and posted the changes in red. See if this does it.

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)
,@is_primary bit

--
-- we have a primary entry
--



  SELECT @is_primary = CASE WHEN @primary_server IS NULL
                            THEN 0
                            ELSE 1
                       END

insert into #log_shipping_monitor (
status
,is_primary
,server
,database_name
,is_backup_alert_enabled)
values (
1
,@is_primary
,@primary_database
,@backup_threshold
,@is_backup_alert_enabled)

Peter2012
Starting Member

27 Posts

Posted - 2015-01-20 : 21:24:27

Hi mandm,

Thank you for your reply and help.

Had tested and it's working OK. No errors anymore. Great work ... so happy :) Thanks so much.

However, when I checked my inbox, it's sending an empty email. It should have some information something like below.

Database:PET
Status:Good
Last Copied File:H:\BAK\PET_20150121020001.trn
Time since last Copy (min):12
Last Restored File:H:\BAK\PET_20150120230001.trn
Time since last Restore (min):12
Restore Latency (min):180
Restore Threshold (min):180

Appreciate for your help.

Thanks.

- Peter

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-20 : 22:33:21

I'm glad the fix took care of the error.

Is the email empty from both servers or just the one? I'm assuming the empty email is something new right?

Peter2012
Starting Member

27 Posts

Posted - 2015-01-21 : 01:10:21

Hi mandm,

Thank you for your reply and help.

I've checked again and confirmed the issue is only coming from the "2nd" secondary server, the one with the data type error mentioned earlier. Email is sent and received, but empty.

I've used the same script modified as per your update and it's working OK on the other secondary db (the "1st" secondary db). Email is sent and received, but it's not empty.

Could you help?

Thanks.

- Peter

Peter2012
Starting Member

27 Posts

Posted - 2015-01-21 : 05:16:48

Hi mandm,

Any idea on how to fix the issue?

Thanks for your help.

- Peter

Peter2012
Starting Member

27 Posts

Posted - 2015-01-21 : 05:38:21

Hi mandm,

For your info, here are the errors after execution.

Msg 110, Level 15, State 1, Line 142
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must

match the number of columns specified in the INSERT statement.

Thanks.

- Peter

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-21 : 06:05:16

I'm taking a look now.

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-21 : 06:08:20

Can you post the current script now that we've made changes? That will make the line references match up to what you're seeing.

Peter2012
Starting Member

27 Posts

Posted - 2015-01-21 : 09:06:38

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)
,@is_primary bit

SET @Recipients = 'abc123@gmail.com'
SET @Body = ''
SET @Server = @@SERVERNAME
SET @Subject = @Server + ' :: Daily Transaction Log Shipping Status'
SET @Body = '' + @Server + ': <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
--
SELECT @is_primary = CASE WHEN @primary_server IS NULL
THEN 0
ELSE 1
END

insert into #log_shipping_monitor (
status
,is_primary
,server
,database_name
,is_backup_alert_enabled)
values (
1
,@is_primary
,@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
--
-- 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
--
-- 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 > @restore_threshold or @last_restored_latency > @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) + '<table cellspacing="0" cellpadding="0" width="100%">'

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)
+ '<tr><td nowrap style="font-size:11px;font-family:Verdana">'
+ 'Database:' + RTRIM(@DB) + '
'
+ CASE WHEN @Status = 'Good' THEN
'Status:' + RTRIM(@Status) + '
'
ELSE 'Status:' + RTRIM(@Status) + '
' END
+ 'Last Copied File:' + RTRIM(@LastCFile) + '
'
+ 'Time since last Copy (min):' + RTRIM(@LastCopy) + '
'
+ 'Last Restored File:' + RTRIM(@LastRFile) + '
'
+ 'Time since last Restore (min):' + RTRIM(@LastRestore) + '
'
+ 'Restore Latency (min):' + RTRIM(@Latency) + '
'
+ 'Restore Threshold (min):' + RTRIM(@Threshold) + '
'
+ '</td></tr>'
+ '<hr style="width: 100%; height: 1px;"> '
DELETE FROM #log_shipping_monitor WHERE idx = @idx
END

SET @Body = RTRIM(@Body) + '</table>'
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

Peter2012
Starting Member

27 Posts

Posted - 2015-01-21 : 09:11:43

Hi mandm,

Thanks for your reply.

I've posted our entire script that I've tested, no more erros, but it does not produce any content when it's sent out.

Please ignore below error message which I've posted earlier.
Msg 110, Level 15, State 1, Line 142

I was experimenting with some codes and realized it's not related to our changes.

Hope you can help.

Thanks.

- Peter

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-21 : 09:49:09

Do a select out of the #log_shipping_monitor right before the email section (starting at or around line 518) as shown below in red. My guess is that one or more of the values used in the email body (@Body variable) are NULL in the temp table. Please post back the results of the SELECT.


SELECT * FROM #log_shipping_monitor

--
-- return resultset
--
SET @Body = RTRIM(@Body) + '<table cellspacing="0" cellpadding="0" width="100%">'

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) 
+ '<tr><td nowrap style="font-size:11px;font-family:Verdana">'
+ '<b>Database</b>:' + RTRIM(@DB) + '
'
+ CASE WHEN @Status = 'Good' THEN
'<b>Status</b>:' + RTRIM(@Status) + '
'
ELSE '<b>Status</b>:<font size="+1" color="red">' + RTRIM(@Status) + '</font>
' END 
+ '<b>Last Copied File</b>:' + RTRIM(@LastCFile) + '
'
+ '<b>Time since last Copy (min)</b>:' + RTRIM(@LastCopy) + '
'
+ '<b>Last Restored File</b>:' + RTRIM(@LastRFile) + '
'
+ '<b>Time since last Restore (min)</b>:' + RTRIM(@LastRestore) + '
'
+ '<b>Restore Latency (min)</b>:' + RTRIM(@Latency) + '
'
+ '<b>Restore Threshold (min)</b>:' + RTRIM(@Threshold) + '
'
+ '</td></tr>'
+ '<hr style="width: 100%; height: 1px;"> ' 
DELETE FROM #log_shipping_monitor WHERE idx = @idx
END

SET @Body = RTRIM(@Body) + '</table>'
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

Peter2012
Starting Member

27 Posts

Posted - 2015-01-21 : 20:42:36

Hi mandm,

Thanks for your reply.

I've tried below query, but it returned with some errors:
SELECT * FROM #log_shipping_monitor

Errors:
Msg 208, Level 16, State 0, Line 1
Invalid object name '#log_shipping_monitor'.

I've tried to comment out the last 3 lines of "DROP" commands, but it still does not work.

How to extract the result in a proper method?

Thanks.

- Peter

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-21 : 22:49:05

And you put the command before the email section like I posted? If it's in the the right spot and spelled right the next code section does the same command with a TOP 1 so it should be a valid object.

Peter2012
Starting Member

27 Posts

Posted - 2015-01-22 : 00:49:27

Hi mandm,

Thanks for your reply.

Yep, got it now. The output is quite long, thus I've typed them manually below and break them into sections.

idx status is_primary server database_name
1 0 0 PET-Database PET

is_backup_alert_enabled time_since_last_copy last_copied_file
NULL 6 D:\bak\PET_20150122053000.trn

time_since_last_restore last_restored_file last_restored_latency
6 D:\bak\PET_20150122023000.trn 180

last_restored_latency restore_threshold
180 180

is_restore_alert_enabled ts
1 0x00000000000007F8

I've arranged them nicely in notepad, but it behaves differently when pasted here and preview.

Anyway, hope they can be used.

- Peter

mandm
Posting Yak Master

120 Posts

Posted - 2015-01-22 : 04:29:24

Was this run on the server that sends the blank email? In looking at your results it looks like it wasn't because your is_primary = 0 but the code we added should have made it 1.



idx	status	is_primary	server	        database_name
1	0	0	        PET-Database	PET

If this was run on the server with the issue then we need to look at the @Body variable before the email is sent by selecting that as shown below in red.

SET @Body = RTRIM(@Body) + '</table>'

SELECT @Body

EXECUTE msdb.dbo.sp_send_dbmail @recipients = @Recipients
,@subject = @Subject
,@body = @Body
,@body_format = 'HTML'

The trick for maintaining formatting when you paste code or results is to add the keywords code before it and /code after it and wrap each of them in square brackets (i.e. [keyword] and [/keyword]).

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources