Raster Catalog: requirement

Hi,
We have a requirement to read the .trc files generated by SQL server
in real-time or atleast near real-time. We do not want to interfere
with existing SQL server setup by running functions like
fn_trace_get_table. The requirement is to get the events logged in .trc
files by directly reading them. SQL Profiler can read the trace files,
but looks like the .trc file format is not published by Microsoft. But
I also see some tools like Idera's SQL Compliance Manager are capable
of directly reading the trace files. Can any one suggest an approach to
read trace file without having to disturb the existing SQL server
setup.
Thanks
ad kompellaSQLBenchmarkPro is a tool I designed to organise trace scheduling,
collection & analysis accross multiple server concurrently - for both SQL
2000 and SQL 2005. www.gajsoftware.com
Regards,
Greg Linwood
SQL Server MVP
"adkompella" <adkompella@.gmail.com> wrote in message
news:1156924170.713163.303500@.h48g2000cwc.googlegroups.com...
> Hi,
> We have a requirement to read the .trc files generated by SQL server
> in real-time or atleast near real-time. We do not want to interfere
> with existing SQL server setup by running functions like
> fn_trace_get_table. The requirement is to get the events logged in .trc
> files by directly reading them. SQL Profiler can read the trace files,
> but looks like the .trc file format is not published by Microsoft. But
> I also see some tools like Idera's SQL Compliance Manager are capable
> of directly reading the trace files. Can any one suggest an approach to
> read trace file without having to disturb the existing SQL server
> setup.
> Thanks
> ad kompella
>|||On 30 Aug 2006 00:49:30 -0700, "adkompella" <adkompella@.gmail.com>
wrote:
> We have a requirement to read the .trc files generated by SQL server
>in real-time or atleast near real-time. We do not want to interfere
>with existing SQL server setup by running functions like
>fn_trace_get_table. The requirement is to get the events logged in .trc
>files by directly reading them. SQL Profiler can read the trace files,
>but looks like the .trc file format is not published by Microsoft. But
>I also see some tools like Idera's SQL Compliance Manager are capable
>of directly reading the trace files. Can any one suggest an approach to
>read trace file without having to disturb the existing SQL server
>setup.
If you're in a serious hurry, I believe you can write your own
listener using debug API hooks.
J.|||Thanks for the reply. But I have no idea about the debug API hooks, can
you give me any pointers to the debug API hooks for this particular
requirements?
kompella
JXStern wrote:
> >
> If you're in a serious hurry, I believe you can write your own
> listener using debug API hooks.
> J.|||On 30 Aug 2006 21:45:31 -0700, "adkompella" <adkompella@.gmail.com>
wrote:
>Thanks for the reply. But I have no idea about the debug API hooks, can
>you give me any pointers to the debug API hooks for this particular
>requirements?
>kompella
>JXStern wrote:
>> >
>> If you're in a serious hurry, I believe you can write your own
>> listener using debug API hooks.
I'm not current on this, but if you search the online doc and post to
the windows API newsgroups, should be able to figure it out.
Sorry not to have it at hand.
J.

I have a requirement in SSRS where in i need to generate a report in which initially there will be one record (say, a manager of a department and some values corresponding to him ) now i need to provide a drill-down on this employee(mamager) such that the next level of employees reporting to him should be displayed with all the parameters and this has to continue till the leaf level employee.

Thanks in advance.

will the number of levels be known in advance or is it parent-child?

RS is not very good at dealing with an arbitary number of grouping levels.

One way to do it would be define a report with a manager parameter that renders a table of subordiantes. Then in a master report, nest the subreport in a table for all expected levels passing the employee id to the subreport. Problem with that is that it wouldn't export very well.

The other ooption would be to PIVOT the parent-child table i.e. flatten it out into columns per each level, then build your table based on that.

Either way you'll need to know the number of levels in advance.

|||

Yes the number of levels will be known in advance.

i'm a novice in SSRS and does not much about it so can you throw some light on what you have just said.

|||

Please tell me the structure of the data and we'll take it from there.

Is it SQL or OLAP? A table or a dimension? what are the columns or levels?

|||

Hi adam,

I'll get an employee id as a parameter to the stored procedure and then i'll get a dataset which will contain all the data for me for all the employees.

Then from this data set i need to generate the drill down report as

(the root level employee)

(then his subordinates)

(then for each employee in the above level we need to get the lower levels here.)

............................................

And this hierarchy goes on till the last employee.

Thanks in advance.|||

ok, I'll ask the question again.

In format does the data come back? What are the columns?

is it employee_id, manager_id (where manager _id is the employee_id of the manager)

is it employee_level_1_id, employee_level_2_id, employee_level_3_id,......

The latter is what you want to end up with to structure your report.

|||

Hi adam,

The data is in the following format

EmployeeID ManagerID Level(the level of the employee)

Thanks in advance.

|||

OK what about the top level manager, is his manager_id = employee_id or is it NULL?

Does the Level column representative of the number of levels in the hierarchy? In it numeric or a textual descriptor?

I assume you also have employee_name an well as some other employee information.

Can you post a little bit of dummy data?

|||

top level managertop level manager is NULL and you will have columns like

EMP_NO , EMP_NAME , MGR_NO , LEVEL(int) , and some other fields............................

12345 xyz 32141 1

level ranges from 1-some known value

|||

The following is a code example of my implementation. Copy the xml into a file and save as .RDL.

You may need to modify the connection as locally I have SQL 2005 installed as a named instance.

