How to add background color of row based to existing SP/query
-
Team - I have this SP to generate DB mirroring status report daily, but I like to add color to each row results.
Can someone help me add this to the below query.
ALTER PROCEDURE [dbo].[Mirroring_Status]
AS
--Display the status of the databases
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST((
SELECT
databasename AS 'td' ,'',
mirroring_role_desc AS 'td' ,'',
mirroring_state_desc AS 'td' ,'',
CASE mirroring_role_desc
WHEN 'MIRROR' THEN mirroring_partner_instance
WHEN 'PRINCIPAL' THEN principal
END AS 'td' ,'',
CASE mirroring_role_desc
WHEN 'MIRROR' THEN principal
WHEN 'PRINCIPAL' THEN mirroring_partner_instance
END 'td' ,'',
CASE mirroring_safety_level
WHEN 1 THEN 'HIGH PERFORMANCE'
WHEN 2 THEN 'HIGH SAFETY'
END 'td' ,''
FROM openquery([LinkedServer], 'select @@servername as ''principal'', db_name(database_id) as "databasename",* from master.sys.database_mirroring')
WHERE mirroring_state IS NOT NULL
Order by databasename
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX));
SET @body ='<html><body>
Database Mirroring Status Report
<table border = 1>
<tr>
<tr bgcolor="gray">
<th>Database</th>
<th>Role</th>
<th>State</th>
<th>Principal_Instance </th>
<th>DR_Instance </th>
<th>Operating Mode </th>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail profile', -- enter Database Mail profile name
@recipients = 'Email.com', -- list of Email recipients
@subject = 'Database Mirroring Status Report',
@body = @body,
@body_format ='HTML';
Regards,
SQLisAwe5oMe. -
Hi,
it is very diffucult to read the t-sql. Try to put your code in the right form
select @@version
It is very easy.
-
Deleted
-
When I do things like this, I return separate columns for the properties I wish to control (font, bold, colour etc) and then assign those columns to the relevant Tablix cells in SSRS.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
select @@version - Microsoft SQL Server 2019 (RTM-GDR) (KB4517790) - 15.0.2070.41 (X64) on Windows Server 2019 Datacenter 10.0 <X64> (Build 17763: )
Also, attached the script.
Thanks in advance.
Regards,
SQLisAwe5oMe. -
Attached the script in .docx type.
Regards,
SQLisAwe5oMe. -
Phil Parkin wrote:
When I do things like this, I return separate columns for the properties I wish to control (font, bold, colour etc) and then assign those columns to the relevant Tablix cells in SSRS.
I'm thinking he might not be using SSRS for this one.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Jeff Moden wrote:Phil Parkin wrote:
When I do things like this, I return separate columns for the properties I wish to control (font, bold, colour etc) and then assign those columns to the relevant Tablix cells in SSRS.
I'm thinking he might not be using SSRS for this one.
I'm thinking you're dead right! For some reason, my mind immediately jumped to that conclusion, without evidence. Notwithstanding that, this problem looks like one which could be solved easily in SSRS!
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
SQLisAwe5oMe wrote:
Attached the script in .docx type.
You've been around for a long time and I think I've shown you this before but one more time.
When you're filling out a post and you're ready to add code to the post, put the insertion point where you want the code to go and then click on the "code button" at the top of the post-entry window (highlighted in Rec in the following graphic). That will cause a code window to pop up. Past your code in that and then click "OK'.
As a bit of a sidebar, please make sure that your code is NOT double spaced. Also, comment here and there to identify the parts of the code would also save us some time as it would you or someone else in the future when they're trying do modify your code.
SQLisAwe5oMe wrote:Team - I have this SP to generate DB mirroring status report daily, but I like to add color to each row results.
Can someone help me add this to the below query.
Yes, but instead of us going back and forth, this is one place where a graphic would actually help. Please show us an example of what you want the report to look like even if you have to truss one up in Excel or whatever.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
To answer the question that I think you're asking, do something like this just before you combine @Body and @XML.
SET @xml = REPLACE(@XML,'<tr>','<tr style="background-color:Red; color:White; ">')
If you intend to change colors for each row depending on the content of the row, (I haven't tried it) you could just conditionally add the "style" notation to the beginning of the database name.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Thanks Jeff for the reply.
--Working SP to send email for Database mirroring report using LinkedServer OPENQUERY
USE [master]
GO
/****** Object: StoredProcedure [dbo].[Mirroring_Status] Script Date: 4/13/2022 12:01:25 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Mirroring_Status]
AS
--Display the status of the databases
DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
SET @xml = CAST((
SELECT
databasename AS 'td' ,'',
mirroring_role_desc AS 'td' ,'',
mirroring_state_desc AS 'td' ,'',
CASE mirroring_role_desc
WHEN 'MIRROR' THEN mirroring_partner_instance
WHEN 'PRINCIPAL' THEN principal
END AS 'td' ,'',
CASE mirroring_role_desc
WHEN 'MIRROR' THEN principal
WHEN 'PRINCIPAL' THEN mirroring_partner_instance
END 'td' ,'',
CASE mirroring_safety_level
WHEN 1 THEN 'HIGH PERFORMANCE'
WHEN 2 THEN 'HIGH SAFETY'
END 'td' ,''
FROM openquery([LinkedServer], 'select @@servername as ''principal'', db_name(database_id) as "databasename",* from master.sys.database_mirroring')
WHERE mirroring_state IS NOT NULL
Order by databasename
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX));
SET @body ='<html><body>Database Mirroring Status Report For WEB SQL
<table border = 1>
<tr>
<tr bgcolor="gray">
<th>Database</th>
<th>Role</th>
<th>State</th>
<th>Principal_Instance </th>
<th>DR_Instance </th>
<th>Operating Mode </th>
</tr>'
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail profile', -- enter Database Mail profile name
@recipients = 'Email.com', -- list of Email recipients
@subject = 'Database Mirroring Status Report',
@body = @body,
@body_format ='HTML';Regards,
SQLisAwe5oMe. -
I'll try to work on this tonight after work. Thanks for the example.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Hi Jeff - just checking to see if you got a chance to test it.
Regards,
SQLisAwe5oMe. -
Ah, my apologies. No. Have not gotten to this yet and I'm not sure about tonight because I have a presentation to give tonight. Thank you for the reminder and I'll try to get to it after the presentation that occurs right after work today.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
SQLisAwe5oMe wrote:
Hi Jeff - just checking to see if you got a chance to test it.
Ok. I don't actually have any mirrored databases anywhere but I tested this the best I could. I'll let you put the stored procedure wrapper around it when you're ready. I also formatted the code as if I were going to put it into production on one of my boxes. Most of the code is your original code with a bit of formatting.
I also added a nice, informative caption above the table with the server instance name and date/time the report was created.
--===== Local obviously named Variables
DECLARE @xml NVARCHAR(MAX)
,@body NVARCHAR(MAX)
;
--===== Prep the status of the databases for tabular display of the Database Mirror Status
SELECT @xml = REPLACE(REPLACE(REPLACE(REPLACE(
CAST(
(
SELECT td = CONCAT('~bgcolor="'
,CASE mirroring_state_desc
WHEN 'SYNCHRONIZED' THEN 'Green"'
WHEN 'PRINCIPAL' THEN 'Yellow"'
WHEN 'DISCONNECTED' THEN 'Red"'
ELSE 'White"'
END,'~',databasename) ,''
,td = mirroring_role_desc ,''
,td = mirroring_state_desc ,''
,td = CASE mirroring_role_desc
WHEN 'MIRROR' THEN mirroring_partner_instance
WHEN 'PRINCIPAL' THEN principal
END, ''
,td = CASE mirroring_role_desc
WHEN 'MIRROR' THEN principal
WHEN 'PRINCIPAL' THEN mirroring_partner_instance
END, ''
,td = CASE mirroring_safety_level
WHEN 1 THEN 'HIGH PERFORMANCE'
WHEN 2 THEN 'HIGH SAFETY'
END, ''
FROM OPENQUERY([LoopBack], 'select principal = @@servername, databasename =db_name(database_id), * from master.sys.database_mirroring')
WHERE mirroring_state IS NOT NULL
ORDER BY databasename
FOR XML PATH('tr'),TYPE
) --End of the XML
AS NVARCHAR(MAX)) --End of the CAST
--===== The other end of the REPLACEs
,'<td>~','<tr ')
,'"~','"><td>')
,'</tr>','</tr>'+CHAR(13)+CHAR(10))
,'<tr>',SPACE(8)+'<tr>')
;
--===== Create the caption with the server name and datetime.
SELECT @body = '<html>
<body>
<table border="1" cellpadding = "4" cellspacing="0" style="font-family:arial; color:black; font-size:10pt;">
<caption style=" border:1; background-color:White; font-weight:bold; font-size:14pt;">
Database Mirroring Status Report for ' + @@SERVERNAME + '<br>
~ ' + CONVERT(CHAR(17),GETDATE(),113) + ' ~
</caption>
<tr>
<tr bgcolor="gray">
<th>Database</th>
<th>Role</th>
<th>State</th>
<th>Principal_Instance </th>
<th>DR_Instance </th>
<th>Operating Mode </th>
</tr>
'
;
--===== Assemble the entire report and create the end for the HTML
SELECT @body = @body + @xml + ' </table>
</body>
</html>'
;
--===== Sent the HTML report via email.
-- Make sure you have a correct profile or default profile and that you're
-- sending to the correct email address before you drop the hammer on this code.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Mail profile'
,@recipients = 'Email.com'
,@subject = 'Database Mirroring Status Report'
,@body = @body
,@body_format = 'HTML'
;--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply