This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SQL Server User Experience Monitor (SUEM) - Custom SQL Query

Hello Thwack!

I'm trying to run a custom SQL query against a database and get back a set of information to be displayed.  I've been attempting to use the SQL SUEM, but I'm running into an issue.

When I run the query, I only seem to get one cell of information back instead of the information from the entire scripts.

Here is the expected outcome:

SQL_expected.png

After inputting the (adjusted) code into SUEM, here is the result I get:

SQL_test.PNG

As you can see, I'm only getting the first data cell instead of the information from the entire scripts.

I've read through a couple other posts about this and I've added a "SELECT 0" to my script to get it to even run, but I'm trying to figure out how to get the entire script to run/output properly.

Here is the adjusted and sanitized scripts below:

use msdb

SELECT 0, j.name JobName,h.step_name StepName,

     CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,

     STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,

     STUFF(STUFF(REPLACE(STR(run_duration, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS run_duration,

     case h.run_status when 0 then 'failed'

     when 1 then 'Succeded'

     when 2 then 'Retry'

     when 3 then 'Cancelled'

     when 4 then 'In Progress'

     else 'unknown'

     end as ExecutionStatus

     FROM [SERVERNAME].msdb.dbo.sysjobhistory h left join msdb.dbo.sysjobs j

     ON j.job_id = h.job_id

     where name in ('KNX Processor: F6P430_F6P_ALL_XR_BACKUP'

     ,'KNX Processor: F6P430_F6P_PREMRP_MD'

     ,'KNX Processor: F6P430_F6P_PREMRP_TD1_INIT'

     ,'KNX Processor: F6P430_F6P_PREMRP_TD2_INIT'

     ,'KNX Processor: F6P430_F6P_PREMRP_TD3_INIT'

     ,'KNX Processor: F6P430_F6P_2ND_RUN_NC'

     ,'KNX Processor: F6P430_F6_XR_OPT_BACKUP')

     and CAST(STR(h.run_date, 8, 0) AS DATETIME) + CAST(STUFF(STUFF(RIGHT('000000' + CAST (h.run_time AS VARCHAR(6)), 6), 5, 0, ':'), 3, 0, ':') AS DATETIME) > getdate()-10

     and h.step_name = '(Job outcome)'

     and h.run_status <> 4

Any help would be greatly appreciated!

  • ayup, the end user experience returns value for one cell only.

    one workaround is to end your query with FOR XML PATH()

    see THIS post where this example is given:

    DATE / MSG of Trap(s) in last 5 minutes:

    ------------------------------------------------------

    ${SQL:select CAST(t.DateTime as DateTime2(0)) as DateTime, ' --- '+tvb.OIDValue +CHAR(13)+CHAR(10) as Value FROM Nodes WITH(NOLOCK) JOIN Traps t WITH(NOLOCK) ON (Nodes.NodeID=t.NodeID and t.Tag like '%CriticalSW%' and t.DateTime between DATEADD(MI,-6,getdate()) and GETDATE()  ) JOIN TrapVarbinds tvb WITH(NOLOCK) ON (t.TrapID=tvb.TrapID) Order By t.DateTime FOR XML PATH('')}

    if you want to get fancy with formatting you can nest and use the () to add rows, etc...

    I use this in a few of my email alerts, though the "quote"ing gets tricky emoticons_mischief.png