Iterate a Variable

Hi Guys,

I need to design this SSIS migration package to migrate data.

In a Execute SQL Task, I need to get a full result set and assign it to an variable, such as v_collection;

The SQL statement can be as simple as : select primary_key from a_table;

After that I have a ForEach Loop container, that consumes the variable, and assign each iteration to another variable, such as v_iter, the type of v_iter is DT_I4, because the primary key is a long integer

The problem is: in Oracle, the primary key is NUMERIC(10,0) and in SQL it is int.

I can not assign a NUMERIC(10,0) to an variable of DT_I4, but if I change the variable definition to DT_NUMERIC, then it would not work for SQL.

Anyone knows how to fix this?

I was thinking to add a Script Task between the Execute SQL Task and the Foreach Loop, and somehow access the collection variable, v_collection, and manually convert the value to a DT_IT, then repopulate another collection variable, v_collection_I4 with Integers, and force the Foreach Loop to use v_collection_I4 collection variable.

Will this work? If yes, how? :)

Thanks a lot!

Wenbiao

Hi Wenbiao,

My general principle with variables in SSIS is to use String datatype where at all possible. So following your example above, I would change your SQL statement along the lines of:

select convert ( varchar, primary_key) as primary_key from a_table;

The variable datatype would then be a String.

Then during your Dataflow within your loop, you can use a Derived Column transformation to perform a Type Cast to whatever datatype you need before you deliver the data.

Good luck.

Mike

|||As an alternative, you could create two variables, the first one DT_NUMERIC, the second DT_I4. Set the first one with the numeric value from Oracle in your For Each Loop. Set the second one to evaluate as an expression, and cast the first variable to DT_I4 in the expression. It will be updated each time the first variable changes, and you can use it in the tasks in the loop.

Iterate a Variable

Hi Guys,

I need to design this SSIS migration package to migrate data.

In a Execute SQL Task, I need to get a full result set and assign it to an variable, such as v_collection;

The SQL statement can be as simple as : select primary_key from a_table;

After that I have a ForEach Loop container, that consumes the variable, and assign each iteration to another variable, such as v_iter, the type of v_iter is DT_I4, because the primary key is a long integer

The problem is: in Oracle, the primary key is NUMERIC(10,0) and in SQL it is int.

I can not assign a NUMERIC(10,0) to an variable of DT_I4, but if I change the variable definition to DT_NUMERIC, then it would not work for SQL.

Anyone knows how to fix this?

I was thinking to add a Script Task between the Execute SQL Task and the Foreach Loop, and somehow access the collection variable, v_collection, and manually convert the value to a DT_IT, then repopulate another collection variable, v_collection_I4 with Integers, and force the Foreach Loop to use v_collection_I4 collection variable.

Will this work? If yes, how? :)

Thanks a lot!

Wenbiao

Hi Wenbiao,

My general principle with variables in SSIS is to use String datatype where at all possible. So following your example above, I would change your SQL statement along the lines of:

select convert ( varchar, primary_key) as primary_key from a_table;

The variable datatype would then be a String.

Then during your Dataflow within your loop, you can use a Derived Column transformation to perform a Type Cast to whatever datatype you need before you deliver the data.

Good luck.

Mike

|||As an alternative, you could create two variables, the first one DT_NUMERIC, the second DT_I4. Set the first one with the numeric value from Oracle in your For Each Loop. Set the second one to evaluate as an expression, and cast the first variable to DT_I4 in the expression. It will be updated each time the first variable changes, and you can use it in the tasks in the loop.

It seems there is no solution for this question?

Hi guys,

I posted this question a couple of weeks ago, however, I got no response at all,. It seems that no body tried this before. Anyhow if anybody did the same thing, here is my issue;

Assume I have a pie chart whose Value: is Internet Sales Amount and Category group: is Product Subcategroy. Suppose I have 5 segments(slices) with the value 200,150,80,50,and 40. Now, here is the question; I want to aggregate those values that are less than 100 (in this case the last three values: 80+50+40 = 170) and put the sum(170) in one segment, so that in the new pie chart we will have only 3 segments with the value 200,250, and 170.

Any idea is appreciated.

There are several other ways of doing this:
- perform the pre-aggregation in the dataset query / stored procedure. If the data source is Analysis Services, you can use e.g. the MDX iif functions to "re-group" certain members within a hierarchy.

- look into RS 2005 CustomReportItem-based third party charting solutions which may offer this functionality.

- wait for a future release of RS. Adding this particular re-group feature as a native concept within RS is under consideration for a future release.

- there is a solution based on using the OLEDB provider to connect to AS. See my posting of 08/23 with a sample report in this thread.

-- Robert

|||

Robert,

The data source is Analysis Services Cube; that makes it a litle bit difficult. Anyhow, I tried all my best to aggregate the values (that are less than 100, for instance) and put them in one segment. What I did is in the value tab I wrote the following expression:

=Iif(Fields!Internet_Sales_Amount.Value < 100,Fields!sumofothers.value,fields!Internet_Sales_Amount.value),

where Fields!sumeofothers.value is sum of those values that are less than 100.

And in the Data Leble I wrote the following expression

=IIF(fields!Internet_Sales_Amount.Value <100,"Others",Fields!Subcategory.Value).

So, here what I want is to group those values that are less than 100 and aggregate them in one segment called "Others", however, I am getting multiple "others" segments.

Can you please give me some comment on the above expressions or an example of expression which perform this functionality?

The other thing is that where shall we expect the next release of reporting service?

--Amde

|||

Let's take a look at the following MDX query where you perform essentially "re-grouping" of data, such as the following query based on the Adventure Works sample cube:

WITH
SET [ProductLineTop5ModelName] AS 'Generate([Product].[Product Model Lines].Children, {{TOPCOUNT([Product].[Product Model Lines].Children, 10, [Measures].[Order Count]) as TC}, VisualTotals({[Product].[Product Model Lines].CurrentMember, [Product].[Product Model Lines].Children - TC},
"* - Other") - [Product].[Product Model Lines].Children, [Product].[Product Model Lines].CurrentMember})'

SELECT {[Measures].[Order Count]} ON COLUMNS,
[ProductLineTop5ModelName] ON ROWS FROM [Adventure Works]

The "Other" group is represented as a subtotal aggregate row in the flattened rowset from the AS server. The only way to retrieve this aggregate row when using the AS data extension in RS 2005 RTM/SP1 is to use the RS server aggregate function =Aggregate(...) in the correct scope in the report - otherwise the row is ignored since it is marked as an AS server aggregate row. However, in that particular case the "re-grouped" data actually is not the subtotal of the other groups. It is rather "detail data".

I attached a report at the bottom of this posting which implements two options side by side for the MDX query above:

1. the first dataset / first table in the report is based on using the data source type "Analysis Services" and it will require a new feature under consideration to be added in the next service pack of RS 2005. Without that new feature, the "VisualTotal" rows will not show up in the first table because the server aggregate function is not used. With the new feature however they will show up in the first table because the report does not explicitly use the RS server aggregate row to actually retrieve the aggregate rows as a subtotals - and they will therefore be automatically recategorized as "detail data".

2. the second dataset / second table in the report uses the data source type "OLEDB" and connects through the OleDB for AS9.0 provider - this is available in RS 2005 today. The fact that the OLEDB provider does not understand anything about hierarchies, measures, extended properties, server aggregate rows, etc. is actually a benefit in this case: the "VisualTotal" shows up as detail row in the table - as desired.

You should be able to apply the same concept to your particular situation with the chart. The important part is that for now you will need to use the OLEDB provider to connect to AS and execute the queries.

-- Robert

==================================================================================

<?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="RoseAS">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=roseas;Initial Catalog="Adventure Works DW"</ConnectString>
<DataProvider>OLEDB-MD</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>2853688f-8806-4d0a-ae38-2a865ce38226</rd:DataSourceID>
</DataSource>
<DataSource Name="DataSource1">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Provider=MSOLAP.3;Data Source=roseas;Initial Catalog="Adventure Works DW"</ConnectString>
<DataProvider>OLEDB</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>90e8d194-dfcc-42a3-826e-042e456854e3</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table2">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<rd:DefaultName>textbox15</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<rd:DefaultName>textbox16</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<rd:DefaultName>textbox17</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
<Left>0.25in</Left>
<DataSetName>DataSet_OleDB</DataSetName>
<Top>1.875in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Product_Product_Model_Lines_Product_Line">
<rd:DefaultName>Product_Product_Model_Lines_Product_Line</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightSkyBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Product_Product_Model_Lines_Product_Line.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<rd:DefaultName>textbox19</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightSkyBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<rd:DefaultName>textbox20</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightSkyBlue</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table2_Group1">
<GroupExpressions>
<GroupExpression>=Fields!Product_Product_Model_Lines_Product_Line.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<ZIndex>1</ZIndex>
<Width>4.625in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<rd:DefaultName>textbox12</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Product_Product_Model_Lines_Model">
<rd:DefaultName>Product_Product_Model_Lines_Model</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=iif(Fields!Product_Product_Model_Lines_Model.Value is Nothing, Fields!Product_Product_Model_Lines_Product_Line.Value &amp; " - Other", Fields!Product_Product_Model_Lines_Model.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Measures_Order_Count">
<rd:DefaultName>Measures_Order_Count</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Measures_Order_Count.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Product Line</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Model</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Order Count</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>1.5in</Width>
</TableColumn>
<TableColumn>
<Width>2.25in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
</TableColumns>
<Height>1in</Height>
</Table>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Footer>
<Left>0.25in</Left>
<DataSetName>DataSet_ADOMD</DataSetName>
<Top>0.25in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Product_Line">
<rd:DefaultName>Product_Line</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightGrey</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Product_Line.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightGrey</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Order_Count_1">
<rd:DefaultName>Order_Count_1</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BackgroundColor>LightGrey</BackgroundColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Order_Count.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!Product_Line.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Width>4.625in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Model">
<rd:DefaultName>Model</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=iif(Fields!Model.Value is Nothing, Fields!Product_Line.Value &amp; " - Other", Fields!Model.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Order_Count">
<rd:DefaultName>Order_Count</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Order_Count.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>
<ZIndex>11</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Product Line</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Model</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Order Count</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>1.5in</Width>
</TableColumn>
<TableColumn>
<Width>2.25in</Width>
</TableColumn>
<TableColumn>
<Width>0.875in</Width>
</TableColumn>
</TableColumns>
<Height>1in</Height>
</Table>
</ReportItems>
<Height>3in</Height>
</Body>
<rd:ReportID>2158ae1a-bacd-437c-ace7-4413c1c708e8</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet_ADOMD">
<Query>
<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
<rd:DesignerState><QueryDefinition xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification xsi:type="MDXQuerySpecification"><Select><Items><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Model Lines</HierarchyName><HierarchyUniqueName>[Product].[Product Model Lines]</HierarchyUniqueName><LevelName>Product Line</LevelName><UniqueName>[Product].[Product Model Lines].[Product Line]</UniqueName></ID><ItemCaption>Product Line</ItemCaption></Item><Item><ID xsi:type="Level"><DimensionName>Product</DimensionName><HierarchyName>Product Model Lines</HierarchyName><HierarchyUniqueName>[Product].[Product Model Lines]</HierarchyUniqueName><LevelName>Model</LevelName><UniqueName>[Product].[Product Model Lines].[Model]</UniqueName></ID><ItemCaption>Model</ItemCaption></Item><Item><ID xsi:type="Measure"><MeasureName>Order Count</MeasureName><UniqueName>[Measures].[Order Count]</UniqueName></ID><ItemCaption>Order Count</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>Adventure Works</From><Filter><FilterItems /></Filter><Calculations /><Aggregates /><QueryProperties /></QuerySpecification><Query><Statement>WITH
SET [ProductLineTop5ModelName] AS
'Generate([Product].[Product Model Lines].Children, {{TOPCOUNT([Product].[Product Model Lines].Children, 10, [Measures].[Order Count]) as TC}, VisualTotals({[Product].[Product Model Lines].CurrentMember, [Product].[Product Model Lines].Children - TC},
"* - Other") - [Product].[Product Model Lines].Children, [Product].[Product Model Lines].CurrentMember})'

