Problem with using a SQL script / query in Dashboard Designer

[Anon Anon]

We experience some problems with using a MS SQL query shown below.

 

Running the query direct on the sql server or in the Service Level Manager gives the results I as expected.

 

 

The sql query looks like this:

 

use NimsoftSLM
Create Table #Temp1
(
var1 char(30),
var2 char(20),
var3 char(20),
var4 int,
var5 int,
var6 int,
var7 int,
var8 int,
var9 int
)
--
-- some variables to the declare
--
DECLARE @setpoint0 INT;
DECLARE @setpoint1 INT;
DECLARE @setpoint2 INT;
DECLARE @TAG INT;
SET @TAG = 3227
Set @setpoint0 = 7800;
Set @setpoint1 = @setpoint0 + 5000;
Set @setpoint2 = @setpoint1 + 10000;
--
--loop while
--
While @TAG <= 3249 
Begin
INSERT INTO #Temp1
--
SELECT Top 1 Substring(s_qos_data.target, 20,50) as Target,
CONVERT(VARCHAR(10), DATEADD(day, DATEDIFF(day, 0, rn_qos_data_0001.sampletime) , 0), 105)  AS Datum,
Datename(dw,DATEADD(day, DATEDIFF(day, 0, rn_qos_data_0001.sampletime) , 0)) as Dag,
Max(rn_qos_data_0001.samplevalue)-Min(rn_qos_data_0001.samplevalue) AS Urenperdag,
Max(rn_qos_data_0001.samplevalue) AS Totaal,
@setpoint0 AS offset,
@setpoint1 AS maint,
@setpoint2 AS repl,
rn_qos_data_0001.table_id AS id
--
FROM rn_qos_data_0001 JOIN s_qos_data ON (s_qos_data.table_id=rn_qos_data_0001.table_id)
WHERE (rn_qos_data_0001.table_id=@TAG and rn_qos_data_0001.samplevalue>0)
GROUP BY  DATEADD(day, DATEDIFF(day, 0, rn_qos_data_0001.sampletime), 0) ,  s_qos_data.target, rn_qos_data_0001.table_id
Order by s_qos_data.target, Datum desc

SET @TAG= @TAG+1

Continue

END
Select * from #Temp1
Drop Table #Temp1

 

 

Using the table widgets in both Dashboard Designer (old and new) gives this error:

 

In Old Dashboard Designer:

 

 

In New Dashboard Designer: (besides, number of columns appears to be limited to 5)

 

 

 

 

My Questions:

 

- What do I wrong?

- Is there an other way to produce this table and show it in UIM/Nimsoft?

 

Kind regards

Ashley Pietersen

[Anon Anon]

Update: I have learned now (and I am not a SQL specialist)

 

- using a temporary table is not allowed here

- using a While statement is therefore not applicable ( so far as I have tried)

- rewritten sql query for every item based on Union statement

- the new sql query is now also usable in sql-response probe

 

The new sql-query now looks like:

 

select * from

 

SELECT Top 1 Substring(s_qos_data.target, 20,50) as Target,

CONVERT(VARCHAR(10), DATEADD(day, DATEDIFF(day, 0, rn_qos_data_0001.sampletime) , 0), 105)  AS Datum,

CONVERT(Decimal(10,0),Max(rn_qos_data_0001.samplevalue)-Min(rn_qos_data_0001.samplevalue)) AS HrsToDay,

CONVERT(Int,Max(rn_qos_data_0001.samplevalue)) AS TotalRunHrs,

CONVERT(Decimal(10,0),(-Max(rn_qos_data_0001.samplevalue)+@setpoint1)/24) As DaysToM,

CONVERT(Decimal(10,0),(-Max(rn_qos_data_0001.samplevalue)+@setpoint2)/24) As DaysToR,

CONVERT(Decimal(10,0),(Max(rn_qos_data_0001.samplevalue)-@setpoint0)/24) as DaysAfterReset,

@Flag as order_id

 

FROM rn_qos_data_0001 JOIN s_qos_data ON (s_qos_data.table_id=rn_qos_data_0001.table_id)

WHERE (rn_qos_data_0001.table_id=@TAG and rn_qos_data_0001.samplevalue>0)

GROUP BY  DATEADD(day, DATEDIFF(day, 0, rn_qos_data_0001.sampletime), 0) ,  s_qos_data.target, rn_qos_data_0001.table_id

Order by DATEADD(day, DATEDIFF(day, 0, rn_qos_data_0001.sampletime), 0) desc

 

UNION

 

And so on .......

 

 

Problem solved!

 

Regards

 

Ashley Pietersen

[Legacy User]

I'm surprised this worked using the SLM. Typically you cannot use SQL extensions (PL\SQL or Transact-SQL) in the UMP or the sql_response probe. Glad to see that reverting to standard SQL resolved the issue for you.

[Anon Anon]

Hmmm, you may be right, using the new script in sql-response probe gives an error:

Profile table-yc-1, Execute SQL,COM error [0x800a0e78] Unknown error 0x800A0E78 - [ADODB.Recordset] Operation is not allowed when the object is closed.

Not sure what it means and how to solve it. Need to look in to it further. Probably need to open something first!?

 

But table widget in (new) Dashboard Designer is working fine.

 

 

regards

Ashley Pietersen

[Anon Anon]

Profile table-yc-1, Execute SQL,COM error [0x800a0e78] Unknown error 0x800A0E78 - [ADODB.Recordset] Operation is not allowed when the object is closed.

 

The above error seems to refers to an object that is closed, but what object should I open (and close) after running the script?

 

The beginning of the script looks like:

 

USE NimsoftSLM
DECLARE @setpoint0 INT;
DECLARE @setpoint1 INT;
DECLARE @setpoint2 INT;
DECLARE @TAG INT;
DECLARE @Flag INT;
SET @Flag = 1;
SET @TAG = 3227;
Set @setpoint0 = 7800;
Set @setpoint1 = @setpoint0 + 5000;
Set @setpoint2 = @setpoint1 + 15000;

select * from
(
SELECT Top 1 LEFT ((Substring(s_qos_data.target, 20, 30)), LEN(Substring(s_qos_data.target, 20, 30))-9) as Target,
CONVERT(VARCHAR(10), DATEADD(day, DATEDIFF(day, 0, rn_qos_data_0001.sampletime) , 0), 105)  AS Datum,
CONVERT(Decimal(10,0),Max(rn_qos_data_0001.samplevalue)-Min(rn_qos_data_0001.samplevalue)) AS HrsToDay,
CONVERT(Int,Max(rn_qos_data_0001.samplevalue)) AS TotalRunHrs,
CONVERT(Decimal(10,0),(-Max(rn_qos_data_0001.samplevalue)+@setpoint1)/24) As DaysToM,
CONVERT(Decimal(10,0),(-Max(rn_qos_data_0001.samplevalue)+@setpoint2)/24) As DaysToR,
CONVERT(Decimal(10,0),(Max(rn_qos_data_0001.samplevalue)-@setpoint0)/24) as DaysAfterReset,
CASE WHEN CONVERT(Decimal(10,0),(Max(rn_qos_data_0001.samplevalue)-@setpoint0)) < 0 THEN 0
  WHEN CONVERT(Decimal(10,0),(Max(rn_qos_data_0001.samplevalue)-@setpoint0)) > 0
  THEN CONVERT(Decimal(10,0),(Max(rn_qos_data_0001.samplevalue)-@setpoint0)) END as HrsAfterReset,
@Flag as order_id

UNION

...etc etc ....

 

 

 

Any suggestions?

 

Regards

Ashley Pietersen

[Garin Walsh]

Do you have the option of putting this code into a stored procedure? It's possible that the ADODB interface is breaking this into individual statements. There's also some indication that ADODB is preventing the creation of the temp table for performance reasons that I vaguely remember.

 

-Garin

[Anon Anon]

Hi Garin,

 

Not sure, but I will give it a try. Thanks for the suggestion.

 

regards

Ashley Pietersen.

[Anon Anon]

Hi Garin,

 

yes, using stored procedures did the trick. Thanks !

 

regards

Ashley

[alberto rodriguez]

Hello Ashley,

 

A bit of a late response but this is a very neat dashboard it seems. Are you using the "sql table" widget to display this information or is this a list view? I can't really tell and I am not Sql savvy besides basics so I'm wondering how you made these graphs through sql calls?

 

Any input or feedback is appreciated. Thanks!

 

A