How to snag the report SQL queries from the database

Products

• Management Platform (Formerly known as Notification Server)

Issue/Introduction

 

Resolution

Question
How can you snag the report or other SQL queries from the database

Answer

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

1.) Open Query Analyzer
2.) Change the results of a query then is run in Query Analyzer

a.)    Open menu Tools > Options and select the Results tab.

b.)    Change the value Maximum characters per column; from 256 (default) to say 8000.

c.)    Select the button OK

3.) Run the following SQL query. This will snap 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.

 

---

This is a query you can use for SQL server 2005...

select i.[guid], i.[name], convert(xml,i.[state]), cl.[Type]
from Item i
join ItemClass ic on ic.[Guid] = i.[Guid]
join Class cl on cl.[Guid] = ic.[ClassGuid]
where 1 = 1
  and i.[name] like '%%'
  and i.[Attributes] = 0
  and [state] is not null
  and i.[name] not in ('',null)
order by  i.[name], cl.[Type]

The link in the query will open a new tab with the state column in XML.

The queries with (<,>,@...) will be translated to a safe xml data, so you will need to translate it back if you wish to use it.

---

 

Notes:

·        I've added conditions in the where clause to help you filter the results

·        There is a limitation to using the functions substring and charindex.

o        The substring can truncate the ntext data 8190 characters.

o       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 ''.