Hi,
I have a report that is going to have to show the following.
Columns 1-3 will be counts of a certain activity.
Columns 4-12 will have calculations based on the data contained in the 3rd
count.
I can get the counts, and I can perform the calculations just fine. Where I
run into a problem is on the display. I have a table with some grouping that
will do the calculations and show columns 3-12, with the data available if
the user wishes to drill down. If I have second data set with my counts how
can I get the data from it where my keys are equal? I know that I could put
all the counts in my data set but I donâ't want to have them in each row
because I am expecting to only have around 100 groups, but over 8000 rows of
data will be need to calculate columns 4-12.
I guess what I am really after is a way to query a dataset, or to join them
inside of the rdl.This may help explain what I am trying to accomplish.
<?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="AKAIMS Production">
<rd:DataSourceID>af566fca-304d-4f11-bc74-505d4c0175b4</rd:DataSourceID>
<DataSourceReference>AKAIMS Production</DataSourceReference>
</DataSource>
</DataSources>
<InteractiveHeight>11in</InteractiveHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<RightMargin>1in</RightMargin>
<LeftMargin>1in</LeftMargin>
<BottomMargin>1in</BottomMargin>
<rd:ReportID>0e5aad5b-e956-4edb-8be1-9dbe4893dc7d</rd:ReportID>
<DataSets>
<DataSet Name="Counts">
<Fields>
<Field Name="agency_id">
<DataField>agency_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CountA">
<DataField>CountA</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="CountB">
<DataField>CountB</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>AKAIMS Production</DataSourceName>
<CommandText>SELECT 1 As agency_id, 13 AS CountA, 21 As CountB
UNION
SELECT 2 As agency_id, 14 AS CountA, 24 As CountB</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="Detail">
<Fields>
<Field Name="agency_id">
<DataField>agency_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Data">
<DataField>Data</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>AKAIMS Production</DataSourceName>
<CommandText>SELECT 1 As agency_id, 1 AS Data
UNION
SELECT 1 As agency_id, 2 AS Data
UNION
SELECT 2 As agency_id, 3 AS Data
UNION
SELECT 2 As agency_id, 4 AS Data</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<Width>5.5in</Width>
<Body>
<ReportItems>
<Table Name="table1">
<DataSetName>Detail</DataSetName>
<TableGroups>
<TableGroup>
<Grouping Name="table1_Group1_agency">
<GroupExpressions>
<GroupExpression>=Fields!agency_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!agency_id.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>23</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!CountA.Value, "Counts")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>22</ZIndex>
<CanGrow>true</CanGrow>
<Value>=iif(Fields!agency_id.Value=1,"13","14")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<rd:DefaultName>textbox20</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>21</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!CountB.Value, "Counts")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox25">
<rd:DefaultName>textbox25</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>20</ZIndex>
<CanGrow>true</CanGrow>
<Value>=iif(Fields!agency_id.Value=1,"21","24")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="agency_id">
<rd:DefaultName>agency_id</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>19</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Fields!agency_id.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<rd:DefaultName>textbox12</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>18</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<rd:DefaultName>textbox13</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>11</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<rd:DefaultName>textbox17</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>10</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox22">
<rd:DefaultName>textbox22</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>9</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox27">
<rd:DefaultName>textbox27</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>8</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<rd:DefaultName>textbox14</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>7</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<rd:DefaultName>textbox15</rd:DefaultName>
<Style>
<BorderColor>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<BorderWidth>
<Top>1pt</Top>
</BorderWidth>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>6</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Avg(Fields!Data.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
</TableGroups>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>5</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<rd:DefaultName>textbox16</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>4</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox21">
<rd:DefaultName>textbox21</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>3</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox26">
<rd:DefaultName>textbox26</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>2</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>1</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Data">
<rd:DefaultName>Data</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Data.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>29</ZIndex>
<CanGrow>true</CanGrow>
<Value>Count A</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>28</ZIndex>
<CanGrow>true</CanGrow>
<Value>Desired Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<rd:DefaultName>textbox19</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>27</ZIndex>
<CanGrow>true</CanGrow>
<Value>Count B</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox29">
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>26</ZIndex>
<CanGrow>true</CanGrow>
<Value>Desired Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>25</ZIndex>
<CanGrow>true</CanGrow>
<Value>agency id</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>24</ZIndex>
<CanGrow>true</CanGrow>
<Value>Data</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
<TableColumn>
<Width>1.25in</Width>
</TableColumn>
<TableColumn>
<Width>0.75in</Width>
</TableColumn>
</TableColumns>
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>17</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<rd:DefaultName>textbox18</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>16</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox23">
<rd:DefaultName>textbox23</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>15</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox28">
<rd:DefaultName>textbox28</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>14</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>13</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<Style>
<BorderColor>
<Top>Black</Top>
</BorderColor>
<BorderStyle>
<Top>Double</Top>
</BorderStyle>
<BorderWidth>
<Top>2pt</Top>
</BorderWidth>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
<PaddingBottom>2pt</PaddingBottom>
</Style>
<ZIndex>12</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Avg(Fields!Data.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
</Table>
</ReportItems>
<Height>1.25in</Height>
</Body>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
"AKCodeRat" wrote:
> Hi,
> I have a report that is going to have to show the following.
> Columns 1-3 will be counts of a certain activity.
> Columns 4-12 will have calculations based on the data contained in the 3rd
> count.
>
> I can get the counts, and I can perform the calculations just fine. Where I
> run into a problem is on the display. I have a table with some grouping that
> will do the calculations and show columns 3-12, with the data available if
> the user wishes to drill down. If I have second data set with my counts how
> can I get the data from it where my keys are equal? I know that I could put
> all the counts in my data set but I donâ't want to have them in each row
> because I am expecting to only have around 100 groups, but over 8000 rows of
> data will be need to calculate columns 4-12.
> I guess what I am really after is a way to query a dataset, or to join them
> inside of the rdl.
>
Showing posts with label activity. Show all posts
Showing posts with label activity. Show all posts
Thursday, March 22, 2012
Sunday, March 11, 2012
Datamart
Hi,
I need some advise. We are in he process of designing our first data
mart. This Datamart will consists of sales orders activity. Sales
orders have lines created, deleted and inserted on a daily basis as
long as the sales order is open.
It has been suggested that we do triggers the these tables and then
populate the Datamart. Which the DBA is not thrilled with. is there
another way to do this wihtout triggers.?
The data needs to get updated daily and the volume is about 500K rows.
The data is being sourced from an ERP and the DB is oracle 9i. We also
have the option of using SQL Server if need be. Our BI tool is Cognos
and ETL tool is not an option since our data tranformations are very
simple.
Please share your experience.
Regards
Ather
Ather
I only put complete / closed / posted items into my DWH unless the business
can justify why they want incomplete / incorrect data in the DWH, so i would
only transfer closed orders
Use MS- SQL as your DWH server as you get DTS (Data Transformation
Services) with it, which can be used as a lightweight ETL tool
Create some DTS packages to update your DWH at the end of each day ( or any
other viable interval) with the new closed orders
Unless you have paid for / are forced to use COGNOS look carefully at
reporting services and analysis services as your BI tools
Regards
Reg Besseling
"Ather Mian" <ather@.nowwhere.com> wrote in message
news:eIdrqSV4EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need some advise. We are in he process of designing our first data
> mart. This Datamart will consists of sales orders activity. Sales
> orders have lines created, deleted and inserted on a daily basis as
> long as the sales order is open.
> It has been suggested that we do triggers the these tables and then
> populate the Datamart. Which the DBA is not thrilled with. is there
> another way to do this wihtout triggers.?
> The data needs to get updated daily and the volume is about 500K rows.
> The data is being sourced from an ERP and the DB is oracle 9i. We also
> have the option of using SQL Server if need be. Our BI tool is Cognos
> and ETL tool is not an option since our data tranformations are very
> simple.
> Please share your experience.
> Regards
> Ather
>
|||Identify all the OPEN orders (order status or some other combination of
field(s) ) which will narrow the list of orders that will get affected in
your datamart. For these open orders:
(1) Delete the orders from your datamart.
(2) Re-insert the data about the open orders.
I am making an assumption that you do not have track the order status
history itself. I am also making an assumption that data about "closed"
orders does not change and/or you do not want to change them in the datamart
in any way.
Hope this helps.
Rangarajan Suresh
www.picarossolutions.com
*********************
"Ather Mian" wrote:
> Hi,
> I need some advise. We are in he process of designing our first data
> mart. This Datamart will consists of sales orders activity. Sales
> orders have lines created, deleted and inserted on a daily basis as
> long as the sales order is open.
> It has been suggested that we do triggers the these tables and then
> populate the Datamart. Which the DBA is not thrilled with. is there
> another way to do this wihtout triggers.?
> The data needs to get updated daily and the volume is about 500K rows.
> The data is being sourced from an ERP and the DB is oracle 9i. We also
> have the option of using SQL Server if need be. Our BI tool is Cognos
> and ETL tool is not an option since our data tranformations are very
> simple.
> Please share your experience.
> Regards
> Ather
>
>
|||What Will happen to orders that change from open to closed ? it seems to me
that they will get "Lost" until the next full refresh
Regards
Reg Besseling
"Rangarajan Suresh" <RangarajanSuresh@.discussions.microsoft.com> wrote in
message news:DC96DE91-8994-41BD-A84F-5FED65FBA3D6@.microsoft.com...[vbcol=seagreen]
> Identify all the OPEN orders (order status or some other combination of
> field(s) ) which will narrow the list of orders that will get affected in
> your datamart. For these open orders:
> (1) Delete the orders from your datamart.
> (2) Re-insert the data about the open orders.
> I am making an assumption that you do not have track the order status
> history itself. I am also making an assumption that data about "closed"
> orders does not change and/or you do not want to change them in the
> datamart
> in any way.
> Hope this helps.
> Rangarajan Suresh
> www.picarossolutions.com
> *********************
>
> "Ather Mian" wrote:
|||I guess I should have also addressed the issue of "closed orders" along with
"open orders" -- The closed orders are to be loaded as inserts into the
datamart. They do not necessarily fall under the issue of "replacing"
existing open orders that may have changed in some fashion. Since we are
deleting existing open orders from the datamart, the same order will not go
into the datamart as it is no longer open.
Sorry for the confusion.
Rangarajan Suresh
www.picarossolutions.com
*********************
"Reg Besseling" wrote:
> What Will happen to orders that change from open to closed ? it seems to me
> that they will get "Lost" until the next full refresh
> Regards
> Reg Besseling
> "Rangarajan Suresh" <RangarajanSuresh@.discussions.microsoft.com> wrote in
> message news:DC96DE91-8994-41BD-A84F-5FED65FBA3D6@.microsoft.com...
>
I need some advise. We are in he process of designing our first data
mart. This Datamart will consists of sales orders activity. Sales
orders have lines created, deleted and inserted on a daily basis as
long as the sales order is open.
It has been suggested that we do triggers the these tables and then
populate the Datamart. Which the DBA is not thrilled with. is there
another way to do this wihtout triggers.?
The data needs to get updated daily and the volume is about 500K rows.
The data is being sourced from an ERP and the DB is oracle 9i. We also
have the option of using SQL Server if need be. Our BI tool is Cognos
and ETL tool is not an option since our data tranformations are very
simple.
Please share your experience.
Regards
Ather
Ather
I only put complete / closed / posted items into my DWH unless the business
can justify why they want incomplete / incorrect data in the DWH, so i would
only transfer closed orders
Use MS- SQL as your DWH server as you get DTS (Data Transformation
Services) with it, which can be used as a lightweight ETL tool
Create some DTS packages to update your DWH at the end of each day ( or any
other viable interval) with the new closed orders
Unless you have paid for / are forced to use COGNOS look carefully at
reporting services and analysis services as your BI tools
Regards
Reg Besseling
"Ather Mian" <ather@.nowwhere.com> wrote in message
news:eIdrqSV4EHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I need some advise. We are in he process of designing our first data
> mart. This Datamart will consists of sales orders activity. Sales
> orders have lines created, deleted and inserted on a daily basis as
> long as the sales order is open.
> It has been suggested that we do triggers the these tables and then
> populate the Datamart. Which the DBA is not thrilled with. is there
> another way to do this wihtout triggers.?
> The data needs to get updated daily and the volume is about 500K rows.
> The data is being sourced from an ERP and the DB is oracle 9i. We also
> have the option of using SQL Server if need be. Our BI tool is Cognos
> and ETL tool is not an option since our data tranformations are very
> simple.
> Please share your experience.
> Regards
> Ather
>
|||Identify all the OPEN orders (order status or some other combination of
field(s) ) which will narrow the list of orders that will get affected in
your datamart. For these open orders:
(1) Delete the orders from your datamart.
(2) Re-insert the data about the open orders.
I am making an assumption that you do not have track the order status
history itself. I am also making an assumption that data about "closed"
orders does not change and/or you do not want to change them in the datamart
in any way.
Hope this helps.
Rangarajan Suresh
www.picarossolutions.com
*********************
"Ather Mian" wrote:
> Hi,
> I need some advise. We are in he process of designing our first data
> mart. This Datamart will consists of sales orders activity. Sales
> orders have lines created, deleted and inserted on a daily basis as
> long as the sales order is open.
> It has been suggested that we do triggers the these tables and then
> populate the Datamart. Which the DBA is not thrilled with. is there
> another way to do this wihtout triggers.?
> The data needs to get updated daily and the volume is about 500K rows.
> The data is being sourced from an ERP and the DB is oracle 9i. We also
> have the option of using SQL Server if need be. Our BI tool is Cognos
> and ETL tool is not an option since our data tranformations are very
> simple.
> Please share your experience.
> Regards
> Ather
>
>
|||What Will happen to orders that change from open to closed ? it seems to me
that they will get "Lost" until the next full refresh
Regards
Reg Besseling
"Rangarajan Suresh" <RangarajanSuresh@.discussions.microsoft.com> wrote in
message news:DC96DE91-8994-41BD-A84F-5FED65FBA3D6@.microsoft.com...[vbcol=seagreen]
> Identify all the OPEN orders (order status or some other combination of
> field(s) ) which will narrow the list of orders that will get affected in
> your datamart. For these open orders:
> (1) Delete the orders from your datamart.
> (2) Re-insert the data about the open orders.
> I am making an assumption that you do not have track the order status
> history itself. I am also making an assumption that data about "closed"
> orders does not change and/or you do not want to change them in the
> datamart
> in any way.
> Hope this helps.
> Rangarajan Suresh
> www.picarossolutions.com
> *********************
>
> "Ather Mian" wrote:
|||I guess I should have also addressed the issue of "closed orders" along with
"open orders" -- The closed orders are to be loaded as inserts into the
datamart. They do not necessarily fall under the issue of "replacing"
existing open orders that may have changed in some fashion. Since we are
deleting existing open orders from the datamart, the same order will not go
into the datamart as it is no longer open.
Sorry for the confusion.
Rangarajan Suresh
www.picarossolutions.com
*********************
"Reg Besseling" wrote:
> What Will happen to orders that change from open to closed ? it seems to me
> that they will get "Lost" until the next full refresh
> Regards
> Reg Besseling
> "Rangarajan Suresh" <RangarajanSuresh@.discussions.microsoft.com> wrote in
> message news:DC96DE91-8994-41BD-A84F-5FED65FBA3D6@.microsoft.com...
>
Sunday, February 26, 2012
Databases Starting Repeatedly
I have four databases that show up in the database log as
starting over and over again. While this activity is
going on the web based application is unable to connect.
I have checked all available log files for errors but have
not received the first one as of yet.
I need to call on the experts in the field. Have any of
you ever seen this before? If so, what did you do to
correct the problem?
This is SQL Server 2000 Sp3 running on Windows 2000 Sp3
with 2GB of ram and two 2.4 GHZ processors.
I would appreciate anyone's help as this is a show stopper
for me. Thanks in advance.
A portion of log follows.
**********************************************
2003-09-29 09:53:38.42 server Microsoft SQL Server
2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195:
Service Pack 3)
2003-09-29 09:53:38.42 server Copyright (C) 1988-2002
Microsoft Corporation.
2003-09-29 09:53:38.42 server All rights reserved.
2003-09-29 09:53:38.42 server Server Process ID is 996.
2003-09-29 09:53:38.42 server Logging SQL Server
messages in file 'e:\Program Files\Microsoft SQL
Server\MSSQL\log\ERRORLOG'.
2003-09-29 09:53:38.42 server SQL Server is starting at
priority class 'normal'(4 CPUs detected).
2003-09-29 09:53:38.67 server SQL Server configured for
thread mode processing.
2003-09-29 09:53:38.67 server Using dynamic lock
allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2003-09-29 09:53:38.79 server Attempting to initialize
Distributed Transaction Coordinator.
2003-09-29 09:53:40.29 spid3 Starting up
database 'master'.
2003-09-29 09:53:40.74 spid3 Server name is 'HSMDB'.
2003-09-29 09:53:40.74 spid3 Skipping startup of clean
database id 5
2003-09-29 09:53:40.78 spid3 Skipping startup of clean
database id 6
2003-09-29 09:53:40.78 spid3 Skipping startup of clean
database id 7
2003-09-29 09:53:40.78 spid5 Starting up
database 'msdb'.
2003-09-29 09:53:40.78 spid6 Starting up
database 'hsmqa'.
2003-09-29 09:53:40.78 spid7 Starting up
database 'WebTrend'.
2003-09-29 09:53:40.78 server Using 'SSNETLIB.DLL'
version '8.0.760'.
2003-09-29 09:53:40.78 spid8 Starting up
database 'model'.
2003-09-29 09:53:40.78 spid9 Starting up
database 'WebData'.
2003-09-29 09:53:40.78 spid12 Starting up
database 'WebCache'.
2003-09-29 09:53:40.81 spid7 Analysis of
database 'WebTrend' (10) is 100% complete (approximately 0
more seconds)
2003-09-29 09:53:40.85 spid12 Analysis of
database 'WebCache' (12) is 100% complete (approximately 0
more seconds)
2003-09-29 09:53:40.92 server SQL server listening on
TCP, Shared Memory, Named Pipes.
2003-09-29 09:53:40.92 server SQL Server is ready for
client connections
2003-09-29 09:53:40.92 spid8 Clearing tempdb database.
2003-09-29 09:53:41.10 spid8 Starting up
database 'tempdb'.
2003-09-29 09:53:41.21 spid3 Recovery complete.
2003-09-29 09:53:41.21 spid3 SQL global counter
collection task is created.
2003-09-29 09:53:42.45 spid3 Launched startup
procedure 'hbi_unlock_objects'
2003-09-29 09:53:43.40 spid52 Using 'xpsqlbot.dll'
version '2000.80.194' to execute extended stored
procedure 'xp_qv'.
2003-09-29 09:54:26.79 spid53 Using 'xpstar.dll'
version '2000.80.760' to execute extended stored
procedure 'sp_MSgetversion'.
2003-09-29 09:54:30.64 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:54:30.75 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:54:30.84 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:44.43 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:44.53 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:44.62 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:45.98 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.07 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.17 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.26 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.36 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.45 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.57 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:46.67 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:46.76 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:46.86 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:46.95 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:47.04 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:47.14 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.23 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.32 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.42 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.53 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.62 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.76 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:47.86 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:47.95 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:50.21 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:50.31 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:50.40 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:50.93 spid53 Starting up
database 'hsmprod'.
2003-09-29 10:00:01.78 spid86 Starting up
database 'hsmtest'.
2003-09-29 10:00:01.87 spid86 Starting up
database 'hsmtrain'.
2003-09-29 10:00:01.98 spid86 Starting up
database 'hsmtest'.
2003-09-29 10:00:02.07 spid86 Starting up
database 'hsmtrain'.
2003-09-29 10:00:02.40 spid87 Starting up
database 'hsmtest'.
2003-09-29 10:00:02.51 spid87 Starting up
database 'hsmtrain'.Hi Thomas,
Such issues tend to be complex and require extensive research. I'd like to
set the right expectations and let you know that it may take a while for us
to help you narrow down the problem.
Please check to see if the "Auto close" is checked in the Option tab in the
property dialog of the database. If so, make sure this option is unchecked
and try to connect to SQL Server again. Does this resolve the problem?
If not, I would like you to provide me with more information regarding this
issue so that I can narrow down it.
Please make sure that the MSSQLServer service, for the SQL Server you are
trying to connect to, is started and running before doing any further
troubleshooting.
1. What do you mean by "the web based application is unable to connect"?
How do you connect SQL Server? Please describe it in detail. Do any error
messages occur?
2. Ping the server using the server name and IP address. See if the request
completes successfully or times out.
3. Can you connect to SQL Server using Query Analyzer? If not, does any
error message occur?
4. What is the MDAC version on the client machine? For more information
regarding how to check the MDAC version, please refer to the following
article:
301202 HOW TO: Check for MDAC Version
http://support.microsoft.com/?id=301202
5. Please provide the complete error log, you can send it at
v-yshao@.microsoft.com
I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Most probably the autoclose database option turned on.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Thomas Rhodes" <thomas.rhodes@.nghs.com> wrote in message
news:1c7601c386a0$fb9485d0$a001280a@.phx.gbl...
> I have four databases that show up in the database log as
> starting over and over again. While this activity is
> going on the web based application is unable to connect.
> I have checked all available log files for errors but have
> not received the first one as of yet.
> I need to call on the experts in the field. Have any of
> you ever seen this before? If so, what did you do to
> correct the problem?
> This is SQL Server 2000 Sp3 running on Windows 2000 Sp3
> with 2GB of ram and two 2.4 GHZ processors.
> I would appreciate anyone's help as this is a show stopper
> for me. Thanks in advance.
> A portion of log follows.
> **********************************************
> 2003-09-29 09:53:38.42 server Microsoft SQL Server
> 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195:
> Service Pack 3)
> 2003-09-29 09:53:38.42 server Copyright (C) 1988-2002
> Microsoft Corporation.
> 2003-09-29 09:53:38.42 server All rights reserved.
> 2003-09-29 09:53:38.42 server Server Process ID is 996.
> 2003-09-29 09:53:38.42 server Logging SQL Server
> messages in file 'e:\Program Files\Microsoft SQL
> Server\MSSQL\log\ERRORLOG'.
> 2003-09-29 09:53:38.42 server SQL Server is starting at
> priority class 'normal'(4 CPUs detected).
> 2003-09-29 09:53:38.67 server SQL Server configured for
> thread mode processing.
> 2003-09-29 09:53:38.67 server Using dynamic lock
> allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
> 2003-09-29 09:53:38.79 server Attempting to initialize
> Distributed Transaction Coordinator.
> 2003-09-29 09:53:40.29 spid3 Starting up
> database 'master'.
> 2003-09-29 09:53:40.74 spid3 Server name is 'HSMDB'.
> 2003-09-29 09:53:40.74 spid3 Skipping startup of clean
> database id 5
> 2003-09-29 09:53:40.78 spid3 Skipping startup of clean
> database id 6
> 2003-09-29 09:53:40.78 spid3 Skipping startup of clean
> database id 7
> 2003-09-29 09:53:40.78 spid5 Starting up
> database 'msdb'.
> 2003-09-29 09:53:40.78 spid6 Starting up
> database 'hsmqa'.
> 2003-09-29 09:53:40.78 spid7 Starting up
> database 'WebTrend'.
> 2003-09-29 09:53:40.78 server Using 'SSNETLIB.DLL'
> version '8.0.760'.
> 2003-09-29 09:53:40.78 spid8 Starting up
> database 'model'.
> 2003-09-29 09:53:40.78 spid9 Starting up
> database 'WebData'.
> 2003-09-29 09:53:40.78 spid12 Starting up
> database 'WebCache'.
> 2003-09-29 09:53:40.81 spid7 Analysis of
> database 'WebTrend' (10) is 100% complete (approximately 0
> more seconds)
> 2003-09-29 09:53:40.85 spid12 Analysis of
> database 'WebCache' (12) is 100% complete (approximately 0
> more seconds)
> 2003-09-29 09:53:40.92 server SQL server listening on
> TCP, Shared Memory, Named Pipes.
> 2003-09-29 09:53:40.92 server SQL Server is ready for
> client connections
> 2003-09-29 09:53:40.92 spid8 Clearing tempdb database.
> 2003-09-29 09:53:41.10 spid8 Starting up
> database 'tempdb'.
> 2003-09-29 09:53:41.21 spid3 Recovery complete.
> 2003-09-29 09:53:41.21 spid3 SQL global counter
> collection task is created.
> 2003-09-29 09:53:42.45 spid3 Launched startup
> procedure 'hbi_unlock_objects'
> 2003-09-29 09:53:43.40 spid52 Using 'xpsqlbot.dll'
> version '2000.80.194' to execute extended stored
> procedure 'xp_qv'.
> 2003-09-29 09:54:26.79 spid53 Using 'xpstar.dll'
> version '2000.80.760' to execute extended stored
> procedure 'sp_MSgetversion'.
> 2003-09-29 09:54:30.64 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:54:30.75 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:54:30.84 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:44.43 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:44.53 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:44.62 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:45.98 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.07 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.17 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.26 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.36 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.45 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.57 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:46.67 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:46.76 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:46.86 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:46.95 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:47.04 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:47.14 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.23 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.32 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.42 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.53 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.62 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.76 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:47.86 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:47.95 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:50.21 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:50.31 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:50.40 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:50.93 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 10:00:01.78 spid86 Starting up
> database 'hsmtest'.
> 2003-09-29 10:00:01.87 spid86 Starting up
> database 'hsmtrain'.
> 2003-09-29 10:00:01.98 spid86 Starting up
> database 'hsmtest'.
> 2003-09-29 10:00:02.07 spid86 Starting up
> database 'hsmtrain'.
> 2003-09-29 10:00:02.40 spid87 Starting up
> database 'hsmtest'.
> 2003-09-29 10:00:02.51 spid87 Starting up
> database 'hsmtrain'.|||Michael:
First, Thank you for your reply. I appreciate the
professional level and degree of your response.
Second, yes the Auto Close was set. I have since removed
the check mark. I have several other databases running on
this server in support of the web based application I
mentioned. These other databases started normally and
remained so after I had restarted the server. If I had
given more thought to situation I could have compared the
properties between the problem databases and those that
functioned as expected. The problem ceased yesterday
morning around 10:00am even with the auto close still
set. I suspect the web application connection succeeded
and maintain its connection thereafter.
Just quickly, should others have interest, I'll respond to
your questions.
1. The web application could not connect from the web
application server. This is a separate server from the
database. I am not exactly sure how the vendor connects
to the database in the code behind the pages. No errors
reported back to Internet Explorer except that the page
could not be found. Basically the weblogic web server
could not start without the connection to the database.
2. I did ping all the servers associated with this
application. I receive successful results on all counts.
3. Yes I was able to connect to the database server using
Query Analyzer and was able to query tables and data from
the affected databases.
4. I have the latest MDAC installation that comes with
SQL SP3a.
Again, I thank you for your help.
Thomas
>--Original Message--
>Hi Thomas,
>Such issues tend to be complex and require extensive
research. I'd like to
>set the right expectations and let you know that it may
take a while for us
>to help you narrow down the problem.
>Please check to see if the "Auto close" is checked in the
Option tab in the
>property dialog of the database. If so, make sure this
option is unchecked
>and try to connect to SQL Server again. Does this resolve
the problem?
>If not, I would like you to provide me with more
information regarding this
>issue so that I can narrow down it.
>Please make sure that the MSSQLServer service, for the
SQL Server you are
>trying to connect to, is started and running before doing
any further
>troubleshooting.
>1. What do you mean by "the web based application is
unable to connect"?
>How do you connect SQL Server? Please describe it in
detail. Do any error
>messages occur?
>2. Ping the server using the server name and IP address.
See if the request
>completes successfully or times out.
>3. Can you connect to SQL Server using Query Analyzer? If
not, does any
>error message occur?
>4. What is the MDAC version on the client machine? For
more information
>regarding how to check the MDAC version, please refer to
the following
>article:
>301202 HOW TO: Check for MDAC Version
>http://support.microsoft.com/?id=301202
>5. Please provide the complete error log, you can send it
at
>v-yshao@.microsoft.com
>I am standing by for your response.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>|||Hi Thomas,
Thank you for taking time to write in your experience so that it may help
other customers who may encounter similar issues. As per your previous
post, I am going consider this issue as Resolved. However, should you have
any further questions or concerns, please feel free to send your post here.
I will continue to work with you.
Thanks for using MSDN newsgroup again.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
starting over and over again. While this activity is
going on the web based application is unable to connect.
I have checked all available log files for errors but have
not received the first one as of yet.
I need to call on the experts in the field. Have any of
you ever seen this before? If so, what did you do to
correct the problem?
This is SQL Server 2000 Sp3 running on Windows 2000 Sp3
with 2GB of ram and two 2.4 GHZ processors.
I would appreciate anyone's help as this is a show stopper
for me. Thanks in advance.
A portion of log follows.
**********************************************
2003-09-29 09:53:38.42 server Microsoft SQL Server
2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.0 (Build 2195:
Service Pack 3)
2003-09-29 09:53:38.42 server Copyright (C) 1988-2002
Microsoft Corporation.
2003-09-29 09:53:38.42 server All rights reserved.
2003-09-29 09:53:38.42 server Server Process ID is 996.
2003-09-29 09:53:38.42 server Logging SQL Server
messages in file 'e:\Program Files\Microsoft SQL
Server\MSSQL\log\ERRORLOG'.
2003-09-29 09:53:38.42 server SQL Server is starting at
priority class 'normal'(4 CPUs detected).
2003-09-29 09:53:38.67 server SQL Server configured for
thread mode processing.
2003-09-29 09:53:38.67 server Using dynamic lock
allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2003-09-29 09:53:38.79 server Attempting to initialize
Distributed Transaction Coordinator.
2003-09-29 09:53:40.29 spid3 Starting up
database 'master'.
2003-09-29 09:53:40.74 spid3 Server name is 'HSMDB'.
2003-09-29 09:53:40.74 spid3 Skipping startup of clean
database id 5
2003-09-29 09:53:40.78 spid3 Skipping startup of clean
database id 6
2003-09-29 09:53:40.78 spid3 Skipping startup of clean
database id 7
2003-09-29 09:53:40.78 spid5 Starting up
database 'msdb'.
2003-09-29 09:53:40.78 spid6 Starting up
database 'hsmqa'.
2003-09-29 09:53:40.78 spid7 Starting up
database 'WebTrend'.
2003-09-29 09:53:40.78 server Using 'SSNETLIB.DLL'
version '8.0.760'.
2003-09-29 09:53:40.78 spid8 Starting up
database 'model'.
2003-09-29 09:53:40.78 spid9 Starting up
database 'WebData'.
2003-09-29 09:53:40.78 spid12 Starting up
database 'WebCache'.
2003-09-29 09:53:40.81 spid7 Analysis of
database 'WebTrend' (10) is 100% complete (approximately 0
more seconds)
2003-09-29 09:53:40.85 spid12 Analysis of
database 'WebCache' (12) is 100% complete (approximately 0
more seconds)
2003-09-29 09:53:40.92 server SQL server listening on
TCP, Shared Memory, Named Pipes.
2003-09-29 09:53:40.92 server SQL Server is ready for
client connections
2003-09-29 09:53:40.92 spid8 Clearing tempdb database.
2003-09-29 09:53:41.10 spid8 Starting up
database 'tempdb'.
2003-09-29 09:53:41.21 spid3 Recovery complete.
2003-09-29 09:53:41.21 spid3 SQL global counter
collection task is created.
2003-09-29 09:53:42.45 spid3 Launched startup
procedure 'hbi_unlock_objects'
2003-09-29 09:53:43.40 spid52 Using 'xpsqlbot.dll'
version '2000.80.194' to execute extended stored
procedure 'xp_qv'.
2003-09-29 09:54:26.79 spid53 Using 'xpstar.dll'
version '2000.80.760' to execute extended stored
procedure 'sp_MSgetversion'.
2003-09-29 09:54:30.64 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:54:30.75 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:54:30.84 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:44.43 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:44.53 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:44.62 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:45.98 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.07 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.17 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.26 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.36 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.45 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:46.57 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:46.67 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:46.76 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:46.86 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:46.95 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:47.04 spid53 Starting up
database 'hsmtest'.
2003-09-29 09:55:47.14 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.23 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.32 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.42 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.53 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.62 spid53 Starting up
database 'hsmtrain'.
2003-09-29 09:55:47.76 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:47.86 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:47.95 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:50.21 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:50.31 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:50.40 spid53 Starting up
database 'hsmprod'.
2003-09-29 09:55:50.93 spid53 Starting up
database 'hsmprod'.
2003-09-29 10:00:01.78 spid86 Starting up
database 'hsmtest'.
2003-09-29 10:00:01.87 spid86 Starting up
database 'hsmtrain'.
2003-09-29 10:00:01.98 spid86 Starting up
database 'hsmtest'.
2003-09-29 10:00:02.07 spid86 Starting up
database 'hsmtrain'.
2003-09-29 10:00:02.40 spid87 Starting up
database 'hsmtest'.
2003-09-29 10:00:02.51 spid87 Starting up
database 'hsmtrain'.Hi Thomas,
Such issues tend to be complex and require extensive research. I'd like to
set the right expectations and let you know that it may take a while for us
to help you narrow down the problem.
Please check to see if the "Auto close" is checked in the Option tab in the
property dialog of the database. If so, make sure this option is unchecked
and try to connect to SQL Server again. Does this resolve the problem?
If not, I would like you to provide me with more information regarding this
issue so that I can narrow down it.
Please make sure that the MSSQLServer service, for the SQL Server you are
trying to connect to, is started and running before doing any further
troubleshooting.
1. What do you mean by "the web based application is unable to connect"?
How do you connect SQL Server? Please describe it in detail. Do any error
messages occur?
2. Ping the server using the server name and IP address. See if the request
completes successfully or times out.
3. Can you connect to SQL Server using Query Analyzer? If not, does any
error message occur?
4. What is the MDAC version on the client machine? For more information
regarding how to check the MDAC version, please refer to the following
article:
301202 HOW TO: Check for MDAC Version
http://support.microsoft.com/?id=301202
5. Please provide the complete error log, you can send it at
v-yshao@.microsoft.com
I am standing by for your response.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Most probably the autoclose database option turned on.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Thomas Rhodes" <thomas.rhodes@.nghs.com> wrote in message
news:1c7601c386a0$fb9485d0$a001280a@.phx.gbl...
> I have four databases that show up in the database log as
> starting over and over again. While this activity is
> going on the web based application is unable to connect.
> I have checked all available log files for errors but have
> not received the first one as of yet.
> I need to call on the experts in the field. Have any of
> you ever seen this before? If so, what did you do to
> correct the problem?
> This is SQL Server 2000 Sp3 running on Windows 2000 Sp3
> with 2GB of ram and two 2.4 GHZ processors.
> I would appreciate anyone's help as this is a show stopper
> for me. Thanks in advance.
> A portion of log follows.
> **********************************************
> 2003-09-29 09:53:38.42 server Microsoft SQL Server
> 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Enterprise Edition on Windows NT 5.0 (Build 2195:
> Service Pack 3)
> 2003-09-29 09:53:38.42 server Copyright (C) 1988-2002
> Microsoft Corporation.
> 2003-09-29 09:53:38.42 server All rights reserved.
> 2003-09-29 09:53:38.42 server Server Process ID is 996.
> 2003-09-29 09:53:38.42 server Logging SQL Server
> messages in file 'e:\Program Files\Microsoft SQL
> Server\MSSQL\log\ERRORLOG'.
> 2003-09-29 09:53:38.42 server SQL Server is starting at
> priority class 'normal'(4 CPUs detected).
> 2003-09-29 09:53:38.67 server SQL Server configured for
> thread mode processing.
> 2003-09-29 09:53:38.67 server Using dynamic lock
> allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
> 2003-09-29 09:53:38.79 server Attempting to initialize
> Distributed Transaction Coordinator.
> 2003-09-29 09:53:40.29 spid3 Starting up
> database 'master'.
> 2003-09-29 09:53:40.74 spid3 Server name is 'HSMDB'.
> 2003-09-29 09:53:40.74 spid3 Skipping startup of clean
> database id 5
> 2003-09-29 09:53:40.78 spid3 Skipping startup of clean
> database id 6
> 2003-09-29 09:53:40.78 spid3 Skipping startup of clean
> database id 7
> 2003-09-29 09:53:40.78 spid5 Starting up
> database 'msdb'.
> 2003-09-29 09:53:40.78 spid6 Starting up
> database 'hsmqa'.
> 2003-09-29 09:53:40.78 spid7 Starting up
> database 'WebTrend'.
> 2003-09-29 09:53:40.78 server Using 'SSNETLIB.DLL'
> version '8.0.760'.
> 2003-09-29 09:53:40.78 spid8 Starting up
> database 'model'.
> 2003-09-29 09:53:40.78 spid9 Starting up
> database 'WebData'.
> 2003-09-29 09:53:40.78 spid12 Starting up
> database 'WebCache'.
> 2003-09-29 09:53:40.81 spid7 Analysis of
> database 'WebTrend' (10) is 100% complete (approximately 0
> more seconds)
> 2003-09-29 09:53:40.85 spid12 Analysis of
> database 'WebCache' (12) is 100% complete (approximately 0
> more seconds)
> 2003-09-29 09:53:40.92 server SQL server listening on
> TCP, Shared Memory, Named Pipes.
> 2003-09-29 09:53:40.92 server SQL Server is ready for
> client connections
> 2003-09-29 09:53:40.92 spid8 Clearing tempdb database.
> 2003-09-29 09:53:41.10 spid8 Starting up
> database 'tempdb'.
> 2003-09-29 09:53:41.21 spid3 Recovery complete.
> 2003-09-29 09:53:41.21 spid3 SQL global counter
> collection task is created.
> 2003-09-29 09:53:42.45 spid3 Launched startup
> procedure 'hbi_unlock_objects'
> 2003-09-29 09:53:43.40 spid52 Using 'xpsqlbot.dll'
> version '2000.80.194' to execute extended stored
> procedure 'xp_qv'.
> 2003-09-29 09:54:26.79 spid53 Using 'xpstar.dll'
> version '2000.80.760' to execute extended stored
> procedure 'sp_MSgetversion'.
> 2003-09-29 09:54:30.64 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:54:30.75 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:54:30.84 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:44.43 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:44.53 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:44.62 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:45.98 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.07 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.17 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.26 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.36 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.45 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:46.57 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:46.67 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:46.76 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:46.86 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:46.95 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:47.04 spid53 Starting up
> database 'hsmtest'.
> 2003-09-29 09:55:47.14 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.23 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.32 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.42 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.53 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.62 spid53 Starting up
> database 'hsmtrain'.
> 2003-09-29 09:55:47.76 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:47.86 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:47.95 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:50.21 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:50.31 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:50.40 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 09:55:50.93 spid53 Starting up
> database 'hsmprod'.
> 2003-09-29 10:00:01.78 spid86 Starting up
> database 'hsmtest'.
> 2003-09-29 10:00:01.87 spid86 Starting up
> database 'hsmtrain'.
> 2003-09-29 10:00:01.98 spid86 Starting up
> database 'hsmtest'.
> 2003-09-29 10:00:02.07 spid86 Starting up
> database 'hsmtrain'.
> 2003-09-29 10:00:02.40 spid87 Starting up
> database 'hsmtest'.
> 2003-09-29 10:00:02.51 spid87 Starting up
> database 'hsmtrain'.|||Michael:
First, Thank you for your reply. I appreciate the
professional level and degree of your response.
Second, yes the Auto Close was set. I have since removed
the check mark. I have several other databases running on
this server in support of the web based application I
mentioned. These other databases started normally and
remained so after I had restarted the server. If I had
given more thought to situation I could have compared the
properties between the problem databases and those that
functioned as expected. The problem ceased yesterday
morning around 10:00am even with the auto close still
set. I suspect the web application connection succeeded
and maintain its connection thereafter.
Just quickly, should others have interest, I'll respond to
your questions.
1. The web application could not connect from the web
application server. This is a separate server from the
database. I am not exactly sure how the vendor connects
to the database in the code behind the pages. No errors
reported back to Internet Explorer except that the page
could not be found. Basically the weblogic web server
could not start without the connection to the database.
2. I did ping all the servers associated with this
application. I receive successful results on all counts.
3. Yes I was able to connect to the database server using
Query Analyzer and was able to query tables and data from
the affected databases.
4. I have the latest MDAC installation that comes with
SQL SP3a.
Again, I thank you for your help.
Thomas
>--Original Message--
>Hi Thomas,
>Such issues tend to be complex and require extensive
research. I'd like to
>set the right expectations and let you know that it may
take a while for us
>to help you narrow down the problem.
>Please check to see if the "Auto close" is checked in the
Option tab in the
>property dialog of the database. If so, make sure this
option is unchecked
>and try to connect to SQL Server again. Does this resolve
the problem?
>If not, I would like you to provide me with more
information regarding this
>issue so that I can narrow down it.
>Please make sure that the MSSQLServer service, for the
SQL Server you are
>trying to connect to, is started and running before doing
any further
>troubleshooting.
>1. What do you mean by "the web based application is
unable to connect"?
>How do you connect SQL Server? Please describe it in
detail. Do any error
>messages occur?
>2. Ping the server using the server name and IP address.
See if the request
>completes successfully or times out.
>3. Can you connect to SQL Server using Query Analyzer? If
not, does any
>error message occur?
>4. What is the MDAC version on the client machine? For
more information
>regarding how to check the MDAC version, please refer to
the following
>article:
>301202 HOW TO: Check for MDAC Version
>http://support.microsoft.com/?id=301202
>5. Please provide the complete error log, you can send it
at
>v-yshao@.microsoft.com
>I am standing by for your response.
>Regards,
>Michael Shao
>Microsoft Online Partner Support
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>.
>|||Hi Thomas,
Thank you for taking time to write in your experience so that it may help
other customers who may encounter similar issues. As per your previous
post, I am going consider this issue as Resolved. However, should you have
any further questions or concerns, please feel free to send your post here.
I will continue to work with you.
Thanks for using MSDN newsgroup again.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Subscribe to:
Posts (Atom)