November 28, 2011 at 4:36 am
Hi,
My SQL query returns data with HTML Code as single row
I used Execute SQL task & Objects variable to get the SQL resultset.(When i used String variable it thrown errors)
I am going to use this Object in SendMailTask expression for Subject.
Thanks,
sasidhar p
November 28, 2011 at 4:40 am
Pulivarthi Sasidhar (11/28/2011)
Hi,My SQL query returns data with HTML Code as single row
I used Execute SQL task & Objects variable to get the SQL resultset.(When i used String variable it thrown errors)
I am going to use this Object in SendMailTask expression for Subject.
Thanks,
sasidhar p
...And your question is?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 28, 2011 at 7:23 am
Pulivarthi Sasidhar (11/28/2011)
Hi,My SQL query returns data with HTML Code as single row
I used Execute SQL task & Objects variable to get the SQL resultset.(When i used String variable it thrown errors)
I am going to use this Object in SendMailTask expression for Subject.
Thanks,
sasidhar p
I don't think you can use the Object variable directly in an expression. You'll need to parse it using a script task/component.
What error did you get when using a string variable?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 29, 2011 at 12:28 am
Hi,
my SQL statements are following
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Declare
@Recipients varchar(max) ,
@ProfileName sysname,
@Subject nvarchar(255),
@Body nvarchar(max),
@Body1 nvarchar(max),
@Body2 nvarchar(max),
@Body3 nvarchar(max),
@Body4 nvarchar(max),
@BodySF nvarchar(max),
@BodySFRows nvarchar(max),
@BodySFRowsPC nvarchar(max),
@BodyFormat varchar(20),
@Importance varchar(6),
@Sensitivity varchar(12),
@DeletedTotal nvarchar(20)
set @Recipients = 'drparakala@kuhjhgj.org;dparakal@abcvdfty.com;'
Set @BodyFormat = 'HTML'
Set @Importance = 'NORMAL'
Set @Sensitivity = 'NORMAL'
DECLARE @strHTML VARCHAR(MAX)
DECLARE @strHTML1 VARCHAR(MAX)
DECLARE @strHTML2 VARCHAR(MAX)
DECLARE @strHTML3 VARCHAR(MAX)
DECLARE @i INT
DECLARE @rc INT
DECLARE @dbname VARCHAR(400)
declare @MailHTML nvarchar(max)
set @ProfileName='DBATEAM'
--------------------------------
-------Total Jobs---------------
--------------------------------
Select COUNT(0) as [Total Jobs] INTO #TTLJBS from msdb..sysjobs
SET @rc = @@rowcount
set @Body1= isnull(@strHTML1,'') + '<TR BGCOLOR="EEEEEE">
<TD ALIGN="center" WIDTH="20%"><B>Total Jobs</B> </TD></TR>'
-- Query the data only if there are rows:
IF @rc = 0
BEGIN
set @Body1= '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="left"><B><A NAME="_TOTALJOBS">TOTAL JOBS IN THE SERVER</A></B> </TD></TR>'
+ '<TR BGCOLOR="EEEEEE"><TD ALIGN="center" WIDTH="70%"><B>There are no jobs on this server</B> </TD></TR>'
END
ELSE
BEGIN
SELECT @strHTML1 = '<TR><TD ALIGN="center">' + CONVERT(VARCHAR(100),[Total Jobs]) + ' </TD>' + '</TR>'
FROM #TTLJBS
set @Body1= @Body1+@strHTML1
DROP Table #TTLJBS
-----------------------------------------
----No of Succeeded and Failured Jobs----
-----------------------------------------
Select ROW_NUMBER () OVER (ORDER BY SERVERPROPERTY('Servername')) AS RowNumber,
[last_run_status] as 'JOB STATUS' ,
Count([last_run_status]) Total into #NOJBS from
(
SELECT Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,
j.name AS job_name,
CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,
CASE jh.run_status WHEN 0 THEN 'FAILED'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress' ELSE
'Status Unknown' END AS 'last_run_status',
ja.run_requested_date as last_run_date,
CONVERT(VARCHAR(500),jh.message) AS step_description
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id
WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity) --ORDER BY job_name,job_status
) A group by a.[last_run_status]
SET @rc = @@rowcount
set @Body2= isnull(@strHTML2,'') + '<TR BGCOLOR="EEEEEE">
<TD ALIGN="center" WIDTH="70%"><B>JOB STATUS</B> </TD>
<TD ALIGN="Center" WIDTH="30%"><B>Total</B> </TD></TR>'
-- Query the data only if there are rows:
IF @rc = 0
BEGIN
set @Body2= '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="2" ALIGN="LEFT"><B><A NAME="_NoofSucceededandFailuredJobs">No of Succeeded and Failured Jobs</A></B> </TD></TR>'
+ '<TR BGCOLOR="EEEEEE"><TD ALIGN="LEFT" WIDTH="70%"><B>There are no jobs on this server</B> </TD></TR>'
END
ELSE
BEGIN
SET @i = 1
WHILE @i <= @rc
begin
SELECT @strHTML2 = ISNULL(@strHTML2,'')+
'<TR><TD ALIGN="center" WIDTH="70%">' + CONVERT(VARCHAR(100),[JOB STATUS]) + ' </TD>' +
'<TD ALIGN="center" WIDTH="30%">' + CONVERT(VARCHAR(100),Total) + ' </TD>' +
'</TR>'
FROM #NOJBS
WHERE RowNumber = @i
SET @i = @i + 1
end
set @Body2= @Body2+@strHTML2
DROP Table #NOJBS
--------------------------------------------------------------------------
-- Tabular History of jobs with servername,Status,Last rundate and status
--------------------------------------------------------------------------
SELECT ROW_NUMBER () OVER (ORDER BY SERVERPROPERTY('Servername')) AS RowNumber,
Convert(varchar(20),SERVERPROPERTY('ServerName')) AS ServerName,--j.job_id jobid,
j.name AS job_name,
CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END AS job_status,
CASE jh.run_status WHEN 0 THEN 'FAILED'
WHEN 1 THEN 'Succeeded'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
WHEN 4 THEN 'In Progress' ELSE
'Status Unknown' END AS 'last_run_status',
ja.run_requested_date as last_run_date,
CASE when last_success_date is not null then CONVERT( varchar(12),last_success_date )
else 'Job never run successfully' end as last_success_date,
--CONVERT(VARCHAR(max),jh.message) AS step_description ,
--CONVERT(DATETIME,RTRIM(jh.next_run_date)) +(jh.next_run_time * 9 + jh.next_run_time % 10000 * 6 + jh.next_run_time % 100 * 10) / 216e4 AS next_scheduled_run_date,
CASE WHEN jh.message IS NULL THEN 'NULL'
WHEN jh.message like 'The job failed.%' THEN isnull(emsg.message,jh.message)
ELSE jh.message END as ErrorMessage
INTO #Maintemp
FROM
(msdb.dbo.sysjobactivity ja LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)
join msdb.dbo.sysjobs_view j on ja.job_id=j.job_id
left Join (
Select Max(Lastsuccessdate) last_success_date,job_id from
(
select CAST( substring (cast(run_date as varchar),1,4)
+'-'+
substring (cast(run_date as varchar),5,2)
+'-'+
substring (cast(run_date as varchar),7,2)
AS DATETIME)
Lastsuccessdate,sj.job_id
from msdb.dbo.sysjobhistory jh join msdb..sysjobs sj on sj.job_id=jh.job_id
Where run_status=1 and step_id=(Select MAX(step_id) from msdb.dbo.sysjobhistory where job_id=jh.job_id group by job_id)
) a group by a.job_id) ls on ls.job_id=j.job_id
left Join msdb.dbo.sysjobhistory emsg on emsg.job_id=j.job_id and jh.instance_id-1=emsg.instance_id
WHERE ja.session_id=(SELECT MAX(session_id) from msdb.dbo.sysjobactivity)
ORDER BY job_name,job_status
SET @rc = @@rowcount
set @Body3= isnull(@strHTML3,'') + '<TR BGCOLOR="EEEEEE">
<TD ALIGN="center" WIDTH="10%"><B>Servername</B> </TD>
<TD ALIGN="center" WIDTH="10%"><B>Job_name</B> </TD>
<TD ALIGN="center" WIDTH="10%"><B>Job_status</B> </TD>
<TD ALIGN="center" WIDTH="10%"><B>Last_run_status</B> </TD>
<TD ALIGN="center" WIDTH="10%"><B>Last_run_date</B> </TD>
<TD ALIGN="center" WIDTH="10%"><B>Last_success_date</B> </TD>
<TD ALIGN="center" WIDTH="50%"><B>ErrorMessage</B> </TD></TR>'
-- Query the data only if there are rows:
IF @rc = 0
BEGIN
set @Body3= '<TR BGCOLOR="EEEEEE"><TD CLASS="Title" COLSPAN="1" ALIGN="center"><B><A NAME="_JobStatus">Jobs Status</A></B> </TD></TR>'
+ '<TR BGCOLOR="EEEEEE"><TD ALIGN="center" WIDTH="70%"><B>There are no jobs on this server</B> </TD></TR>'
END
ELSE
BEGIN
SET @i = 1
WHILE @i <= @rc
begin
SELECT @strHTML3 = ISNULL(@strHTML3,'')+
'<TR><TD ALIGN="center" WIDTH="10%">' + CONVERT(VARCHAR(100),[Servername]) + ' </TD>' +
'<TD ALIGN="center" WIDTH="10%">' + CONVERT(VARCHAR(100),isnull(Job_name,'NULL')) + ' </TD>' +
'<TD ALIGN="center" WIDTH="10%">' + CONVERT(VARCHAR(100),isnull(Job_status,'NULL')) + ' </TD>' +
'<TD ALIGN="center" WIDTH="10%">' + CONVERT(VARCHAR(100),isnull(Last_run_status,'NULL')) + ' </TD>' +
'<TD ALIGN="center" WIDTH="10%">' + CONVERT(VARCHAR(100),ISNULL(Last_run_date,'01-01-1900')) + ' </TD>' +
'<TD ALIGN="center" WIDTH="10%">' + CONVERT(VARCHAR(100),isnull(Last_success_date,'NULL')) + ' </TD>' +
'<TD ALIGN="center" WIDTH="50%">' + CONVERT(VARCHAR(1000),isnull(ErrorMessage,'NULL')) + ' </TD>' +
'</TR>'
FROM #Maintemp
WHERE RowNumber = @i
SET @i = @i + 1
end
set @Body3= @Body3+@strHTML3
Drop table #maintemp
end
----------------------------------------------
--------- ***** Start of te Mail body --------
----------------------------------------------
Set @Subject = 'SQL Agent Job Failures - SASI'
Set @Body1 = '<p> Hi Team, </p>'+ '<p>Please find the below SQL agent job failures.</p>' +'<p> <CENTER><FONT SIZE="5"><B> Server ' + @@servername + '</B></FONT></CENTER> </p>' + '<p> <B> Total No. of Jobs in the Instance </B> </p>' + '<p><table cellpadding="2" cellspacing="0">' + IsNull(@Body1, '') +'</table></p>'
Set @Body2 ='<p><B> No of Succeeded and Failured Jobs </B> </p>' + '<p><table cellpadding="4" cellspacing="0">' + IsNull(@Body2, '') +'</table></p>'
Set @Body3 ='<p><B> SQL Agent Jobs Status </B> </p>' + '<p><table cellpadding="2" cellspacing="0" ALIGN="LEFT >' + IsNull(@Body3, '') +'</table></p>'
Set @Body4 ='<p> <table cellpadding="2" cellspacing="0" ALIGN="LEFT >' + IsNull(@Body4, '') +'</table></p>'
set @Body3=@Body1+@Body2+@Body3
Set @Body = '<html><head><style type="text/css">caption {background: white;font:bold 10pt Calibri, Arial;}
td {border:solid #cccccc 1px;font:9pt Calibri, Arial;}
th {border:solid #cccccc 1px;font:bold 10pt Calibri, Arial;text-align:left;} tr.even {background:#eeeeea;} tr.head {background:#eeeedd;}</style></head><body>' + @Body3 +'<p>Thank you,
SQL - DBA Team</body></html>'
select @Body as bd
END
END
when i am going to take this in string variable i am getting following error:
An error ocured while assigning a value to the variable "StrBdy1":"The type of value being assigned to variable "User::StrBdy1" differs from the current variable type. variable may not change type during execution.
when i changed variable from string to Object type it executed successfully....
Now this string needs to be the Body of sendmail task
Thanks,
sasidhar P
November 29, 2011 at 12:49 am
Try adding SET NOCOUNT ON at the beginning, as you have multiple SELECT statements in your query, albeit that only one returns data.
If you execute this in SSMS, what do you get?
Think about creating a stored procedure/function for this, maybe that will give less issues.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 29, 2011 at 3:31 am
I will get the following result as single row
<html><head><style type="text/css">caption {background: white;font:bold 10pt Calibri, Arial;} td {border:solid #cccccc 1px;font:9pt Calibri, Arial;} th {border:solid #cccccc 1px;font:bold 10pt Calibri, Arial;text-align:left;} tr.even {background:#eeeeea;} tr.head {background:#eeeedd;}</style></head><body><p> Hi Team, </p><p>Please find the below SQL agent job failures.</p><p> <CENTER><FONT SIZE="5"><B> Server NV000011\MSSQL2008</B></FONT></CENTER> </p><p> <B> Total No. of Jobs in the Instance </B> </p><p><table cellpadding="2" cellspacing="0"><TR BGCOLOR="EEEEEE"> <TD ALIGN="center" WIDTH="20%"><B>Total Jobs</B> </TD></TR><TR><TD ALIGN="center">2 </TD></TR></table></p><p><B> No of Succeeded and Failured Jobs </B> </p><p><table cellpadding="4" cellspacing="0"><TR BGCOLOR="EEEEEE"> <TD ALIGN="center" WIDTH="70%"><B>JOB STATUS</B> </TD> <TD ALIGN="Center" WIDTH="30%"><B>Total</B> </TD></TR><TR><TD ALIGN="center" WIDTH="70%">FAILED </TD><TD ALIGN="center" WIDTH="30%">2 </TD></TR></table></p><p><B> SQL Agent Jobs Status </B> </p><p><table cellpadding="2" cellspacing="0" ALIGN="LEFT ><TR BGCOLOR="EEEEEE"> <TD ALIGN="center" WIDTH="10%"><B>Servername</B> </TD> <TD ALIGN="center" WIDTH="10%"><B>Job_name</B> </TD> <TD ALIGN="center" WIDTH="10%"><B>Job_status</B> </TD> <TD ALIGN="center" WIDTH="10%"><B>Last_run_status</B> </TD> <TD ALIGN="center" WIDTH="10%"><B>Last_run_date</B> </TD> <TD ALIGN="center" WIDTH="10%"><B>Last_success_date</B> </TD> <TD ALIGN="center" WIDTH="50%"><B>ErrorMessage</B> </TD></TR><TR><TD ALIGN="center" WIDTH="10%">NH328041\MSSQL2008 </TD><TD ALIGN="center" WIDTH="10%">syspolicy_purge_history </TD><TD ALIGN="center" WIDTH="10%">Enabled </TD><TD ALIGN="center" WIDTH="10%">FAILED </TD><TD ALIGN="center" WIDTH="10%">Nov 29 2011 2:00AM </TD><TD ALIGN="center" WIDTH="10%">Job never run successfully </TD><TD ALIGN="center" WIDTH="50%">Executed as user: NH328041\SYSTEM. A job step received an error at line 1 in a PowerShell script. The corresponding line is '(Get-Item SQLSERVER:\SQLPolicy\NV000011\MSSQL2008).EraseSystemHealthPhantomRecords()'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Cannot retrieve the dynamic parameters for the cmdlet. SQL Server PowerShell provider error: Could not connect to 'NV000011\MSSQL2008'. [Failed to connect to server NV000011\MSSQL2008. --> A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] '. Process Exit Code -1. The step failed. </TD></TR><TR><TD ALIGN="center" WIDTH="10%">NH328041\MSSQL2008 </TD><TD ALIGN="center" WIDTH="10%">S_tst </TD><TD ALIGN="center" WIDTH="10%">Enabled </TD><TD ALIGN="center" WIDTH="10%">FAILED </TD><TD ALIGN="center" WIDTH="10%">Nov 21 2011 6:38AM </TD><TD ALIGN="center" WIDTH="10%">Nov 21 2011 </TD><TD ALIGN="center" WIDTH="50%">Executed as user: NT AUTHORITY\SYSTEM. Unclosed quotation mark after the character string 'as '. [SQLSTATE 42000] (Error 105) Incorrect syntax near 'as '. [SQLSTATE 42000] (Error 102). The step failed. </TD></TR></table></p><p>Thank you,
NISH - DBA Team</body></html>
But i was unable to get this into SSIS string variable.
November 29, 2011 at 3:44 am
SSIS could be giving troubles because of the length of the text. (there is a 4000 character limit, but I thought this was only in expressions)
I would do this in SQL Server itself. Put your query in a stored procedure and use the result in dbo.sp_send_dbmail.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 29, 2011 at 6:40 am
I did it actually.....
But i have to run the same query on 5 servers and send mails. Instead of 5 mails i need to send 1 mail.
A single mail that contains SQL job(s) history of 5 servers. So i used SSIS with 5 execute SQL tasks and finally concatenating all resultsets to send a mail....
Even i tried with ScriptTask to convert Object to String it also thrown error with System_ComObject...etc..
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply