Showing posts with label multiple. Show all posts
Showing posts with label multiple. Show all posts

Wednesday, March 28, 2012

How to show two dataset with equal & non equal of multiple selection.

Dear Friends,

In my report, I am having Listbox for users to choose Country, City & Company.
The user can choose Country. Based on the country selection, cities will be listed out.
Based on the city selection, Companies will be listed out.
They can choose companies.

Now, I have to show two set of results.

A. List of Companies as per selection ( dataset with equal to selection )

B. List of Companies which are not selected ( ie dataset with not equal to selection )

I have created a dataset with all companies and filter it by selection. When I tried with the filter option in the Dataset, I am able to check for only one value and not for multiple value. If the selection is one company, then I can filter it. But if they choose 5 companies, I am not not able to filter it. Is there any other option I can try out.


Please advice. Thanks.

warm regards
Rakin
Singapore.

It sounds like you are using RS 2005, since you have multi value report parameters.

On the filter tab, you can set the filter operator to "IN" (this is the last entry in the list of filter operators). Assuming that your Company report parameter is marked as "multi-value", you can then use the following filter settings:

Filter expression: =Fields!CompanyName.Value
Filter operator: IN
Filter value: =Parameters!Company.Value

The IN-filter can automatically deal with multi-value parameters (which represent an array of values).

-- Robert

|||

Thanks Robert.

You are right. I am using RS 2005.

Now, I am able to get the value for the first option. ( with IN )

But I could n't find any NOT IN option in the filter for the second set of results ?

Could you please advice for this ?

Regards
Rakin

|||

Rakin, a "not in" filter is currently not natively supported in Reporting Services. You would need to look into ways of achieving this directly in the query by using the NOT IN clause:

E.g. select * from products where ReorderLevel not IN (@.Level)

I also attached a small sample report at the bottom based on the Northwind database.

-- 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="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>9904b9f4-d569-4718-a75a-b7fb657657fd</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Level">
<DataType>Integer</DataType>
<Prompt>Level</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>0</Value>
</ParameterValue>
<ParameterValue>
<Value>5</Value>
</ParameterValue>
<ParameterValue>
<Value>10</Value>
</ParameterValue>
<ParameterValue>
<Value>15</Value>
</ParameterValue>
<ParameterValue>
<Value>20</Value>
</ParameterValue>
<ParameterValue>
<Value>25</Value>
</ParameterValue>
<ParameterValue>
<Value>30</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
<MultiValue>true</MultiValue>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<Width>1in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="ReorderLevel">
<rd:DefaultName>ReorderLevel</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ReorderLevel.Value</Value>
</Textbox>
</ReportItems>
<Sorting>
<SortBy>
<SortExpression>=Fields!ReorderLevel.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="matrix1_ReorderLevel">
<GroupExpressions>
<GroupExpression>=Fields!ReorderLevel.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryID.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_CategoryID">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Top>0.25in</Top>
<Width>2in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</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>
</Corner>
<Height>0.5in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ProductID">
<rd:DefaultName>ProductID</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!ProductID.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>1.25in</Height>
</Body>
<rd:ReportID>ed5d6416-a54d-4c50-8568-8f5bae4484cf</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from products where ReorderLevel not IN (@.Level)</CommandText>
<QueryParameters>
<QueryParameter Name="@.Level">
<Value>=Parameters!Level.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ProductID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="SupplierID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SupplierID</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="QuantityPerUnit">
<rd:TypeName>System.String</rd:TypeName>
<DataField>QuantityPerUnit</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="UnitsInStock">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsInStock</DataField>
</Field>
<Field Name="UnitsOnOrder">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsOnOrder</DataField>
</Field>
<Field Name="ReorderLevel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ReorderLevel</DataField>
</Field>
<Field Name="Discontinued">
<rd:TypeName>System.Boolean</rd:TypeName>
<DataField>Discontinued</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>2.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||

In RS 2005, I have a report with 5 report parameters. Two of those parameters are query based in order to populate drop down lists in the report for the user.

The BUG occurs when the report initialises: the first query based parameter is populated but the second query-based parameter is blank. I select the available values in both parameters and click on the "View Report" button but the same BUG occurs: the first parameter is populated but the second parameter's values are reset. If I select values from both and click the View Report button a few times it will eventually display the report.

Have any of you encountered anything like this?

Niall

Posted by inlandtyuf at 2:18 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , , ,

How to show two dataset with equal & non equal of multiple selection.

Dear Friends,

In my report, I am having Listbox for users to choose Country, City & Company.
The user can choose Country. Based on the country selection, cities will be listed out.
Based on the city selection, Companies will be listed out.
They can choose companies.

Now, I have to show two set of results.

A. List of Companies as per selection ( dataset with equal to selection )

B. List of Companies which are not selected ( ie dataset with not equal to selection )

I have created a dataset with all companies and filter it by selection. When I tried with the filter option in the Dataset, I am able to check for only one value and not for multiple value. If the selection is one company, then I can filter it. But if they choose 5 companies, I am not not able to filter it. Is there any other option I can try out.


Please advice. Thanks.

warm regards
Rakin
Singapore.

It sounds like you are using RS 2005, since you have multi value report parameters.

On the filter tab, you can set the filter operator to "IN" (this is the last entry in the list of filter operators). Assuming that your Company report parameter is marked as "multi-value", you can then use the following filter settings:

Filter expression: =Fields!CompanyName.Value
Filter operator: IN
Filter value: =Parameters!Company.Value

The IN-filter can automatically deal with multi-value parameters (which represent an array of values).

-- Robert

|||

Thanks Robert.

You are right. I am using RS 2005.

Now, I am able to get the value for the first option. ( with IN )

But I could n't find any NOT IN option in the filter for the second set of results ?

Could you please advice for this ?

Regards
Rakin

|||

Rakin, a "not in" filter is currently not natively supported in Reporting Services. You would need to look into ways of achieving this directly in the query by using the NOT IN clause:

E.g. select * from products where ReorderLevel not IN (@.Level)

I also attached a small sample report at the bottom based on the Northwind database.

-- 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="Northwind">
<DataSourceReference>Northwind</DataSourceReference>
<rd:DataSourceID>9904b9f4-d569-4718-a75a-b7fb657657fd</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Level">
<DataType>Integer</DataType>
<Prompt>Level</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>0</Value>
</ParameterValue>
<ParameterValue>
<Value>5</Value>
</ParameterValue>
<ParameterValue>
<Value>10</Value>
</ParameterValue>
<ParameterValue>
<Value>15</Value>
</ParameterValue>
<ParameterValue>
<Value>20</Value>
</ParameterValue>
<ParameterValue>
<Value>25</Value>
</ParameterValue>
<ParameterValue>
<Value>30</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
<MultiValue>true</MultiValue>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<Width>1in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="ReorderLevel">
<rd:DefaultName>ReorderLevel</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ReorderLevel.Value</Value>
</Textbox>
</ReportItems>
<Sorting>
<SortBy>
<SortExpression>=Fields!ReorderLevel.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="matrix1_ReorderLevel">
<GroupExpressions>
<GroupExpression>=Fields!ReorderLevel.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryID.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_CategoryID">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Top>0.25in</Top>
<Width>2in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</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>
</Corner>
<Height>0.5in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ProductID">
<rd:DefaultName>ProductID</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!ProductID.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>1.25in</Height>
</Body>
<rd:ReportID>ed5d6416-a54d-4c50-8568-8f5bae4484cf</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from products where ReorderLevel not IN (@.Level)</CommandText>
<QueryParameters>
<QueryParameter Name="@.Level">
<Value>=Parameters!Level.Value</Value>
</QueryParameter>
</QueryParameters>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ProductID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="SupplierID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SupplierID</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="QuantityPerUnit">
<rd:TypeName>System.String</rd:TypeName>
<DataField>QuantityPerUnit</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="UnitsInStock">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsInStock</DataField>
</Field>
<Field Name="UnitsOnOrder">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsOnOrder</DataField>
</Field>
<Field Name="ReorderLevel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ReorderLevel</DataField>
</Field>
<Field Name="Discontinued">
<rd:TypeName>System.Boolean</rd:TypeName>
<DataField>Discontinued</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>2.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>

|||

In RS 2005, I have a report with 5 report parameters. Two of those parameters are query based in order to populate drop down lists in the report for the user.

The BUG occurs when the report initialises: the first query based parameter is populated but the second query-based parameter is blank. I select the available values in both parameters and click on the "View Report" button but the same BUG occurs: the first parameter is populated but the second parameter's values are reset. If I select values from both and click the View Report button a few times it will eventually display the report.

Have any of you encountered anything like this?

Niall

sql
Posted by inlandtyuf at 2:18 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , , ,

How to show multiple main reports with their subreports one set by

Hi,
I have made a SQL Reporting Services report with the subreport. I need
my report to show multiple records with their subreport at the same time.
At now, I can generate multiple main reports altogether and then they are
followed by the subreport with multiple subreport details. It's just like
the following:
1st Page: 1st record in the Main report
2nd Page: 2nd record in the Main report
3rd Page: 1st Subreport detail (use Table object)
2nd Subreport detail (use Table object)
Inside the table of the 3rd page, it shows the 1st
Subreport details and then 2nd Subreport details.
What I actually need is I want my report to show one record in the main
report and then followed by the corresponding subreport details, then next
page for the next record in the main report and then followed by the
corresponding subreport details.
1st Page: 1st record in the Main report
2nd Page: 1st Subreport detail
3rd Page: 2nd record in the Main report
4th Page: 2nd Subreport detail
Is there anyone who can help me to solve this problem ?
Thanks in advance.
RogerHave you tried putting your subreport inside a table cell? The table could
contain your main rows and your subreports inside the same table as
repeating elements.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Roger" <Roger@.discussions.microsoft.com> wrote in message
news:17AC53EB-D966-476E-8B86-30B0864DE28B@.microsoft.com...
> Hi,
> I have made a SQL Reporting Services report with the subreport. I
> need
> my report to show multiple records with their subreport at the same time.
> At now, I can generate multiple main reports altogether and then they are
> followed by the subreport with multiple subreport details. It's just
> like
> the following:
> 1st Page: 1st record in the Main report
> 2nd Page: 2nd record in the Main report
> 3rd Page: 1st Subreport detail (use Table object)
> 2nd Subreport detail (use Table object)
> Inside the table of the 3rd page, it shows the 1st
> Subreport details and then 2nd Subreport details.
> What I actually need is I want my report to show one record in the
> main
> report and then followed by the corresponding subreport details, then next
> page for the next record in the main report and then followed by the
> corresponding subreport details.
> 1st Page: 1st record in the Main report
> 2nd Page: 1st Subreport detail
> 3rd Page: 2nd record in the Main report
> 4th Page: 2nd Subreport detail
> Is there anyone who can help me to solve this problem ?
> Thanks in advance.
> Roger
>
Posted by inlandtyuf at 2:04 AM 0 comments
Labels: , , , , , , , , , , , , , ,

how to show data from multiple tables in one report

Hi,
How to show data from multiple tables which are linked in one report. I will
elaborate the problem little bit, please bear with me -
I have a db which contains three tables - ServerInfo, ServiceInfo, TaskInfo
The relation among them is
One server can have multiple services
One server can have multiple task
There is no relation betwen task and services. There could be 'n' services
and 'm' tasks running on the same server.
Moreover, there could be n servers in the system.
I want to create a all server health reports which displays status of the
services and tasks per server. Each server info comes on a separate page.
I tried to do this using data region. But the problem is data region are
bound to one dataset, which doesnt work in my case.
Please help me out in creating this reportRead up on subreports. What you want is perfect to solve this.
A subreport is just a regular report with a parameter that you drag and drop
onto the main report. Then do a right mouse click on the subreport and map
the parameter to the appropriate field. In your case you will have two
subreport. First develop and test the report individually.
When you deploy the reports you can go to report manager, properties for the
subreport and hide them in list view so that your users don't see
unnecessary reports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
news:CB5A3CB1-48D8-4844-A71B-ACBC2C0E261B@.microsoft.com...
> Hi,
> How to show data from multiple tables which are linked in one report. I
> will
> elaborate the problem little bit, please bear with me -
> I have a db which contains three tables - ServerInfo, ServiceInfo,
> TaskInfo
> The relation among them is
> One server can have multiple services
> One server can have multiple task
> There is no relation betwen task and services. There could be 'n' services
> and 'm' tasks running on the same server.
> Moreover, there could be n servers in the system.
> I want to create a all server health reports which displays status of the
> services and tasks per server. Each server info comes on a separate page.
> I tried to do this using data region. But the problem is data region are
> bound to one dataset, which doesnt work in my case.
> Please help me out in creating this report|||Hi Bruce:
I have same problem with this case!
I try to use subreport to show (for this example, Server Info (1), Service
Info (n) and Tasks (m). It looks very good so far. However when I try to
export it to Excel. All subreport contents cannot be shown. (note that,
preview and export to pdf format is normal)!
please help!
Tony
"Bruce L-C [MVP]" wrote:
> Read up on subreports. What you want is perfect to solve this.
> A subreport is just a regular report with a parameter that you drag and drop
> onto the main report. Then do a right mouse click on the subreport and map
> the parameter to the appropriate field. In your case you will have two
> subreport. First develop and test the report individually.
> When you deploy the reports you can go to report manager, properties for the
> subreport and hide them in list view so that your users don't see
> unnecessary reports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
> news:CB5A3CB1-48D8-4844-A71B-ACBC2C0E261B@.microsoft.com...
> > Hi,
> >
> > How to show data from multiple tables which are linked in one report. I
> > will
> > elaborate the problem little bit, please bear with me -
> >
> > I have a db which contains three tables - ServerInfo, ServiceInfo,
> > TaskInfo
> > The relation among them is
> > One server can have multiple services
> > One server can have multiple task
> > There is no relation betwen task and services. There could be 'n' services
> > and 'm' tasks running on the same server.
> > Moreover, there could be n servers in the system.
> >
> > I want to create a all server health reports which displays status of the
> > services and tasks per server. Each server info comes on a separate page.
> >
> > I tried to do this using data region. But the problem is data region are
> > bound to one dataset, which doesnt work in my case.
> >
> > Please help me out in creating this report
>
>|||Thanks Bruce !!!
I want to show information about all servers in one report. So this report
does not take any parameter. Moreover I want to group related servers info
per page.
Can I do this using subreports?
What I understood is I will create one master report which will have 'n'
subreports in it. Each subreport will show information about one server.
If above understanding is correct. I have one more question
How the subreport will know the server name for which information is to be
retrieved.
regards,
Sachin.
"Bruce L-C [MVP]" wrote:
> Read up on subreports. What you want is perfect to solve this.
> A subreport is just a regular report with a parameter that you drag and drop
> onto the main report. Then do a right mouse click on the subreport and map
> the parameter to the appropriate field. In your case you will have two
> subreport. First develop and test the report individually.
> When you deploy the reports you can go to report manager, properties for the
> subreport and hide them in list view so that your users don't see
> unnecessary reports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
> news:CB5A3CB1-48D8-4844-A71B-ACBC2C0E261B@.microsoft.com...
> > Hi,
> >
> > How to show data from multiple tables which are linked in one report. I
> > will
> > elaborate the problem little bit, please bear with me -
> >
> > I have a db which contains three tables - ServerInfo, ServiceInfo,
> > TaskInfo
> > The relation among them is
> > One server can have multiple services
> > One server can have multiple task
> > There is no relation betwen task and services. There could be 'n' services
> > and 'm' tasks running on the same server.
> > Moreover, there could be n servers in the system.
> >
> > I want to create a all server health reports which displays status of the
> > services and tasks per server. Each server info comes on a separate page.
> >
> > I tried to do this using data region. But the problem is data region are
> > bound to one dataset, which doesnt work in my case.
> >
> > Please help me out in creating this report
>
>|||Hi Sachin Laddha,
Subreport is work but I get the following problem:
1, When export to Excel, get error for the subreport
2, When export to pdf, I get the layout problem. If you interest in this
case, pls read the question I ask "Bruce". The question is "Question want to
ask Bruce L-C for report layout!".
Tony
"Sachin Laddha" wrote:
> Thanks Bruce !!!
> I want to show information about all servers in one report. So this report
> does not take any parameter. Moreover I want to group related servers info
> per page.
> Can I do this using subreports?
> What I understood is I will create one master report which will have 'n'
> subreports in it. Each subreport will show information about one server.
> If above understanding is correct. I have one more question
> How the subreport will know the server name for which information is to be
> retrieved.
> regards,
> Sachin.
> "Bruce L-C [MVP]" wrote:
> > Read up on subreports. What you want is perfect to solve this.
> >
> > A subreport is just a regular report with a parameter that you drag and drop
> > onto the main report. Then do a right mouse click on the subreport and map
> > the parameter to the appropriate field. In your case you will have two
> > subreport. First develop and test the report individually.
> >
> > When you deploy the reports you can go to report manager, properties for the
> > subreport and hide them in list view so that your users don't see
> > unnecessary reports.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Sachin Laddha" <SachinLaddha@.discussions.microsoft.com> wrote in message
> > news:CB5A3CB1-48D8-4844-A71B-ACBC2C0E261B@.microsoft.com...
> > > Hi,
> > >
> > > How to show data from multiple tables which are linked in one report. I
> > > will
> > > elaborate the problem little bit, please bear with me -
> > >
> > > I have a db which contains three tables - ServerInfo, ServiceInfo,
> > > TaskInfo
> > > The relation among them is
> > > One server can have multiple services
> > > One server can have multiple task
> > > There is no relation betwen task and services. There could be 'n' services
> > > and 'm' tasks running on the same server.
> > > Moreover, there could be n servers in the system.
> > >
> > > I want to create a all server health reports which displays status of the
> > > services and tasks per server. Each server info comes on a separate page.
> > >
> > > I tried to do this using data region. But the problem is data region are
> > > bound to one dataset, which doesnt work in my case.
> > >
> > > Please help me out in creating this report
> >
> >
> >
Posted by inlandtyuf at 1:54 AM 0 comments
Labels: , , , , , , , , , , , ,

Monday, March 26, 2012

How to show data at the bottom with multiple section?

I have three report footer setion. The first two section has a subreport in it. The last section has the grand total of a amount to both of the subreports.

I want to be able to show all three of these report footer section at the bottom of the page.

I have try the PrintAtBottomOfPage properties. I set it to TRUE to all of the report footer section. When I run the report, each section just shown on it own page.

Is there is a way to have all three section onto one page and on top of each other, not overlaying each other or showing on it own page?HI,
PrintAtBottomOfPage prints on the each pages bottom, so when this works
it still shows inside the Subreports bottm, if u want to get a value from sub report to main report use

1. Have a SHARED Variable(s) in a formula(s) for each of u r subreport(s)
2. Place those above the subreport(s) in the main Rpt, preferabblyy in the main reports header
3. in side the Subreports use a formula to sum u r values using
WhilePrintingRecords function to a varible u have declared 4 that Rpt.
(do this to u r all sub reports), place this formula in u r detail line of records printing (supress it, coz u need only the final answer).
4. in the main report sum the variable of sub reports declared in the main
or show seperatly as u wish in u r footer of main.

Favaz.|||I just want all three of the report footer section to be shown on the bottom of the same report of the first page and the subreport has the ability to GROW if there is a lot of data.

I just want all of the three report footer section to be all in the same page with my DETAILS section and just one page will be printed out.

I will not be pulling any variable from the subreport.|||Ummm .... I'm also having the same problem as his. Does anyone know the answer to his question?

please reply!
Posted by inlandtyuf at 5:06 AM 0 comments
Labels: , , , , , , , , , , , , , ,

How to share dimensions for multiple cubes in AS 2005?

Hello,

I have a couple of cubes that I created using a wizard in AS 2005. Every time I create a new cube, a AS wizard adds dimensions with 1,2 etc at the end of their names. Is it possible to use/share existing dimensions which are used by other cubes instead of having bunch of dimensions which are the same and the only difference is their name?

Thanks!

When you are going through cube building wizard, once you get to the Review Shared Dimensions page, select all the dimensions that you want to reuse. The wizard will reuse these dimensions and it won't create the dimensions with the 1,2 etc.

Hope this helps.

Van Dieu

|||Altering what you have already generated may be complicated. I don't use the wizard to generate cubes, so I'm not sure how much work is involved to change it. It might be as simple as opening the cube editor, deleting the dimensions that are duplicates, and adding the ones that you want to use, but I doubt it. You'll probably have to alter the data source view and the dimension usage in the cube editor as well.|||

Actually, it is that easy. You can just open up the cube editor and delete all the duplicated dimensions. Then on the dimension usage tab add them back in (the dimensions that you want to share).

Van Dieu

|||Good to know Smile
Posted by inlandtyuf at 4:49 AM 0 comments
Labels: , , , , , , , , , , , , , , , ,

Friday, March 23, 2012

How to setup Multiple tempdbs?

Hi,
We have got a huge SQL server 2000 box and we want to setup Multiple
tempdbs for a multiprocessor machine, but do not know how.
Anyone that can help please?
Thanks"Jnadile" <Jnadile@.discussions.microsoft.com> wrote in message
news:9E5C5454-8C17-4CC5-B00B-4FD7FCD08A35@.microsoft.com...
> Hi,
> We have got a huge SQL server 2000 box and we want to setup Multiple
> tempdbs for a multiprocessor machine, but do not know how.
>
You can not set up multiple tempdbs.
You can however add FILES to the existing one.
Check out the alter database command.
> Anyone that can help please?
> Thanks
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi
"Jnadile" wrote:
> Hi,
> We have got a huge SQL server 2000 box and we want to setup Multiple
> tempdbs for a multiprocessor machine, but do not know how.
> Anyone that can help please?
> Thanks
You can only have one tempdb per instance, but it is beneficial to have
multiple files for the tempdb that match the number of (logical) processes,
as this will help to reduce contention. For SQL 2000 you will also need to
set the trace flag 1118 and implement hotfix 702 see
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
To increase the number of files use the ALTER DATABASE command
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
HTH
John|||"John Bell" wrote:
> Hi
> "Jnadile" wrote:
> > Hi,
> >
> > We have got a huge SQL server 2000 box and we want to setup Multiple
> > tempdbs for a multiprocessor machine, but do not know how.
> >
> > Anyone that can help please?
> >
> > Thanks
> You can only have one tempdb per instance, but it is beneficial to have
> multiple files for the tempdb that match the number of (logical) processes,
Typo processes should be processors ie. CPUs!
It is also advised that if you have detected blocking on tempdb, then to
increase the number of files gradually up to the recommended number and stop
at the minumum number where it does not block.
Johnsql
Posted by inlandtyuf at 4:08 AM 0 comments
Labels: , , , , , , , , , , , ,

How to setup Multiple tempdbs?

Hi,
We have got a huge SQL server 2000 box and we want to setup Multiple
tempdbs for a multiprocessor machine, but do not know how.
Anyone that can help please?
Thanks
"Jnadile" <Jnadile@.discussions.microsoft.com> wrote in message
news:9E5C5454-8C17-4CC5-B00B-4FD7FCD08A35@.microsoft.com...
> Hi,
> We have got a huge SQL server 2000 box and we want to setup Multiple
> tempdbs for a multiprocessor machine, but do not know how.
>
You can not set up multiple tempdbs.
You can however add FILES to the existing one.
Check out the alter database command.

> Anyone that can help please?
> Thanks
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hi
"Jnadile" wrote:

> Hi,
> We have got a huge SQL server 2000 box and we want to setup Multiple
> tempdbs for a multiprocessor machine, but do not know how.
> Anyone that can help please?
> Thanks
You can only have one tempdb per instance, but it is beneficial to have
multiple files for the tempdb that match the number of (logical) processes,
as this will help to reduce contention. For SQL 2000 you will also need to
set the trace flag 1118 and implement hotfix 702 see
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
To increase the number of files use the ALTER DATABASE command
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
HTH
John
|||"John Bell" wrote:

> Hi
> "Jnadile" wrote:
>
> You can only have one tempdb per instance, but it is beneficial to have
> multiple files for the tempdb that match the number of (logical) processes,
Typo processes should be processors ie. CPUs!
It is also advised that if you have detected blocking on tempdb, then to
increase the number of files gradually up to the recommended number and stop
at the minumum number where it does not block.
John
Posted by inlandtyuf at 4:07 AM 0 comments
Labels: , , , , , , , , , , , , ,

How to setup Multiple tempdbs?

Hi,
We have got a huge SQL server 2000 box and we want to setup Multiple
tempdbs for a multiprocessor machine, but do not know how.
Anyone that can help please?
Thanks"Jnadile" <Jnadile@.discussions.microsoft.com> wrote in message
news:9E5C5454-8C17-4CC5-B00B-4FD7FCD08A35@.microsoft.com...
> Hi,
> We have got a huge SQL server 2000 box and we want to setup Multiple
> tempdbs for a multiprocessor machine, but do not know how.
>
You can not set up multiple tempdbs.
You can however add FILES to the existing one.
Check out the alter database command.

> Anyone that can help please?
> Thanks
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi
"Jnadile" wrote:

> Hi,
> We have got a huge SQL server 2000 box and we want to setup Multiple
> tempdbs for a multiprocessor machine, but do not know how.
> Anyone that can help please?
> Thanks
You can only have one tempdb per instance, but it is beneficial to have
multiple files for the tempdb that match the number of (logical) processes,
as this will help to reduce contention. For SQL 2000 you will also need to
set the trace flag 1118 and implement hotfix 702 see
http://www.sqlsecurity.com/Tools/Fr...65/Default.aspx
To increase the number of files use the ALTER DATABASE command
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
HTH
John|||"John Bell" wrote:
[vbcol=seagreen]
> Hi
> "Jnadile" wrote:
>
> You can only have one tempdb per instance, but it is beneficial to have
> multiple files for the tempdb that match the number of (logical) processes,[/vbcol
]
Typo processes should be processors ie. CPUs!
It is also advised that if you have detected blocking on tempdb, then to
increase the number of files gradually up to the recommended number and stop
at the minumum number where it does not block.
John
Posted by inlandtyuf at 4:07 AM 0 comments
Labels: , , , , , , , , , , , , ,

Friday, March 9, 2012

How to SET multiple variables from one table record?

It's come up more than once for me, where I need to DECLARE and SET several SQL variables in a Stored Procedure where many of these values come from the same table record - what is the best method for doing this, where I don't have to resort to making a separate query for each value.

Currently I'll do something like this:

DECLARE @.var1 int
SET @.var1 = (SELECT TOP 1 field1 FROM table1 WHERE recordkey = @.somekey)
DECLARE @.var2 nvarchar(20)
SET @.var2 = (SELECT TOP 1 field2 FROM table1 WHERE recordkey = @.somekey)

Of course, I'd rather just have to query "table1" just once to assign my variables.

What obvious bit of T-SQL am I missing?

Thank you in advance.

Select @.var1 = field1, @.var2 = field2, @.var3 = field3 from table1 where recordid = @.recid

|||

Thank you PDraigh!

I knew it was something obvious - I think I need a holidayEmbarrassed [:$]

Posted by inlandtyuf at 5:12 AM 0 comments
Labels: , , , , , , , , , , , , ,

How To Set Multiple ReadOnlyVariables in Script Component in Integration Services 2005

Hello!

I'ave got a problem of setting more than one Variable in ReadOnlyVariables Property of ScriptComponent...I provide comma separated list of names ( As described in the help ) byt VS Studio Editor can not be opoened claiming that there is no a variablle with such a name...Looks like it doesn't treat the list as a collection of names...

Please help.

Vladimir

Make sure there are no spaces in the list.

Var1,Var2,Var3

This will not work:

Var1, Var2, Var3

Also note that variable names are case sensitive.|||Triple-check your spelling and the scope your variables are defined in. I got the error just this morning and it was a spelling problem.
|||

Thanks for your response...

I verified the spelling got rid of spaces...but result is the same

It is interesting thing.. I have only two variables: One is set on a package level and another is on the Data Flow Task level...

When I set one of them in ReadonlyVariables and another in ReadandWriteVariables it allows me to open VS for Applications. If I move both to the same location ( ReadonLy or ReadAnd Write with comma separation and no spaces ) it issues the message I described...

I tried specifying the namespaces for the variables, but with no Luck...

Not usre what to do...

Any ideas will be greately appreciated...

Thanks,

Vladimir

|||What version of SSIS are you using?

RTM? SP1? SP2?

Posted by inlandtyuf at 5:12 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , , ,

Wednesday, March 7, 2012

how to set multiple local variable from single select statement

is it possible to set the value of multiple local variables using just 1
statement. For example assume a simple table 'E' with 3 columns 'empID',
'empName', 'empToken'. I can get their values with
declare @.ENAME as char(30), @.ETOKEN as int
SET @.ENAME = (SELECT empName FROM E WHERE empID = 1)
SET @.ETOKEN = (SELECT empToken FROM E WHERE empID = 1)
What I want to do is compine the two assignments into 1 single statement so
that the db never has to be looked up more than once. e.g.
SET @.ENAME, @.ETOKEN = (SELECT empName, empToken FROM E WHERE empID = 1)
is this possible and if it is, what is the synthax?try this.. hope this helps.
SELECT @.ENAME = empName,
@.ETOKEN = empToken
FROM E WHERE empID = 1
--|||HI,
sure:
SELECT @.ENAME = empName , @.ETOKEN = emptoken FROM E WHERE empID = 1
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
--
Posted by inlandtyuf at 8:16 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , ,

How to set Multiple Active results Sets (MARS) enable in ODBC

hello all there

can any body tell me how could i set the Multiple Active results Sets (MARS) option on/yes in case of the ODBC.

The thing is i create the connection using the SQL Native(sql sqrver-2005)wizard,

i got following when create the DNS

Microsoft SQL Native Client Version 09.00.1399

Data Source Name: DM_N
Data Source Description:
Server: vsnet1
Use Integrated Security: No
Database: DM
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Mirror Server:
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes

i just want to set the

Multiple Active Result Sets(MARS): YES

how could i ?

any help, solution or hint

Regards

Thanks

GSG

use this within your SQL Server connectionstring:

"Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;MARS_Connection=yes"

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Posted by inlandtyuf at 8:16 AM 0 comments
Labels: , , , , , , , , , , , , , , ,

How to set Multiple Active Results Sets (MARS) enable in ODBC

hello all there

can any body tell me how could i set the Multiple Active results Sets (MARS) option on/yes in case of the ODBC.

The thing is i create the connection using the SQL Native(sql sqrver-2005)wizard,

i got following when create the DNS

Microsoft SQL Native Client Version 09.00.1399

Data Source Name: DM_N
Data Source Description:
Server: vsnet1
Use Integrated Security: No
Database: DM
Language: (Default)
Data Encryption: No
Trust Server Certificate: No
Multiple Active Result Sets(MARS): No
Mirror Server:
Translate Character Data: Yes
Log Long Running Queries: No
Log Driver Statistics: No
Use Regional Settings: No
Use ANSI Quoted Identifiers: Yes
Use ANSI Null, Paddings and Warnings: Yes

i just want to set the

Multiple Active Result Sets(MARS): YES

how could i ?

any help, solution or hint

Regards

Thanks

Gurpreet S. Gill

use this within your SQL Server connectionstring:

"Driver={SQL Native Client};Server=Aron1;Database=pubs;Trusted_Connection=yes;MARS_Connection=yes"

HTH, jens Suessmeyer.

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

Thanks HTH

but the problem is that i dont want to configure it using connection string as HTH said.

i want to Configure it from using SQL Native wizard(ODBC) itself

as i cant touch the application code...

So i just want to have the DNS with

Multiple Active Result Sets(MARS): YES

or any other idea?

Thanks

Regard

Gurpreet S. Gill

|||

There was a lot of careful thought that went into the decision not to expose MARS in the DSN Wizard setttings. MARS is only useful in the case of an application specifically designed to use MARS, and turning MARS on for an application not explicitly designed for it will either provide no utility or in the worst case may cause the application to work incorrectly. Given that, it made sense to require the developer to be present to make the application changes needed to work correctly with MARS, in which case they can use the connection string to turn MARS on.

One way around this might be to use a FileDSN. By creating a FileDSN and manually adding the MARS_Connection keyword you should be able to connect using MARS. But I would exercise caution and make sure the application is well tested for that case.

|||

Hi i set the File DSN as

[ODBC]
DRIVER=SQL Native Client
UID=sa
DATABASE=DM
WSID=VSNET5
APP=Microsoft Data Access Components
SERVER=VSNET1
MARS_Connection=YES

but still it doesnt make MARS enables connection, our appliction still gives error "Connection Busy...."

Any other hint or solution?

regards,

Gurpreet S. Gill

|||

Add this before you call SQLDriverConnect

SQLSetConnectAttr(hdbc, SQL_COPT_SS_MARS_ENABLED, SQL_MARS_ENABLED_YES, SQL_IS_UINTEGER);

|||

If you need to use MARS, you need to set the connection attribute in your application explicitly. It is not possible to enable it in DSN.

This is because that MARS is only useful if an application acutally uses the functionality provided by MARS. In this case, it should not be a problem for the application to set the MARS attribute in code.

It sounds weird to me an application uses MARS but connects to database without enabling it...

|||Where could i write this

SQLSetConnectAttr(hdbc, SQL_COPT_SS_MARS_ENABLED, SQL_MARS_ENABLED_YES, SQL_IS_UINTEGER);

?

Posted by inlandtyuf at 8:16 AM 0 comments
Labels: , , , , , , , , , , , , , , ,

How to set Merge Repl properties for multiple servers?

Hello...
Currently we have merge replication set up between SQL 2000 and mobile
devices running SQL CE 2.0...and some with SQL Mobile.
As part of the logic on the device, we have to set some properties for the
SqlCeReplication object. Some of these we make dynamic, like the
publication, login, etc.
However, we currently hard code the Publisher and InternetURL values to our
existing server like:
repl.Publisher = "MyMachineName"
repl.InternetURL =
"http://MyMachineName.domain.com/SqlRepl/sqlcesa20.dll"
My question or problem is this...what happens in the case of a failover? We
have a backup/redundant/mirrored server at a different geographic
location...obviously it has a different server name. For the devices in the
field, how do people handle this type of situation where the server failover
will occur automatically. Or, if we decide to move to a server farm...where
we have multiple Sql Servers...how can we make our mobile application
agnostic to the Publisher?
Thanks for any help or advice.
- will
The best way to do this is to put your publisher on a cluster.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dw" <dw@.discussions.microsoft.com> wrote in message
news:D241EC93-AFFF-4024-B342-99AF1C4E82D4@.microsoft.com...
> Hello...
> Currently we have merge replication set up between SQL 2000 and mobile
> devices running SQL CE 2.0...and some with SQL Mobile.
> As part of the logic on the device, we have to set some properties for the
> SqlCeReplication object. Some of these we make dynamic, like the
> publication, login, etc.
> However, we currently hard code the Publisher and InternetURL values to
our
> existing server like:
> repl.Publisher = "MyMachineName"
> repl.InternetURL =
> "http://MyMachineName.domain.com/SqlRepl/sqlcesa20.dll"
> My question or problem is this...what happens in the case of a failover?
We
> have a backup/redundant/mirrored server at a different geographic
> location...obviously it has a different server name. For the devices in
the
> field, how do people handle this type of situation where the server
failover
> will occur automatically. Or, if we decide to move to a server
farm...where
> we have multiple Sql Servers...how can we make our mobile application
> agnostic to the Publisher?
> Thanks for any help or advice.
> - will
|||Would that mean the Publisher and Distributor would be on different machines?
"Hilary Cotter" wrote:

> The best way to do this is to put your publisher on a cluster.
> --
>
|||dw wrote:
> Hello...
> Currently we have merge replication set up between SQL 2000 and mobile
> devices running SQL CE 2.0...and some with SQL Mobile.
> As part of the logic on the device, we have to set some properties for the
> SqlCeReplication object. Some of these we make dynamic, like the
> publication, login, etc.
> However, we currently hard code the Publisher and InternetURL values to our
> existing server like:
> repl.Publisher = "MyMachineName"
> repl.InternetURL =
> "http://MyMachineName.domain.com/SqlRepl/sqlcesa20.dll"
> My question or problem is this...what happens in the case of a failover? We
> have a backup/redundant/mirrored server at a different geographic
> location...obviously it has a different server name. For the devices in the
> field, how do people handle this type of situation where the server failover
> will occur automatically. Or, if we decide to move to a server farm...where
> we have multiple Sql Servers...how can we make our mobile application
> agnostic to the Publisher?
> Thanks for any help or advice.
> - will
Try using a DNS alias name and have your subscribers connect to that. When you change the server,
also change DNS to point to the new machine. Will be transparent to the subscribers. Make sure
that the server will respond to the alias name.
We use aliases at my company. Recently, the motherboard on our production server died. All the
data files are on an external disk array. We have an identical server as backup. We moved the C:
hard drive to the backup server and moved the cable from the disk array. We changed DNS to point
the alias to the new server. Back up in 10 minutes.
This also works when upgrading to a bigger server.
|||No, they should be on the same machine.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"dw" <dw@.discussions.microsoft.com> wrote in message
news:09D18E34-DC6F-4EAE-82F4-07645EE1578E@.microsoft.com...
> Would that mean the Publisher and Distributor would be on different
machines?[vbcol=seagreen]
> "Hilary Cotter" wrote:
Posted by inlandtyuf at 8:15 AM 0 comments
Labels: , , , , , , , , , , , , , ,

How to set Grow column and row height in crosstab reports

hello guys,
i am working with crystal report 9.0, i have one report that contains multiple columns in crosstab. but in this report column and row headings are not growing according to content length and overlapping on other column and row values. also can grow option is disabled for row and column headings in crosstab. can anybody help me to solve this problem.you need to manually increase the width as much as possible
Posted by inlandtyuf at 8:12 AM 0 comments
Labels: , , , , , , , , , , , , , , , , , ,

Sunday, February 19, 2012

How to service data for multiple customers

Hello All,

I do not know if this is the proper group to ask my question. Please do
direct me to the proper place.

I have a database server and would like to store data for multiple
groups of customers. The data is not shared amongst the groups but are
shared only amongst individuals composing a group.

One solution would be to lump all data from all groups into a single
instance of the database server accompanied with a tag that would
identify to which group each piece of information belongs.

Is there a better method to achieve what I want to do. Would my
solution present any difficulties in the future in terms of performance,
maintenance, scalability.

Are there other solutions that may be available to address my needs.

Any suggestions would be greatly appreciated.

ThanksCREATE TABLE CustomerGrps
(grp_nbr INTEGER NOT NULL, -- a code for the CHAID analysis??
customer_id ...,
..
PRIMARY KEY (grp_nbr, customer_id),
...)'|||Thanks for the input. I was looking towards a solution more from a DBA's
perspective rather than a programmer's perspective.

For example. In order to service multiple groups of customers. Would it be
better to have one sqlserver instance to service the need of all groups of
customers or would it be more advantageous to install multiple instances of
sqlserver, one instance for each group of customers.

thanks

"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1121912144.832540.98870@.g49g2000cwa.googlegro ups.com...
> CREATE TABLE CustomerGrps
> (grp_nbr INTEGER NOT NULL, -- a code for the CHAID analysis??
> customer_id ...,
> ..
> PRIMARY KEY (grp_nbr, customer_id),
> ..)'|||If all the customers will have similar admin/support demands and expect
the same service levels then fewer servers/instances will likely mean
less work from a DBA perspective. In that case, the main considerations
will probably be around performance/scalability and any security
concerns the customer has about sharing the server with others.
Multiple instances on the same server don't offer anything much on the
scalability front but design the application such that it can support
multiple servers in case you need that capability in the future.

--
David Portas
SQL Server MVP
--

Posted by inlandtyuf at 11:01 PM 0 comments
Labels: , , , , , , , , , , ,

How to send notification to multiple email accounts?

I am in process of setting up job failure notification and one of the requirements that I have is to send notification to multiple users. I would appreciate any suggestion. One of the possible ways would be to create some sort of group account on exchange server with all users added as members. Is there anyway though where I could use users' individual accounts and based on that forward notification to them?

Thanks

Which version of sql server is this?

In SQL Server 2005 you can use Database Mail - http://www.sqlserverclub.com/articles/article05.aspx

In SQL Server 2000 try SQL Mail - http://support.microsoft.com/kb/263556

and also you can send mail using SP_OACreate SP

Refer : http://www.sqlteam.com/article/sending-smtp-mail-using-a-stored-procedure

Madhu

|||

Madhu,

I am sorry for not mentioning in advance that I am using Database Mail in SQL Server 2005 and that is why I am wondering how I could send notification to multiple users from job failure. As I understand using Profile would send email to only one of the accounts listed in that profile. I am quite familiar with Database Mail and I have been using it successfully for almost a year now.

|||

Without creating distribution list or group recipient from Exchange Server, I couldn't find any other way to send job failure notification to multiple people. My solution was to create a procedure that collects history of failed job and add it as a last step to my jobs so that whenever job failed it would execute that last step. Within that procedure I used sp_send_dbmail with a list of users email addresses. Solution seem to work brilliant.

Posted by inlandtyuf at 10:59 PM 0 comments
Labels: , , , , , , , , , , , , , ,
Subscribe to: Posts (Atom)