SELECT {[Measures].[Order Count]} ON COLUMNS,
[ProductLineTop5ModelName] ON ROWS
FROM [Adventure Works]

</Statement><ParameterDefinitions /></Query></QueryDefinition></rd:DesignerState>
<CommandText>WITH
SET [ProductLineTop5ModelName] AS
'Generate([Product].[Product Model Lines].Children, {{TOPCOUNT([Product].[Product Model Lines].Children, 10, [Measures].[Order Count]) as TC}, VisualTotals({[Product].[Product Model Lines].CurrentMember, [Product].[Product Model Lines].Children - TC},
"* - Other") - [Product].[Product Model Lines].Children, [Product].[Product Model Lines].CurrentMember})'

SELECT {[Measures].[Order Count]} ON COLUMNS,
[ProductLineTop5ModelName] ON ROWS
FROM [Adventure Works]

</CommandText>
<DataSourceName>RoseAS</DataSourceName>
</Query>
<Fields>
<Field Name="Product_Line">
<rd:TypeName>System.String</rd:TypeName>
<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Product].[Product Model Lines].[Product Line]" /&gt;</DataField>
</Field>
<Field Name="Model">
<rd:TypeName>System.String</rd:TypeName>
<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level" UniqueName="[Product].[Product Model Lines].[Model]" /&gt;</DataField>
</Field>
<Field Name="Order_Count">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>&lt;?xml version="1.0" encoding="utf-8"?&gt;&lt;Field xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure" UniqueName="[Measures].[Order Count]" /&gt;</DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="DataSet_OleDB">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>WITH
SET [ProductLineTop5ModelName] AS

'Generate([Product].[Product Model Lines].Children, {{TOPCOUNT([Product].[Product Model Lines].Children, 10, [Measures].[Order Count]) as TC}, VisualTotals({[Product].[Product Model Lines].CurrentMember, [Product].[Product Model Lines].Children - TC},
"* - Other") - [Product].[Product Model Lines].Children, [Product].[Product Model Lines].CurrentMember})'

SELECT {[Measures].[Order Count]} ON COLUMNS,
[ProductLineTop5ModelName] ON ROWS
FROM [Adventure Works]</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
<Fields>
<Field Name="Product_Product_Model_Lines_Product_Line">
<rd:TypeName>System.String</rd:TypeName>
<DataField>[Product].[Product Model Lines].[Product Line].[MEMBER_CAPTION]</DataField>
</Field>
<Field Name="Product_Product_Model_Lines_Model">
<rd:TypeName>System.String</rd:TypeName>
<DataField>[Product].[Product Model Lines].[Model].[MEMBER_CAPTION]</DataField>
</Field>
<Field Name="Measures_Order_Count">
<rd:TypeName>System.Object</rd:TypeName>
<DataField>[Measures].[Order Count]</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Author>Robert M. Bruckner, Microsoft</Author>
<Width>5.54167in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

it helps, but still have some questions

