September 7, 2016 at 10:22 am
Use ReportServer
GO
WITH XMLNAMESPACES( DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' )
Take a look at the attached output fronm the SQL stmt below.
What I want is to list only those entries where the SQL2 field
1.) Starts with the word "Select"
2.) Includes the word "PlanCode" before the first "FROM"
( I have higlited some entries in the output file that should appear in the output )
Thanks ahead
Select a.NAME, a.SQL2
FROM
(
SELECT
name
---, LEFT( Path, LEN(Path) - CharIndex('/', REVERSE(Path) ) ) + '/' as ReportPath
--, cast(cast([Content] as varbinary(max)) as xml).value('(//CommandText)[1]','VARCHAR(2500)') as SQL1
, cast(cast([Content] as varbinary(max)) as xml).value('(//CommandText)[2]','VARCHAR(2500)') as SQL2
--, cast(cast([Content] as varbinary(max)) as xml).value('(//CommandText)[3]','VARCHAR(2500)') as SQL3
FROM [ReportServer].[dbo].[Catalog]
WHERE
type = 2
)a
WHERE
a.SQL2 like 'Select%Plan%'
September 7, 2016 at 10:51 am
You can try replacing
"WHERE
a.SQL2 like 'Select%Plan%' "
with
"WHERE
a.SQL2 like 'Select%' AND a.SQL2 like '%Plan%'"
September 7, 2016 at 10:58 am
Sorry DWalker, That made no difference.
Almost every entry starts with the word "Select..." What I need is the field word PlanCode to appear before the very first entry of the word "FROM"
Here are 2 valid examples
Select DISTINCT PlanCode from DPlanMaster
Select PLanCode from DPlanMaster
September 7, 2016 at 11:11 am
"WHERE
a.SQL2 like 'Select%' AND a.SQL2 like '%Plan FROM%'" *might* work if you can guarantee there is a constant number of spaces between "Plan" and "From".
Another possibility if you come up with several queries that get just some of the data is to use UNION to combine the queries.
September 7, 2016 at 11:23 am
WHERE a.SQL2 like 'Select%PlanCode%From%'
AND CHARINDEX ('FROM', a.SQL2) > CHARINDEX ('PlanCode', a.SQL2)
September 7, 2016 at 11:27 am
Gazareth Solution Works!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply