Retrieving the report SQL queries from the database

Products

• IT Management Suite

Issue/Introduction

How can you retrieve the report or other SQL queries from the database?

Environment

ITMS 8.x

Resolution

The SQL queries from reports are stored in the State column in the Item table. These are the step to follow to retrieve the SQL queries

1.) Open Microsoft SQL Server Management Studio
2.) Change the results of a query then is run in Query Analyzer

a.)    Open menu Tools > Options > Query Results > SQL Server  and select the Results to Text option.

b.)    Change the value Maximum number of characters displayed in each column; from 256 (default) to say 8000.

c.)    Select the button OK

3.) Run the following SQL query. This will retrieve the queries from reports. This includes the all level queries (0, 1, 2 …) and parameter values/queries as well.

 

-- This SQL query will snap the report name and the queries section of the ntext data

select

[Report Name] = it.[name],

[Query String] = case
when (charindex('</item>', it.[state]) > 0 ) then
       substring(it.[state], charindex('<queries>', it.[state]),charindex('</queries>', it.[state]))
else substring(it.[state], charindex('<queries>', it.[state]), 4000)
end

from item it join vReport vr on it.[guid] = vr.[guid]

where it.[state] is not null

   and it.[Name] like '%'

   and vr.[Description] like '%'

order by 1

 

4.) Save the SQL query results to a csv file.

---

Notes:

• We've added conditions in the where clause to help you filter the results
• There is a limitation to using the functions substring and charindex.
  • The substring can truncate the ntext data 8190 characters.
  • And when using charindex on a string value you are looking for, but is not there, it will return the value 0. If this is the length value part of the substring, then you query will return ''.