thank you, guys. Your reply really help me. Now, basicly, I'm clear about
these technical terms. But not sure the following questions:
1. How do I know whether my sql server is in Simple Mode or not?
2. Does the full database backup include the last lsn? I try to understand
how the system works. when I restore data using the full database backup
with a trans log backup, the system will firstly restore the data in the
full backup, next, it will take out the transactions from the trans log
backup that time is later than the last lsn, and do all these trans so that
all data can be restored.
3. My database is not big, I think it will under 2GB. I got a plan, but
don't know is't a good one. I will do a full backup 1 time per month, and
1 time differential database backup per day, and then 1 time trans backup
per hour. Is it suitable for me?
oh, I really sorry for my poor English, even me has been confused by my
words, but I've try my best. Hope you will know what I say. Here some recommendations:
1) Right-click your database and select Properties, then on Options look for
Recovery model.
2) For a database under 2GB, I would definitelly do a daily full backup,
perhaps at night, or when the number of transactions is low or minimum. If
this works fine for you, then you do not need a differential backup, only
transaction log backups in addition to the daily full backup.
Ben Nevarez, MCDBA, OCP
"mizi" <haha@.haha.com> wrote in message
news:Xns9718C5C08B547hahahahacom@.207.46.248.16...
> thank you, guys. Your reply really help me. Now, basicly, I'm clear about
> these technical terms. But not sure the following questions:
> 1. How do I know whether my sql server is in Simple Mode or not?
> 2. Does the full database backup include the last lsn? I try to understand
> how the system works. when I restore data using the full database backup
> with a trans log backup, the system will firstly restore the data in the
> full backup, next, it will take out the transactions from the trans log
> backup that time is later than the last lsn, and do all these trans so
> that
> all data can be restored.
> 3. My database is not big, I think it will under 2GB. I got a plan, but
> don't know is't a good one. I will do a full backup 1 time per month, and
> 1 time differential database backup per day, and then 1 time trans backup
> per hour. Is it suitable for me?
> oh, I really sorry for my poor English, even me has been confused by my
> words, but I've try my best. Hope you will know what I say.

it helps, but still have some questions

thank you, guys. Your reply really help me. Now, basicly, I'm clear about
these technical terms. But not sure the following questions:
1. How do I know whether my sql server is in Simple Mode or not?
2. Does the full database backup include the last lsn? I try to understand
how the system works. when I restore data using the full database backup
with a trans log backup, the system will firstly restore the data in the
full backup, next, it will take out the transactions from the trans log
backup that time is later than the last lsn, and do all these trans so that
all data can be restored.
3. My database is not big, I think it will under 2GB. I got a plan, but
don't know is't a good one. I will do a full backup 1 time per month, and
1 time differential database backup per day, and then 1 time trans backup
per hour. Is it suitable for me?
oh, I really sorry for my poor English, even me has been confused by my
words, but I've try my best. Hope you will know what I say.
Here some recommendations:
1) Right-click your database and select Properties, then on Options look for
Recovery model.
2) For a database under 2GB, I would definitelly do a daily full backup,
perhaps at night, or when the number of transactions is low or minimum. If
this works fine for you, then you do not need a differential backup, only
transaction log backups in addition to the daily full backup.
Ben Nevarez, MCDBA, OCP
"mizi" <haha@.haha.com> wrote in message
news:Xns9718C5C08B547hahahahacom@.207.46.248.16...
> thank you, guys. Your reply really help me. Now, basicly, I'm clear about
> these technical terms. But not sure the following questions:
> 1. How do I know whether my sql server is in Simple Mode or not?
> 2. Does the full database backup include the last lsn? I try to understand
> how the system works. when I restore data using the full database backup
> with a trans log backup, the system will firstly restore the data in the
> full backup, next, it will take out the transactions from the trans log
> backup that time is later than the last lsn, and do all these trans so
> that
> all data can be restored.
> 3. My database is not big, I think it will under 2GB. I got a plan, but
> don't know is't a good one. I will do a full backup 1 time per month, and
> 1 time differential database backup per day, and then 1 time trans backup
> per hour. Is it suitable for me?
> oh, I really sorry for my poor English, even me has been confused by my
> words, but I've try my best. Hope you will know what I say.
sql

it helps, but still have some questions

thank you, guys. Your reply really help me. Now, basicly, I'm clear about
these technical terms. But not sure the following questions:
1. How do I know whether my sql server is in Simple Mode or not?
2. Does the full database backup include the last lsn? I try to understand
how the system works. when I restore data using the full database backup
with a trans log backup, the system will firstly restore the data in the
full backup, next, it will take out the transactions from the trans log
backup that time is later than the last lsn, and do all these trans so that
all data can be restored.
3. My database is not big, I think it will under 2GB. I got a plan, but
don't know is't a good one. I will do a full backup 1 time per month, and
1 time differential database backup per day, and then 1 time trans backup
per hour. Is it suitable for me?
oh, I really sorry for my poor English, even me has been confused by my
words, but I've try my best. Hope you will know what I say. :)Here some recommendations:
1) Right-click your database and select Properties, then on Options look for
Recovery model.
2) For a database under 2GB, I would definitelly do a daily full backup,
perhaps at night, or when the number of transactions is low or minimum. If
this works fine for you, then you do not need a differential backup, only
transaction log backups in addition to the daily full backup.
Ben Nevarez, MCDBA, OCP
"mizi" <haha@.haha.com> wrote in message
news:Xns9718C5C08B547hahahahacom@.207.46.248.16...
> thank you, guys. Your reply really help me. Now, basicly, I'm clear about
> these technical terms. But not sure the following questions:
> 1. How do I know whether my sql server is in Simple Mode or not?
> 2. Does the full database backup include the last lsn? I try to understand
> how the system works. when I restore data using the full database backup
> with a trans log backup, the system will firstly restore the data in the
> full backup, next, it will take out the transactions from the trans log
> backup that time is later than the last lsn, and do all these trans so
> that
> all data can be restored.
> 3. My database is not big, I think it will under 2GB. I got a plan, but
> don't know is't a good one. I will do a full backup 1 time per month, and
> 1 time differential database backup per day, and then 1 time trans backup
> per hour. Is it suitable for me?
> oh, I really sorry for my poor English, even me has been confused by my
> words, but I've try my best. Hope you will know what I say. :)

Issues with database.dbo.tablename usage

Hello,

Can you guys think of all the possible reason why you wouldn't want 2 databases to cross reference each other using -

database.dbo.tablename

I can see one reason...Developers are going to go gagas using database.dbo.tablename for cross database updates/inserts etc. This will mean that the databases cannot scale anymore. What else?Hi,

because you would have to create the user which access the object not only in one database but also in the other one.

<Ignore this part, because this is a bad practice>
You could enable cross database ownership chain and use a view/procedure from database A to Select the data from Database B, opening up a security hole to database B, because the security is only checked once on Database A if the two owners of the objects are both the same.
<Ignore this part, because this is a bad practice>

Thats the hardest part for me, maintaing the user in two databases (without enabling cross database ownership chain)

HTH, Jens K. Suessmeyer.

-
http://www.sqlserver2005.de
-|||

Security issues, additional keystrokes required to use four part names, etc.

I can also think of many equally valid reasons to separate data between different databases. Security issues, Backup/Restore Issues, etc.

Saving Developers 'keystrokes' is a trivial concern and should be ignored. Make the decision based upon 'real' organizational needs and requirements. With drag and drop, cut and paste, etc., too many keystrokes is totally bogus!

sql

Issue with a remote report server

Hey guys,

I have three servers, assume ServerA,ServerB, and ServerC. ServerA is where the database(datasource) is located. ServerB is used as a report server and Server C is my local machine where I design reports. So obviously I deployed the reports from my local machine(server C) to the remote report server(server B). Deployement was successful but I got the following error when I run the report from the report server. You might think that the report is not pointing to the right data source, I checked that several times and it is pointing to the right data source.

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'dsCallB'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

