How to show the current datasource in a report?

I'm using Reporting Services 2000 on SQL Server 2000. I want to show the currently used datasource to avoid troubles when switching between test data and productive data. Is it possible to show the name and/or the description of the used datasource?

Thanks for your answer,
AndreasYou could try selecting @.@.servername, db_name()
along with your query.|||

Unfortunalty servername and database name ist not helpful for the user and works only on MS Sql Server. Isn't there a possibility to get the abstract name and description of the current configured datasource?
Thanks for your answer,
Andreas

|||Is there really no possibility?

how to show one record per one page

I am using reporting services to build a report to show customer's balance statements. I need each page only show one customer and related transactions and balance etc.

Now it shows several customers' balance in one page, how can i change it to show only one record per one page, and also one customer per one page when print it out.

how can i achieve that?

cheers

place all your controls in a list control

in the properties for the list control

group by customer

check the "page brake before" option

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
>

How to show line numbers in the first column of the report

Hi,
I want to show line numbers on report page in the first column of my
report. I am working on microsoft reporting services 2000.
How i can do this.
Regards
DineshOn Mar 9, 11:59 pm, "Dinesh" <dinesh...@.gmail.com> wrote:
> Hi,
> I want to show line numbers on report page in the first column of my
> report. I am working on microsoft reporting services 2000.
> How i can do this.
> Regards
> Dinesh
This should work: =RowNumber("DataSourceName") as an expression in the
Value property of the first column.
Regards,
Enrique Martinez
Sr. SQL Server Developer

How to show HTML text interpretation in SQL Server Reporting Services

Hi everybody

Is there a way to show HTML tags in a SQL Server Reporting services just like we are used to do in Crystal Reports?

I my case, I use the property Text Interpretation: HTML (right click in parameter field inside report and click edit parameter menu option).

I was wondering if we have something similiar in Reporting services?!

Hi, have you figured this out. I'd need to do that as well :)

I'll let you know if I find something.

|||

I'm going to ask Microsoft SQL Server Reporting Services forum:

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=82&SiteID=1

give it a try!

|||

Hi Cynick

Teo from Microsoft told said to me:

"HTML inlining is not supported with RS 2005. It is on the top list for the next release.".

As you can see we have to wait for the next Reporting Services version! Check out the link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1540154&SiteID=1