The code flattens a table by performing a join onto itself for every level in the hierarchy. I'll try to come up with a neater solution because it feels like I should be able to use the new PIVOT functionality of SQL.

[code]

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ds_master">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=.\sql2005;Initial Catalog=master</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>008a7b74-651a-4e19-ad0d-a1af34c884ef</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Table Name="table1">
<DataSetName>dst_employee</DataSetName>
<Top>0.91429cm</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="l1_employee_level">
<rd:DefaultName>l1_employee_level</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>#6e9eca</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l1_employee_level.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l1_employee_id">
<rd:DefaultName>l1_employee_id</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l1_employee_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l1_employee_name">
<rd:DefaultName>l1_employee_name</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l1_employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.53333cm</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!l1_employee_level.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_l1_employee_level">
<GroupExpressions>
<GroupExpression>=Fields!l1_employee_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Visibility>
<ToggleItem>l1_employee_level</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="l2_employee_level">
<rd:DefaultName>l2_employee_level</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SlateGray</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l2_employee_level.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l2_employee_id">
<rd:DefaultName>l2_employee_id</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l2_employee_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l2_employee_name">
<rd:DefaultName>l2_employee_name</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l2_employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.53333cm</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!l2_employee_level.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_l2_employee_level">
<Filters>
<Filter>
<Operator>NotEqual</Operator>
<FilterValues>
<FilterValue>0</FilterValue>
</FilterValues>
<FilterExpression>=Iif(Fields!l2_employee_id.Value, CStr(Fields!l2_employee_id.Value), "0")</FilterExpression>
</Filter>
</Filters>
<GroupExpressions>
<GroupExpression>=Fields!l2_employee_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Visibility>
<ToggleItem>l2_employee_level</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="l3_employee_level">
<rd:DefaultName>l3_employee_level</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>#8fa0b0</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l3_employee_level.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l3_employee_id">
<rd:DefaultName>l3_employee_id</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l3_employee_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="l3_employee_name">
<rd:DefaultName>l3_employee_name</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!l3_employee_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.53333cm</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!l3_employee_level.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_l3_employee_level">
<Filters>
<Filter>
<Operator>NotEqual</Operator>
<FilterValues>
<FilterValue>0</FilterValue>
</FilterValues>
<FilterExpression>=Iif(Fields!l3_employee_id.Value, CStr(Fields!l3_employee_id.Value), "0")</FilterExpression>
</Filter>
</Filters>
<GroupExpressions>
<GroupExpression>=Fields!l3_employee_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<ZIndex>1</ZIndex>
<Width>7.61904cm</Width>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>level</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>id</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>name</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.55873cm</Height>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<TableColumns>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
<TableColumn>
<Width>2.53968cm</Width>
</TableColumn>
</TableColumns>
<Height>2.15872cm</Height>
</Table>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>20pt</FontSize>
<Color>SteelBlue</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.91429cm</Height>
<Value>Report3</Value>
</Textbox>
</ReportItems>
<Height>3.60635cm</Height>
</Body>
<rd:ReportID>0b7fa61b-6ddf-4332-bf17-5b42a10a7437</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="dst_employee">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>WITH employee AS
(
SELECT employee_id = 1, employee_name = 'Manager 1', manager_id = NULL, level_no = 1
UNION ALL
SELECT employee_id = 2, employee_name = 'Sub Manager 1', manager_id = 1, level_no = 2
UNION ALL
SELECT employee_id = 3, employee_name = 'Leaf Employee 1', manager_id = 2, level_no = 3
UNION ALL
SELECT employee_id = 4, employee_name = 'Leaf Employee 2', manager_id = 1, level_no = 2
)
SELECT l1_employee_id = l1.employee_id
, l1_employee_name = l1.employee_name
, l1_employee_level = l1.level_no

, l2_employee_id = l2.employee_id
, l2_employee_name = l2.employee_name
, l2_employee_level = l2.level_no

, l3_employee_id = l3.employee_id
, l3_employee_name = l3.employee_name
, l3_employee_level = l3.level_no

FROM employee l1

LEFT JOIN employee l2
ON l1.employee_id = l2.manager_id

LEFT JOIN employee l3
ON l2.employee_id = l3.manager_id

WHERE ISNULL(l1.level_no, 1) = 1
AND ISNULL(l2.level_no, 2) = 2
AND ISNULL(l3.level_no, 3) = 3</CommandText>
<DataSourceName>ds_master</DataSourceName>
</Query>
<Fields>
<Field Name="l1_employee_id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l1_employee_id</DataField>
</Field>
<Field Name="l1_employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>l1_employee_name</DataField>
</Field>
<Field Name="l1_employee_level">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l1_employee_level</DataField>
</Field>
<Field Name="l2_employee_id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l2_employee_id</DataField>
</Field>
<Field Name="l2_employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>l2_employee_name</DataField>
</Field>
<Field Name="l2_employee_level">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l2_employee_level</DataField>
</Field>
<Field Name="l3_employee_id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l3_employee_id</DataField>
</Field>
<Field Name="l3_employee_name">
<rd:TypeName>System.String</rd:TypeName>
<DataField>l3_employee_name</DataField>
</Field>
<Field Name="l3_employee_level">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>l3_employee_level</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>22.85714cm</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>

[/code]

Friday, March 30, 2012

Reading .trc files on SQL Server 2000/2005

Reading .trc files on SQL Server 2000/2005

Friday, March 23, 2012

read from .ini file

Tuesday, March 20, 2012

Re: Any one help me out!

Raster Catalog

Blog Archive

About Me