However, I also observed that when I deployed the same reports on the local machine report server(server C), it run with out any problem. So it seems that the problem is related with the remote server(server B) not the data source.

Does anybody has a solution for this.

Thx

Normally it is one of two things when I encounter this message:

1) The report is not connected to the datasource on ServerB (as you mentioned).

2) The DataSource Connection String on ServerB has a misspelling somewhere. (If ServerA is Case Sensitive Collation, check upper/lower case).

If not those, make sure ServerB can connect to the database on ServerA through some other means (ODBC, etc). Good luck.

|||

Well, the quick and easy solution is to change the configuration for the connection already deployed to ServerB. Since it's working from your local PC/ServerC, so just use your ID and password to config the connection on ServerB. But if ServerB is really a production server, it's better to create a separate logon, either a Windows account or a SQL account with the access to ServerA. Make sure to set the password to never expire. Then use this account to config the connection on ServerB. HTH.

|||I don't think I need to config the connection on ServerB, because, there are other several reports deployed and run with out any problem. FYI the existing reports was actually deployed by some body else. This problem happened for the new reports that I developed. It is very weired situation.|||

The problem has to be with the connection from ServerB to the data source, otherwise you wouldn't be able to run it from your local machine.

Are you using a shared data source?

Go to the properties of the report in Report Manager, and select 'Data Sources' from the left. Select 'A custom data source' and verify Microsoft SQL Server is listed in your 'Connection type'. The 'Connection string' should look like this: Data Source=ServerA;Initial Catalog=DB_Name. Select 'Credentials stored securely in the report server' and type your username (domain\user) and your password. Then check the box 'Use as Windows credentials when connecting to the data source' and hit Apply at the bottom.

If you do this from ServerB (RS Server), then you should get an informative error message if/when you get the error. Do you have access to log directly into ServerB?

Jarret

|||

Hey Jarret,

First, I really appreciate your follow up to assist me in solving this issue.

Actually, I am using a shared data source to generate the reports. As a matter of fact, the existing reports also uses a shared data source and run with out any problem. I also tried what you said up here, however, I got the same error message. Anyhow, I just wanna cross check one thing with you. If I select A custom data source, which server's username and password do I have to apply, ServerA(datasource), ServerB(report server), or ServerC(Local machine)?

Thx.

|||

I really don't mind. Actually, I'm sorry that I haven't been able to get you a solution yet.

If you select 'A custom data source', then the connection string will have to point to your database server (ServerA) and you'll have to supply either a SQL account or a windows account (domain\user) that has permission to read the data.

If this still doesn't work, I would start a new report without using the shared data source and just store the connection information into the report itself.

Jarret

|||

The problem maybe because the shared datasource you are using may be in a different folder on your report manager whereas you would have specified a different targetdatasourcefolder (Report project properties) for your report. So, it is trying to create that data source at runtime and not able to do it because you dont have enough permissions.

Shyam

|||

I'm very disappointed with this annoying error message. I experienced the same kind of problem in some other project and I was able to solve it with no time. One thing which makes me angry is that the existing shared data sources are working fine, however the data source that I created doesn't work. I wasted a lot of time on this. I don't know what to do next. May be go for a vacation LOL.

Anyhow I will let you know if I am able to find something new. Please keep on posting me if you have any idea.

Thx.

|||

Have you read my post and any progress?

Shyam

|||

Hi Shyam,

I checked that too. On my project properties, the TargetDataSourceFolder is 'Data Sources'. On the report manager, the data sources are created in the same folder name, i.e. 'Data Sources'. I couldn't see any problem.

I even created a new data source (which is not a shared data source) on the report server level and pointed it to my report, but still getting the same error message

|||

Maybe the following links will be of some help to you:

http://msdn2.microsoft.com/en-us/library/ms365166.aspx

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=288600&SiteID=17

Shyam