I`m so disappointed but it'll be worth to wait!

sql

How to show duplicates in reports

I'm trying to create a report in Sql Server 2005 Reporting Services where it will display duplicates rows in the reports. I'm unable to find the property that enables this. I would need something like HideDuplicates = false, but that is not the way it is intended to be used. If HideDuplicates is left blank, it seems to still hide duplicate values if the preceding row matches.

The report is a crosstab type, so we are using a Matrix for the report item. I'll illustrate with a simple example:

This is what we currently get:

Catagory	Class	Detail
Type 1	A	Item 1
	B	Item 2
		Item 3
Type 2	A	Item 4
	B	Item 5

This is what we want:

Catagory	Class	Detail
Type 1	A	Item 1
Type 1	B	Item 2
Type 1	B	Item 3
Type 2	A	Item 4
Type 2	B	Item 5

I'm sure its something simple, but we just cant seem to find the solution,

Thanks

Darin

Rather than using a matrix report, I think a simple list report would do what you are looking for (if you don't do any grouping). You can still use aggreated fields in the simple list report.

|||

Unfortunately we need to use a matrix as the actual report is a crosstab, I just didn't illustrate that in the example for simplicity sake.

I think the report looks nicer with the blanks and is easier to read; however, if it is dumped to Excel for further analysis ( which in our actual report will happen often), the auto filter function will not work as the blank rows are not included if a specifiec catagory is selected. Only the first row that is allgned with the merged cells is shown.

|||

Open the code view of the report and search for the XML node called "HideDuplicates". See http://msdn2.microsoft.com/en-us/library/ms152916.aspx for on how to set. Removing the element entirely will cause the values to appear in each row on the report, like you'd want to have for use in Excel.

My problem is related. I have business analysts using the Report Builder tool basically as a querying tool. 90% of the time, they export to Excel and then crunch there. While I know this is not intended usage of Reporting Services, I am still disappointed by the fact that, in order to get duplicates to appear, you must go code-side, something I can't push off on a BA.

Any ideas for editting a Report Builder generated report file on the fly, to remove "HideDuplicates" elements?

|||

Try setting the "HideDuplicate" property on the "Type 1" textbox in your report.

Here are the defintion of the Textbox properties:

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

Textbox

The Textbox element has the following attributes/elements in addition to what it inherits from ReportItem:

Attributes/Elements

Name

Card

Type

Description

Value

1

Expression

(Variant)

An expression, the value of which is displayed in the text-box.

This can be a constant expression for constant labels.

CanGrow

0-1

Boolean

Indicates the Textbox size can increase to accommodate the contents

CanShrink

0-1

Boolean

Indicates the Textbox size can decrease to match the contents

HideDuplicates

0-1

String

Indicates the text should not be displayed when the value of the expression associated with the report item is the same as the preceding instance.The value of HideDuplicates is the name of a containing grouping (other than the current grouping) or data set over which to apply the hiding.Each time a new instance of that group is encountered, the first instance of this report item will not be hidden. Rows on a previous page are ignored for the purposes of hiding duplicates.If the textbox is in a table or matrix cell, only the text will be omitted.The textbox will remain to provide background and border for the cell.Outside of a table/matrix cell, the background and borders are omitted as well.

Ignored in matrix subtotals.

ToggleImage

0-1

Element

Indicates the initial state of a toggling image should one be displayed as a part of the textbox.

UserSort

0-1

Element

Indicates an end-user sort control should be displayed as a part of this textbox in the UI.

DataElementStyle

0-1

Enum

Indicates whether textbox value should render as an element or attribute: Auto (Default) | AttributeNormal | ElementNormal. Auto uses the setting on the Report element.

|||Sorry for the late reply, but the 'HideDuplicates' element is nowhere to be found in the report definition.|||I've tried setting the HideDuplicates to a number of the values available in the dropdown list and it still does not solve my problem. Other than that, I really dont know what to set it to.|||You have to change the DiscourageGrouping Property on the fields you don't want the report to be hidden when grouped. Do it on the Report Model Designer.

Regards

How to show duplicates in reports

I'm trying to create a report in Sql Server 2005 Reporting Services where it will display duplicates rows in the reports. I'm unable to find the property that enables this. I would need something like HideDuplicates = false, but that is not the way it is intended to be used. If HideDuplicates is left blank, it seems to still hide duplicate values if the preceding row matches.

The report is a crosstab type, so we are using a Matrix for the report item. I'll illustrate with a simple example:

This is what we currently get:

Catagory	Class	Detail
Type 1	A	Item 1
	B	Item 2
		Item 3
Type 2	A	Item 4
	B	Item 5

This is what we want:

Catagory	Class	Detail
Type 1	A	Item 1
Type 1	B	Item 2
Type 1	B	Item 3
Type 2	A	Item 4
Type 2	B	Item 5

I'm sure its something simple, but we just cant seem to find the solution,

Thanks

Darin

Rather than using a matrix report, I think a simple list report would do what you are looking for (if you don't do any grouping). You can still use aggreated fields in the simple list report.

|||

Unfortunately we need to use a matrix as the actual report is a crosstab, I just didn't illustrate that in the example for simplicity sake.

I think the report looks nicer with the blanks and is easier to read; however, if it is dumped to Excel for further analysis ( which in our actual report will happen often), the auto filter function will not work as the blank rows are not included if a specifiec catagory is selected. Only the first row that is allgned with the merged cells is shown.

|||

Open the code view of the report and search for the XML node called "HideDuplicates". See http://msdn2.microsoft.com/en-us/library/ms152916.aspx for on how to set. Removing the element entirely will cause the values to appear in each row on the report, like you'd want to have for use in Excel.

My problem is related. I have business analysts using the Report Builder tool basically as a querying tool. 90% of the time, they export to Excel and then crunch there. While I know this is not intended usage of Reporting Services, I am still disappointed by the fact that, in order to get duplicates to appear, you must go code-side, something I can't push off on a BA.

Any ideas for editting a Report Builder generated report file on the fly, to remove "HideDuplicates" elements?

|||

Try setting the "HideDuplicate" property on the "Type 1" textbox in your report.

Here are the defintion of the Textbox properties:

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

Textbox

The Textbox element has the following attributes/elements in addition to what it inherits from ReportItem:

Attributes/Elements

Name

Card

Type

Description

Value

1

Expression

(Variant)

An expression, the value of which is displayed in the text-box.

This can be a constant expression for constant labels.

CanGrow

0-1

Boolean

Indicates the Textbox size can increase to accommodate the contents

CanShrink

0-1

Boolean

Indicates the Textbox size can decrease to match the contents

HideDuplicates

0-1

String

Indicates the text should not be displayed when the value of the expression associated with the report item is the same as the preceding instance.The value of HideDuplicates is the name of a containing grouping (other than the current grouping) or data set over which to apply the hiding.Each time a new instance of that group is encountered, the first instance of this report item will not be hidden. Rows on a previous page are ignored for the purposes of hiding duplicates.If the textbox is in a table or matrix cell, only the text will be omitted.The textbox will remain to provide background and border for the cell.Outside of a table/matrix cell, the background and borders are omitted as well.

Ignored in matrix subtotals.

ToggleImage

0-1

Element

Indicates the initial state of a toggling image should one be displayed as a part of the textbox.

UserSort

0-1

Element

Indicates an end-user sort control should be displayed as a part of this textbox in the UI.

DataElementStyle

0-1

Enum

Indicates whether textbox value should render as an element or attribute: Auto (Default) | AttributeNormal | ElementNormal. Auto uses the setting on the Report element.

|||Sorry for the late reply, but the 'HideDuplicates' element is nowhere to be found in the report definition.|||I've tried setting the HideDuplicates to a number of the values available in the dropdown list and it still does not solve my problem. Other than that, I really dont know what to set it to.sql

How to show duplicates in reports

I'm trying to create a report in Sql Server 2005 Reporting Services where it will display duplicates rows in the reports. I'm unable to find the property that enables this. I would need something like HideDuplicates = false, but that is not the way it is intended to be used. If HideDuplicates is left blank, it seems to still hide duplicate values if the preceding row matches.

The report is a crosstab type, so we are using a Matrix for the report item. I'll illustrate with a simple example:

This is what we currently get:

Catagory	Class	Detail
Type 1	A	Item 1
	B	Item 2
		Item 3
Type 2	A	Item 4
	B	Item 5

This is what we want:

Catagory	Class	Detail
Type 1	A	Item 1
Type 1	B	Item 2
Type 1	B	Item 3
Type 2	A	Item 4
Type 2	B	Item 5

I'm sure its something simple, but we just cant seem to find the solution,

Thanks

Darin

Rather than using a matrix report, I think a simple list report would do what you are looking for (if you don't do any grouping). You can still use aggreated fields in the simple list report.

|||

Unfortunately we need to use a matrix as the actual report is a crosstab, I just didn't illustrate that in the example for simplicity sake.

I think the report looks nicer with the blanks and is easier to read; however, if it is dumped to Excel for further analysis ( which in our actual report will happen often), the auto filter function will not work as the blank rows are not included if a specifiec catagory is selected. Only the first row that is allgned with the merged cells is shown.

|||

Open the code view of the report and search for the XML node called "HideDuplicates". See http://msdn2.microsoft.com/en-us/library/ms152916.aspx for on how to set. Removing the element entirely will cause the values to appear in each row on the report, like you'd want to have for use in Excel.

My problem is related. I have business analysts using the Report Builder tool basically as a querying tool. 90% of the time, they export to Excel and then crunch there. While I know this is not intended usage of Reporting Services, I am still disappointed by the fact that, in order to get duplicates to appear, you must go code-side, something I can't push off on a BA.

Any ideas for editting a Report Builder generated report file on the fly, to remove "HideDuplicates" elements?

|||

Try setting the "HideDuplicate" property on the "Type 1" textbox in your report.

Here are the defintion of the Textbox properties:

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

Textbox

The Textbox element has the following attributes/elements in addition to what it inherits from ReportItem:

Attributes/Elements

Name

Card

Type

Description

Value

1

Expression

(Variant)

An expression, the value of which is displayed in the text-box.

This can be a constant expression for constant labels.

CanGrow

0-1

Boolean

Indicates the Textbox size can increase to accommodate the contents

CanShrink

0-1

Boolean

Indicates the Textbox size can decrease to match the contents

HideDuplicates

0-1

String

Indicates the text should not be displayed when the value of the expression associated with the report item is the same as the preceding instance.The value of HideDuplicates is the name of a containing grouping (other than the current grouping) or data set over which to apply the hiding.Each time a new instance of that group is encountered, the first instance of this report item will not be hidden. Rows on a previous page are ignored for the purposes of hiding duplicates.If the textbox is in a table or matrix cell, only the text will be omitted.The textbox will remain to provide background and border for the cell.Outside of a table/matrix cell, the background and borders are omitted as well.

Ignored in matrix subtotals.

ToggleImage

0-1

Element

Indicates the initial state of a toggling image should one be displayed as a part of the textbox.

UserSort

0-1

Element

Indicates an end-user sort control should be displayed as a part of this textbox in the UI.

DataElementStyle

0-1

Enum

Indicates whether textbox value should render as an element or attribute: Auto (Default) | AttributeNormal | ElementNormal. Auto uses the setting on the Report element.

|||Sorry for the late reply, but the 'HideDuplicates' element is nowhere to be found in the report definition.|||I've tried setting the HideDuplicates to a number of the values available in the dropdown list and it still does not solve my problem. Other than that, I really dont know what to set it to.

How to show a linked report in a report container?

I have a report container (reportviewer.dll) in a .net web page to display
the reports using the reporting services. Some of the reportds are linked
report and I am not sure how to display it in the report container.
Currently it opens on a new page. Thanks.I have the same problem...
Any luck with that?
--
http://dotnet.org.za/stanley
"Paul" wrote:
> I have a report container (reportviewer.dll) in a .net web page to display
> the reports using the reporting services. Some of the reportds are linked
> report and I am not sure how to display it in the report container.
> Currently it opens on a new page. Thanks.
>
>

how to setup Integration Services on SQL 2005 cluster

We have SQL Server 2005 cluster setup, active/passive, 2 nodes, on Windows
2003. If I try to add a maintenance task using sql management studio on my
pc I get error: "apply to target server failed for job". If I try it using
management studio while on the active node of the cluster I get error:
"failed to create the task, exception from hresult: 0xC0010014
Microsoft.SqlServer.DTSRuntimeWrap".
I installed Integration Services on both nodes and followed instructions in
document ms345193 to configure IS on the cluster, but I must have something
wrong.
Any ideas what I should look at to get this to work? Thanks!
Denise,
this you follow this http://msdn2.microsoft.com/en-us/library/ms345193.aspx
?
HTH,
_Edwin.
"denise" <denise@.discussions.microsoft.com> wrote in message
news:92A9C846-69CA-4B46-87FE-75E567904FD9@.microsoft.com...
> We have SQL Server 2005 cluster setup, active/passive, 2 nodes, on Windows
> 2003. If I try to add a maintenance task using sql management studio on
my
> pc I get error: "apply to target server failed for job". If I try it
using
> management studio while on the active node of the cluster I get error:
> "failed to create the task, exception from hresult: 0xC0010014
> Microsoft.SqlServer.DTSRuntimeWrap".
> I installed Integration Services on both nodes and followed instructions
in
> document ms345193 to configure IS on the cluster, but I must have
something
> wrong.
> Any ideas what I should look at to get this to work? Thanks!
|||Yes, I was a little unsure of which drives to use as the dependencies (should
all shared drives be there, we have several, I just picked one, E drive), and
the registry set up, in step 9, I just put what is in the document, then in
step 14 I put the shared drive where I copied the MsDtsSrvr.ini.xml file (I
used E drive).
Could someone talk me through what should be set in each step? Thanks!
"Edwin vMierlo" wrote:

> Denise,
> this you follow this http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> ?
> HTH,
> _Edwin.
>
>
> "denise" <denise@.discussions.microsoft.com> wrote in message
> news:92A9C846-69CA-4B46-87FE-75E567904FD9@.microsoft.com...
> my
> using
> in
> something
>
>
|||
> Yes, I was a little unsure of which drives to use as the dependencies
(should
> all shared drives be there, we have several, I just picked one, E drive),
and
If the E drive is the only drive you use for the packages and file, than
that is OK, however if not sure you can add all drives.

> the registry set up, in step 9, I just put what is in the document, then
in
In step 9 you fill in the registry location (not the disk);
"SOFTWARE\Microsoft\MSDTS\ServiceConfigFile"

> step 14 I put the shared drive where I copied the MsDtsSrvr.ini.xml file
(I
> used E drive).
In step 14 you put in the fulname and directory path in the registry,
Example
E:\dir\subdir\MsDtsSrvr.ini.xml

> Could someone talk me through what should be set in each step? Thanks!
HTH,
_Edwin.
|||I have all this set up correctly. Any other ideas of what could be wrong?
Would it be affected by the MSDTS configuration set up on the cluster?
Thanks so much for your help.
"Edwin vMierlo" wrote:

> (should
> and
> If the E drive is the only drive you use for the packages and file, than
> that is OK, however if not sure you can add all drives.
> in
> In step 9 you fill in the registry location (not the disk);
> "SOFTWARE\Microsoft\MSDTS\ServiceConfigFile"
> (I
> In step 14 you put in the fulname and directory path in the registry,
> Example
> E:\dir\subdir\MsDtsSrvr.ini.xml
>
> HTH,
> _Edwin.
>
>

How to setup integration services on a cluster

Does anyone know how to install SQL 2005 integration services on a cluster machine?

I have an a-a cluster machine. Both nodes are using SQL 2005 naming instance. During the SQL installation, I did include integration services option as one of my setup items. The setup process was running fine but I can’t connect to integration services via MS SQL Server management studio. I see below errors:

“Invalid server name "TESTServer\B123". SSIS service does not support multi-instance, use just server name instead of "server name\instance".

Looks like I must use default instance for 2005 integration services. I re-ran the setup program and added a default instance on the same cluster node. (On that node I have one default instance and one naming instance). Now I can connect to the default SQL Server but when I click on “stored packages”, I don’t see file system and MSDB. It shows nothing at all. Any idea how to make integration services running on a cluster machine?

Thanks

I had the same error when I tried to view an SSIS package on my server with 2 instances but I had no default instance, so my case was a little different than yours. The way I resolved my problem was by hard coding both instances into my XML configuration file. I did not have a cluster in this case but the principle should be the same, you'll just have to play around with it a little bit. You may also have to modify the XML file on both nodes of your cluster.

<ServerName>my-server-01\instance1</ServerName>
<ServerName>my-server-01\instance2</ServerName>

If you have a default instance and a named instance you might try something like this:

<ServerName>my-server-01</ServerName>
<ServerName>my-server-01\instance1</ServerName>

Microsoft SQL Server 2005 Integration Services (SSIS) includes a configuration file for configuring the Integration Services service.

By default, the file is located in the folder, Program Files\Microsoft SQL Server\90\DTS\Binn, and the file name is MsDtsSrvr.ini.xml.

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

|||

Microsoft has a document published on how to install Integration Services on a cluster.

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

|||

I have the same problem in that I have set up SSIS on the cluster using the microsoft instructions. The SSIS service starts and fails over between nodes just fine. The problem is that I cannot see the MSDB or File System under Stored Packages. I've double (tripple) checked the path to the XML in the registry, the registry path in the cluster service properties, and the XML itself. Everything seems in order.

Any ideas?

|||

I'm in exactly the same situation. Did anyone resolve this problem ?

tia

bryn64

|||Have smae problem. Resolution?

How to setup integration services on a cluster

Does anyone know how to install SQL 2005 integration services on a cluster machine?

I have an a-a cluster machine. Both nodes are using SQL 2005 naming instance. During the SQL installation, I did include integration services option as one of my setup items. The setup process was running fine but I can’t connect to integration services via MS SQL Server management studio. I see below errors:

“Invalid server name "TESTServer\B123". SSIS service does not support multi-instance, use just server name instead of "server name\instance".

Looks like I must use default instance for 2005 integration services. I re-ran the setup program and added a default instance on the same cluster node. (On that node I have one default instance and one naming instance). Now I can connect to the default SQL Server but when I click on “stored packages”, I don’t see file system and MSDB. It shows nothing at all. Any idea how to make integration services running on a cluster machine?

Thanks

I had the same error when I tried to view an SSIS package on my server with 2 instances but I had no default instance, so my case was a little different than yours. The way I resolved my problem was by hard coding both instances into my XML configuration file. I did not have a cluster in this case but the principle should be the same, you'll just have to play around with it a little bit. You may also have to modify the XML file on both nodes of your cluster.

<ServerName>my-server-01\instance1</ServerName>
<ServerName>my-server-01\instance2</ServerName>

If you have a default instance and a named instance you might try something like this:

<ServerName>my-server-01</ServerName>
<ServerName>my-server-01\instance1</ServerName>

Microsoft SQL Server 2005 Integration Services (SSIS) includes a configuration file for configuring the Integration Services service.

By default, the file is located in the folder, Program Files\Microsoft SQL Server\90\DTS\Binn, and the file name is MsDtsSrvr.ini.xml.

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

|||

Microsoft has a document published on how to install Integration Services on a cluster.

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

|||

I have the same problem in that I have set up SSIS on the cluster using the microsoft instructions. The SSIS service starts and fails over between nodes just fine. The problem is that I cannot see the MSDB or File System under Stored Packages. I've double (tripple) checked the path to the XML in the registry, the registry path in the cluster service properties, and the XML itself. Everything seems in order.

Any ideas?

|||

I'm in exactly the same situation. Did anyone resolve this problem ?

tia

bryn64

|||Have smae problem. Resolution?sql

How to setup integration services on a cluster

Does anyone know how to install SQL 2005 integration services on a cluster machine?

I have an a-a cluster machine. Both nodes are using SQL 2005 naming instance. During the SQL installation, I did include integration services option as one of my setup items. The setup process was running fine but I can’t connect to integration services via MS SQL Server management studio. I see below errors:

“Invalid server name "TESTServer\B123". SSIS service does not support multi-instance, use just server name instead of "server name\instance".

Looks like I must use default instance for 2005 integration services. I re-ran the setup program and added a default instance on the same cluster node. (On that node I have one default instance and one naming instance). Now I can connect to the default SQL Server but when I click on “stored packages”, I don’t see file system and MSDB. It shows nothing at all. Any idea how to make integration services running on a cluster machine?

Thanks

I had the same error when I tried to view an SSIS package on my server with 2 instances but I had no default instance, so my case was a little different than yours. The way I resolved my problem was by hard coding both instances into my XML configuration file. I did not have a cluster in this case but the principle should be the same, you'll just have to play around with it a little bit. You may also have to modify the XML file on both nodes of your cluster.

<ServerName>my-server-01\instance1</ServerName>
<ServerName>my-server-01\instance2</ServerName>

If you have a default instance and a named instance you might try something like this:

<ServerName>my-server-01</ServerName>
<ServerName>my-server-01\instance1</ServerName>

Microsoft SQL Server 2005 Integration Services (SSIS) includes a configuration file for configuring the Integration Services service.

By default, the file is located in the folder, Program Files\Microsoft SQL Server\90\DTS\Binn, and the file name is MsDtsSrvr.ini.xml.

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

|||

Microsoft has a document published on how to install Integration Services on a cluster.

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

|||

I have the same problem in that I have set up SSIS on the cluster using the microsoft instructions. The SSIS service starts and fails over between nodes just fine. The problem is that I cannot see the MSDB or File System under Stored Packages. I've double (tripple) checked the path to the XML in the registry, the registry path in the cluster service properties, and the XML itself. Everything seems in order.

Any ideas?

|||

I'm in exactly the same situation. Did anyone resolve this problem ?

tia

bryn64

|||Have smae problem. Resolution?

How to set up reporting services to have up-to-date cube data whenever report opened

Hi, at work I have set up reporting services with analysis services
providing cube data. I have created a cube and deployed it to reporting
services. What I want to know is how do i guarantee that when an executive
opens up the report, they will get the latest data from the SQL database? I
have installed/created everything with defaults, including the reports and
their deployment via visual studio to reporting services.
Here I am guessing that when a report is opened that reporting services
connects to analysis services (which interacts with sql server whatever way)
to get the latest update of data within the report? I am just a little
concerned because I had a previous report set up and no matter how i tried
to get it to update data, it just couldnt. I had to redeploy the solution
from VS to reporting services.
Any help most appreciated!
cheers
murrayThe default deployment will always get you the latest data... However if you
make a change to the report onthe reporting server, that change will NOT be
overridden by redeploying...
Perhaps you set the cache time on the report in Report Manager... That means
that data will be re-used without re-querying for 60 minutes... Redeploying
the report will NOT change this and many other settings... You would have to
either delete and redeploy or change the setting in Report Manager..
However, for the standard default deploy of a new report, no caching is used..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"j.murray" wrote:
> Hi, at work I have set up reporting services with analysis services
> providing cube data. I have created a cube and deployed it to reporting
> services. What I want to know is how do i guarantee that when an executive
> opens up the report, they will get the latest data from the SQL database? I
> have installed/created everything with defaults, including the reports and
> their deployment via visual studio to reporting services.
> Here I am guessing that when a report is opened that reporting services
> connects to analysis services (which interacts with sql server whatever way)
> to get the latest update of data within the report? I am just a little
> concerned because I had a previous report set up and no matter how i tried
> to get it to update data, it just couldnt. I had to redeploy the solution
> from VS to reporting services.
> Any help most appreciated!
> cheers
> murray
>
>sql

how to set up a cube for pivot table service in Excel?

background: sql2k and analysis service
I know how to create a cube in analysis service, but how to view the
cube in the Pivot Table services in Excel?
when I go to Data|PivotTable and PivotChart report...|External Data
Source|
Get Data | OLAP cube, then browse to the server, but I don't see any of
the cubes being created.
I'm using Standard SQL2k. Is this a sql version issue or should i
create cube differently?
thank youHave you set permissions on the cube to allow access from the user running
Excel?
Under Database Roles in Analysis manager.
Standard version includes analysis services, with EE having increased
features in some areas, so I do not think that is your problem.
Mike John
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1107205552.456050.61110@.f14g2000cwb.googlegroups.com...
> background: sql2k and analysis service
> I know how to create a cube in analysis service, but how to view the
> cube in the Pivot Table services in Excel?
> when I go to Data|PivotTable and PivotChart report...|External Data
> Source|
> Get Data | OLAP cube, then browse to the server, but I don't see any of
> the cubes being created.
> I'm using Standard SQL2k. Is this a sql version issue or should i
> create cube differently?
> thank you
>|||i'm very lost...:(
i created a cube on the analysis service then add my nt accout to the
All Users database role. (enforced on client).
i then go thru the steps in Excell, Data|PivotTable and PivotChart
report...|External Data Source|
Get Data | OLAP cube, but then where shoudl browse to from there?
i check the book online about the local cube. it was not helping. i
still dont' know how to create a local cube.|||Steve, it is difficult to follow your description, but Local cube has
nothing to do with connecting to an analysis services cube.
After you select olap cube you should be able to select thser server and
database that contains the cube.
Mike John
"=== Steve L ===" <steve.lin@.powells.com> wrote in message
news:1107212127.396318.211160@.f14g2000cwb.googlegroups.com...
> i'm very lost...:(
> i created a cube on the analysis service then add my nt accout to the
> All Users database role. (enforced on client).
> i then go thru the steps in Excell, Data|PivotTable and PivotChart
> report...|External Data Source|
> Get Data | OLAP cube, but then where shoudl browse to from there?
> i check the book online about the local cube. it was not helping. i
> still dont' know how to create a local cube.
>|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?|||...After you select olap cube you should be able to select thser server
and
database that contains the cube...
which i did but i couldnt' find any cubes on the sql server running
analysis services thru Excel pivot table services. i can see cubes in
the analsysis services itself, but even when i search the sql server
with analysis services on it, i couldn't find any file with extension
.cub
also, i looked up the help files for excel:
In the PivotTable report, the Offline OLAP command on the PivotTable
menu (PivotTable toolbar (toolbar: A bar with buttons and options that
you use to carry out commands. To display a toolbar, click Customize on
the Tools menu, and then click the Toolbars tab.)) is unavailable if
the provider does not support offline cube files.
in my excel, the Offline OLAP menu is always greyed out. why?

How to set this up?

I need to set up the security on the Reporting Services such that user can
view the reports ONLY through a ReportViewer control on a asp.net web page
ie not allow to navigate all other folders or areas in the Report Manager.
Reporting services is installed on a stand alone server (not belong to any
domain, no AD set up) running on Windows 2003 server. Currently I configured
the "Report Manager" and "ReportServer" web site to allow annonyous access.
However some users know how to bypass the web application and directly enter
the IP address/reports of the stand alone server to view all the reports.
The asp.net page has built-in security to filter out the reports listed on
the page based on the user login and password. This web application is also
hosted at the stand alone server and is connected to a remote Sql server.
The web application is using the sql security.I have a similar problem and cannot find a solution provided by Microsoft.
However, I decidee to solve it by a trick. I develop an ASP.net web
application and the application have some report printing function. Whenever
a report is printed, a random key will be inserted into a database table
like this:
Key User
KE#n-asdfjk-ad33klj-kasjd WBush
The key and user are also passed in the query string. When the report is
run, the key and user will be checked again in the stored procedure to
verify that this user can print this report.
I am not sure whether this is a good solution, but I think that's what we
can do before Microsoft can provide a better solution.
"Paul" <paul_mak@.hotmail.com> wrote in message
news:eWZLuvhdGHA.2456@.TK2MSFTNGP04.phx.gbl...
> I need to set up the security on the Reporting Services such that user can
> view the reports ONLY through a ReportViewer control on a asp.net web page
> ie not allow to navigate all other folders or areas in the Report Manager.
> Reporting services is installed on a stand alone server (not belong to any
> domain, no AD set up) running on Windows 2003 server. Currently I
configured
> the "Report Manager" and "ReportServer" web site to allow annonyous
access.
> However some users know how to bypass the web application and directly
enter
> the IP address/reports of the stand alone server to view all the reports.
> The asp.net page has built-in security to filter out the reports listed on
> the page based on the user login and password. This web application is
also
> hosted at the stand alone server and is connected to a remote Sql server.
> The web application is using the sql security.
>|||Hi Paul.
The only way I know of is to have your web site's app pool use an identity
that is the only identity that is allowed to hit the reports.
For example, you might set up a service account called "WebApp1SvcAcct".
give that account and administrators group privileges in reporting services,
but not the rest of your users. This might be only half the solution. It's
been a while since we've had to deal with this.
-Tim
"Paul" <paul_mak@.hotmail.com> wrote in message
news:eWZLuvhdGHA.2456@.TK2MSFTNGP04.phx.gbl...
>I need to set up the security on the Reporting Services such that user can
>view the reports ONLY through a ReportViewer control on a asp.net web page
>ie not allow to navigate all other folders or areas in the Report Manager.
>Reporting services is installed on a stand alone server (not belong to any
>domain, no AD set up) running on Windows 2003 server. Currently I
>configured the "Report Manager" and "ReportServer" web site to allow
>annonyous access. However some users know how to bypass the web application
>and directly enter the IP address/reports of the stand alone server to view
>all the reports. The asp.net page has built-in security to filter out the
>reports listed on the page based on the user login and password. This web
>application is also hosted at the stand alone server and is connected to a
>remote Sql server. The web application is using the sql security.
>

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?

How to set connection, Send Parameter and Filter data

Dear all .Netter,

I'm newbie in Reporting Services. I'm able to creating raw Reporting Services reports.

I have these questions to you all :

1. How to change connection to server and database when running in ASP.Net form. Currently in development PC, the server name is ServerA and the Database name is DatabaseA. After i copy to production server, the server name is ServerB and the database name is DatabaseB.

2. How to send parameter to Reporting Services report. I want to show name of user that print the report.

3. How to filter data that will be showed in the Reporting Services. I want to print Invoice with no : INV-2007-0001 and next INV-2007-0002

Sorry, if I'm lazy, but i'm running with time to replace Crystal Reports with Reporting Services.

Thanks and Regards,

Kusno.

These are some great links that will give you any answer you want

http://www.codeproject.com/sqlrs/AHCreatRepsAspNet.asp
http://msdn2.microsoft.com/en-us/library/ms170246.aspx
http://www.codeproject.com/sqlrs/ReportViewer2005.asp

Have Fun