Configure Integration Services on a Cluster

Hi

I am having an active/active cluster setup where there are 2 virtual SQL Servers one with default instance and other with named instance.

1) i need to know how to cluster integration service.

2) how are the integration packages are run, are they run by the integration
services service, or by the SQL Instance?

3) When fail over happens from one node to other will my packages continue to run?

i had setup Integration Services on a Cluster per the following article: http://msdn2.microsoft.com/en-us/library/ms345193.aspx.... but

when i do fail over or move the group to another node

... getting an error indicating:
An error occured attempting to read properties for the 'xxx IntegrationServices' resource.
The object already exists.
Error ID: 5010 (00001392)
Do you want to ignore this error and continue
[yes] [yes to all] [no] ...

I understand that integration service can be clustered only once.....

Should i do the clustering in Integration Services in a different Group
than sql server groups?....but then again i wont be able to acces MSDB...

Please provide advice on the matter,,,,,i am desparately waiting for a solution

Thanks in advance....

I

1 - That BOL topic is a good resource and pretty much covers it I think. Of course it doesn't cover you error which is perhaps more on an issue. Some other notes here as well- Cluster SSIS
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ClusterSSIS.html)

2 - Neither. SSIS (like DTS before it) is client side, it is just an object model really. Your packages are run by the process that calls them. So normally you use something like DTEXEC to call them, or the similar SQL Server Agent SISS job step subsystem. (Use CmdExec and DTEXEC even in jobs!). So if calling DTEXEC yourself it runs under you account on your machine. If using a scheduled job and DTEXEC/SSIS Subsystem it runs under the SQL Server Agent service (or proxy account is specified) so in that case it runs on the SQL Server machine that hosts the SQL Agent service. The SSIS service is a bit dumb and is just used to show what is running. Packages call into the service to say they are running, but the service is not the host.

3 - No. If packages are running during failover, then they will not continue to run, restart or do anything really because the SSIS service is not in control. However stopping the SSIS service will cancel a running package.

The error sounds has there is something duplicated, double check that you have followed the instructions you followed.

Which Group you choose is a matter of preference, as discussed in the documentation. The link above gives another reference you may wish to consider.

For my money, I don't see MSDB access as a big deal. I don't think using it as a package store is worth the effort, files are much easier to manage. Logging would be the only consideration, and that would sway me to keep them together. Coupled with the fact that I generally always use Agent to run/host the packages, it makes sense to keep them together for that as well.

|||

Hi

Thanks for the reply

My configuration has two virtual servers server1 and server2 in active /active mode

Server1 (default instance) is on cluster group1 and Server2 (named instance) is on cluster group2 as given below

Group1

Disk S - Data Files
Disk T - Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL1)
SQL Server Agent (SQL1)
SQL Full Text (SQL1)

Group2

Disk U - Data Files
Disk V- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL2) SQL Server Agent (SQL2)
SQL Full Text (SQL2)

Both the servers have packages running.

I need to know how to do the clustering.

I tried clustering as per the Microsoft document : http://msdn2.microsoft.com/en-us/library/ms345193.aspx by Configuring Integration Services in the Same Group as SQL Server

then i had to configure it in the two groups (group 1 and Group 2)but cluster groups won't come online on one nodes when i do fail over... was getting an error An error occured attempting to read properties for the 'xxx Integration
Services' resource.
The object already exists.
Error ID: 5010 (00001392)
Do you want to ignore this error and continue
[yes] [yes to all] [no]....

I need to know whether what i did is the correct method ... bcos the two servers were in differnt groups as above, i configured integration service on the two groups as per the document so i had integration service on group 1 as well as integration service on group 2

If i configure it only on a single group will it say group1 then will it be working fine (will group2 ie server2 have integration service running ? and when failover happens packages in both server continue to run ?)

If i go for the other option as per microsoft document that is in a different group than the SQL Server Database Engine

should i go for a third group say group 3 ?..pls tell me how to configure the group ?..wht all resources to be added ...?

Awaiting your reply

Thanks in advance

|||

So now things make sense!

You can only have 1 instance of the SSIS service running per machine. So by adding it to two virtual server groups, you risk trying to run two instances of SSIS on the same machine at the same time, hence the "object exists" or rather "stop mucking about with trying to run two copies of this service, I will not let you."

If running multi-instance cluster, also (previously) known as active/active, then just step back and think about what clustering SSIS will achieve? Since the SSIS service does not support multiple instances on the same machine, and the service is not a true cluster aware service that is installed within the virtual server container, (much the same thing really), then you should not try and cluster it as you will inevitably have clashes.

What does it give you anyway? Storing packages in MSDB or on a shared disk will mean they stay with that instance of SQL, which I'd say is the most important point. Run an instance of the SSIS service on each machine if you want, but don't bother with trying clustering them.

|||

Thanks a lot ............

one more query...

When i do installation of SQL server 2005 should i change the default location of integration services to the shared drive?

In my current I have my packages in C: drive which will not failover to the next node .

How do i go about to make this change...

awaiting your reply

Thanks in advance

|||

You can change the managed file store path after install, which I think would be the best method, and using a shared drive would make some sense.

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

You can actually use any file path, it does not have to be the managed one. I never use it, and find no real need to enumerate packages in SSMS which is about all it gives you.

sqlsql

Configure Integration Services on a Cluster

We are attempting to setup Integration Services on a Cluster per the
following article:
http://msdn2.microsoft.com/en-us/library/ms345193.aspx
We have the following Cluster Groups/resources
"Cluster Group"
Disk Q - Quorum
Disk R - MSDTC
MSDTC
MSDTC IP Address
Cluster Name
Cluster IP
"SQL1 Group"
Disk S - Data Files
Disk T - Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL1)
SQL Server Agent (SQL1)
SQL Full Text (SQL1)
SQL1 Integration Services
"SQL2 Group"
Disk U - Data Files
Disk V- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL2)
SQL Server Agent (SQL2)
SQL Full Text (SQL2)
SQL2 Integration Services
"SQL3 Group"
Disk W - Data Files
Disk X- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL3)
SQL Server Agent (SQL3)
SQL Full Text (SQL3)
SQL3 Integration Services
The problem is that the integration services resources in the various
cluster groups won't come online on all nodes - only certain nodes. We're
getting an error indicating:
An error occured attempting to read properties for the 'xxx Integration
Services' resource.
The object already exists.
Error ID: 5010 (00001392)
Do you want to ignore this error and continue
[yes] [yes to all] [no]
Does anyone know how this should be setup? Should we have seperate
integration services resources in every group or only one group? Why are we
getting the error mentioned above?
Thanks,
BradOps I just realized there is a seperate group for SQL Server Clustering.
Sorry for the cross posting!
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:%23oc4RZc7GHA.1256@.TK2MSFTNGP04.phx.gbl...
> We are attempting to setup Integration Services on a Cluster per the
> following article:
> http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> We have the following Cluster Groups/resources
> "Cluster Group"
> Disk Q - Quorum
> Disk R - MSDTC
> MSDTC
> MSDTC IP Address
> Cluster Name
> Cluster IP
> "SQL1 Group"
> Disk S - Data Files
> Disk T - Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL1)
> SQL Server Agent (SQL1)
> SQL Full Text (SQL1)
> SQL1 Integration Services
> "SQL2 Group"
> Disk U - Data Files
> Disk V- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL2)
> SQL Server Agent (SQL2)
> SQL Full Text (SQL2)
> SQL2 Integration Services
> "SQL3 Group"
> Disk W - Data Files
> Disk X- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL3)
> SQL Server Agent (SQL3)
> SQL Full Text (SQL3)
> SQL3 Integration Services
> The problem is that the integration services resources in the various
> cluster groups won't come online on all nodes - only certain nodes. We're
> getting an error indicating:
> An error occured attempting to read properties for the 'xxx Integration
> Services' resource.
> The object already exists.
> Error ID: 5010 (00001392)
> Do you want to ignore this error and continue
> [yes] [yes to all] [no]
> Does anyone know how this should be setup? Should we have seperate
> integration services resources in every group or only one group? Why are
> we getting the error mentioned above?
> Thanks,
> Brad
>|||Hello Brad,
I understand that you are not able to configure cluster resource for SSIS
properly. As I know, SSIS is not cluster-aware, so you'll have to install
on all nodes. However, SSIS can be installed within a virtual server on a
failover cluster as described here:
http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx.
That's much the way Analysis Services was done in 2000 -make it a generic
resource. It still requires installing the stuff local on each node.
Hope this is helpful. Please let's know if you have further questions or
concerns.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================|||This works as advertised...
SQL Server 2005 Books Online
How to: Configure Integration Services on a Cluster
http://msdn2.microsoft.com/en-us/library/ms345193.aspx
Terry Duffy
Frontline DBA
Peter Yang [MSFT] wrote:
> Hello Brad,
> I understand that you are not able to configure cluster resource for SSIS
> properly. As I know, SSIS is not cluster-aware, so you'll have to install
> on all nodes. However, SSIS can be installed within a virtual server on a
> failover cluster as described here:
> http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx.
> That's much the way Analysis Services was done in 2000 -make it a generic
> resource. It still requires installing the stuff local on each node.
> Hope this is helpful. Please let's know if you have further questions or
> concerns.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================|||Terry -
We followed the instructions in the article you mentioned. (Thats the same
link I included in my original post). However failover functionality does
not seem to be working right in all cases.
I have to assume we did something wrong I'm just not sure what we missed or
misunderstood. Do you have any idea if there should be an instances of the
Integration Services per SQL virtual server or one instance for tht cluster
total?
Brad
"Terry" <tduffy@.calamos.com> wrote in message
news:1160658501.995672.58180@.k70g2000cwa.googlegroups.com...
> This works as advertised...
> SQL Server 2005 Books Online
> How to: Configure Integration Services on a Cluster
> http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> Terry Duffy
> Frontline DBA
> Peter Yang [MSFT] wrote:
>> Hello Brad,
>> I understand that you are not able to configure cluster resource for SSIS
>> properly. As I know, SSIS is not cluster-aware, so you'll have to install
>> on all nodes. However, SSIS can be installed within a virtual server on a
>> failover cluster as described here:
>> http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx.
>> That's much the way Analysis Services was done in 2000 -make it a generic
>> resource. It still requires installing the stuff local on each node.
>> Hope this is helpful. Please let's know if you have further questions or
>> concerns.
>> Best Regards,
>> Peter Yang
>> MCSE2000/2003, MCSA, MCDBA
>> Microsoft Online Partner Support
>>
>> =====================================================>>
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> ======================================================>|||Peter -
Thanks for the link. Unfortunately its much the same information as the link
in my original post except with pictures. I've loaded SSIS on all nodes so I
don't believe that's a problem. I'm still a little unclear if we have a 4
node active/active/active/passive cluster with three SQL virtual servers -
do we have three SSIS cluster resources (one for each sql virtual server) or
just one SSIS cluster resource? Maybe I will go back and try to start from
scratch and see if that makes any difference. Any additional suggestions you
might have would be appreciated.
Thanks
Brad
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:UY5GxTe7GHA.4348@.TK2MSFTNGXA01.phx.gbl...
> Hello Brad,
> I understand that you are not able to configure cluster resource for SSIS
> properly. As I know, SSIS is not cluster-aware, so you'll have to install
> on all nodes. However, SSIS can be installed within a virtual server on a
> failover cluster as described here:
> http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx.
> That's much the way Analysis Services was done in 2000 -make it a generic
> resource. It still requires installing the stuff local on each node.
> Hope this is helpful. Please let's know if you have further questions or
> concerns.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> =====================================================>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ======================================================>|||Hello Brad,
Since SSIS does not support multiple instances, only a single resource is
necessary. You need to remove redudent ones to test the sitaution. Also, it
is suggested that you install it a seperate group since you have mauliple
instances installed. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Configure Integration Services on a Cluster

We are attempting to setup Integration Services on a Cluster per the
following article:
http://msdn2.microsoft.com/en-us/library/ms345193.aspx
We have the following Cluster Groups/resources
"Cluster Group"
Disk Q - Quorum
Disk R - MSDTC
MSDTC
MSDTC IP Address
Cluster Name
Cluster IP
"SQL1 Group"
Disk S - Data Files
Disk T - Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL1)
SQL Server Agent (SQL1)
SQL Full Text (SQL1)
SQL1 Integration Services
"SQL2 Group"
Disk U - Data Files
Disk V- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL2)
SQL Server Agent (SQL2)
SQL Full Text (SQL2)
SQL2 Integration Services
"SQL3 Group"
Disk W - Data Files
Disk X- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL3)
SQL Server Agent (SQL3)
SQL Full Text (SQL3)
SQL3 Integration Services
The problem is that the integration services resources in the various
cluster groups won't come online on all nodes - only certain nodes. We're
getting an error indicating:
An error occured attempting to read properties for the 'xxx Integration
Services' resource.
The object already exists.
Error ID: 5010 (00001392)
Do you want to ignore this error and continue
[yes] [yes to all] [no]
Does anyone know how this should be setup? Should we have seperate
integration services resources in every group or only one group? Why are we
getting the error mentioned above?
Thanks,
BradOps I just realized there is a seperate group for SQL Server Clustering.
Sorry for the cross posting!
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:%23oc4RZc7GHA.1256@.TK2MSFTNGP04.phx.gbl...
> We are attempting to setup Integration Services on a Cluster per the
> following article:
> http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> We have the following Cluster Groups/resources
> "Cluster Group"
> Disk Q - Quorum
> Disk R - MSDTC
> MSDTC
> MSDTC IP Address
> Cluster Name
> Cluster IP
> "SQL1 Group"
> Disk S - Data Files
> Disk T - Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL1)
> SQL Server Agent (SQL1)
> SQL Full Text (SQL1)
> SQL1 Integration Services
> "SQL2 Group"
> Disk U - Data Files
> Disk V- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL2)
> SQL Server Agent (SQL2)
> SQL Full Text (SQL2)
> SQL2 Integration Services
> "SQL3 Group"
> Disk W - Data Files
> Disk X- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL3)
> SQL Server Agent (SQL3)
> SQL Full Text (SQL3)
> SQL3 Integration Services
> The problem is that the integration services resources in the various
> cluster groups won't come online on all nodes - only certain nodes. We're
> getting an error indicating:
> An error occured attempting to read properties for the 'xxx Integration
> Services' resource.
> The object already exists.
> Error ID: 5010 (00001392)
> Do you want to ignore this error and continue
> [yes] [yes to all] [no]
> Does anyone know how this should be setup? Should we have seperate
> integration services resources in every group or only one group? Why are
> we getting the error mentioned above?
> Thanks,
> Brad
>|||Hello Brad,
I understand that you are not able to configure cluster resource for SSIS
properly. As I know, SSIS is not cluster-aware, so you'll have to install
on all nodes. However, SSIS can be installed within a virtual server on a
failover cluster as described here:
http://www.sqljunkies.com/WebLog/kn.../06/16015.aspx.
That's much the way Analysis Services was done in 2000 -make it a generic
resource. It still requires installing the stuff local on each node.
Hope this is helpful. Please let's know if you have further questions or
concerns.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============|||This works as advertised...
SQL Server 2005 Books Online
How to: Configure Integration Services on a Cluster
http://msdn2.microsoft.com/en-us/library/ms345193.aspx
Terry Duffy
Frontline DBA
Peter Yang [MSFT] wrote:
> Hello Brad,
> I understand that you are not able to configure cluster resource for SSIS
> properly. As I know, SSIS is not cluster-aware, so you'll have to install
> on all nodes. However, SSIS can be installed within a virtual server on a
> failover cluster as described here:
> [url]http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx.[/ur
l]
> That's much the way Analysis Services was done in 2000 -make it a generic
> resource. It still requires installing the stuff local on each node.
> Hope this is helpful. Please let's know if you have further questions or
> concerns.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> ========================================
==============|||Terry -
We followed the instructions in the article you mentioned. (Thats the same
link I included in my original post). However failover functionality does
not seem to be working right in all cases.
I have to assume we did something wrong I'm just not sure what we missed or
misunderstood. Do you have any idea if there should be an instances of the
Integration Services per SQL virtual server or one instance for tht cluster
total?
Brad
"Terry" <tduffy@.calamos.com> wrote in message
news:1160658501.995672.58180@.k70g2000cwa.googlegroups.com...
> This works as advertised...
> SQL Server 2005 Books Online
> How to: Configure Integration Services on a Cluster
> http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> Terry Duffy
> Frontline DBA
> Peter Yang [MSFT] wrote:
>|||Peter -
Thanks for the link. Unfortunately its much the same information as the link
in my original post except with pictures. I've loaded SSIS on all nodes so I
don't believe that's a problem. I'm still a little unclear if we have a 4
node active/active/active/passive cluster with three SQL virtual servers -
do we have three SSIS cluster resources (one for each sql virtual server) or
just one SSIS cluster resource? Maybe I will go back and try to start from
scratch and see if that makes any difference. Any additional suggestions you
might have would be appreciated.
Thanks
Brad
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:UY5GxTe7GHA.4348@.TK2MSFTNGXA01.phx.gbl...
> Hello Brad,
> I understand that you are not able to configure cluster resource for SSIS
> properly. As I know, SSIS is not cluster-aware, so you'll have to install
> on all nodes. However, SSIS can be installed within a virtual server on a
> failover cluster as described here:
> [url]http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/07/06/16015.aspx.[/ur
l]
> That's much the way Analysis Services was done in 2000 -make it a generic
> resource. It still requires installing the stuff local on each node.
> Hope this is helpful. Please let's know if you have further questions or
> concerns.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ========================================
==============
>|||Hello Brad,
Since SSIS does not support multiple instances, only a single resource is
necessary. You need to remove redudent ones to test the sitaution. Also, it
is suggested that you install it a seperate group since you have mauliple
instances installed. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Configure Integration Services on a Cluster

We are attempting to setup Integration Services on a Cluster per the
following article: http://msdn2.microsoft.com/en-us/library/ms345193.aspx
We have the following Cluster Groups/resources
"Cluster Group"
Disk Q - Quorum
Disk R - MSDTC
MSDTC
MSDTC IP Address
Cluster Name
Cluster IP
"SQL1 Group"
Disk S - Data Files
Disk T - Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL1)
SQL Server Agent (SQL1)
SQL Full Text (SQL1)
SQL1 Integration Services
"SQL2 Group"
Disk U - Data Files
Disk V- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL2)
SQL Server Agent (SQL2)
SQL Full Text (SQL2)
SQL2 Integration Services
"SQL3 Group"
Disk W - Data Files
Disk X- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL3)
SQL Server Agent (SQL3)
SQL Full Text (SQL3)
SQL3 Integration Services
The problem is that the integration services resources in the various
cluster groups won't come online on all nodes - only certain nodes. We're
getting an error indicating:
An error occured attempting to read properties for the 'xxx Integration
Services' resource.
The object already exists.
Error ID: 5010 (00001392)
Do you want to ignore this error and continue
[yes] [yes to all] [no]
Does anyone know how this should be setup? Should we have seperate
integration services resources in every group or only one group? Why are we
getting the error mentioned above?
Thanks,
Brad
You can only cluster it once. It is not an instanced component so the
service can only exist once on each node with the registry pointing at the
config file on the shared disk. Since you have multiple instances, follow
the instructions for "Configuring Integration Services in a Different Group
than SQL Server" to avoid dependencies on a specific instance being
available. If you store all your packages on the file system rather than
msdb this avoids any depence on any of the instances being available and you
can still share them out using clustered file shares to control access e.g.
Disk I
SSISPackages
SQL1 (Share)
SQL2 (Share)
SQL3 (Share)
SSISLogs
SQL1 (Share)
SQL2 (Share)
SQL3 (Share)
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:eNXo%23ac7GHA.140@.TK2MSFTNGP05.phx.gbl...
> We are attempting to setup Integration Services on a Cluster per the
> following article: http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> We have the following Cluster Groups/resources
> "Cluster Group"
> Disk Q - Quorum
> Disk R - MSDTC
> MSDTC
> MSDTC IP Address
> Cluster Name
> Cluster IP
> "SQL1 Group"
> Disk S - Data Files
> Disk T - Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL1)
> SQL Server Agent (SQL1)
> SQL Full Text (SQL1)
> SQL1 Integration Services
> "SQL2 Group"
> Disk U - Data Files
> Disk V- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL2)
> SQL Server Agent (SQL2)
> SQL Full Text (SQL2)
> SQL2 Integration Services
> "SQL3 Group"
> Disk W - Data Files
> Disk X- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL3)
> SQL Server Agent (SQL3)
> SQL Full Text (SQL3)
> SQL3 Integration Services
> The problem is that the integration services resources in the various
> cluster groups won't come online on all nodes - only certain nodes. We're
> getting an error indicating:
> An error occured attempting to read properties for the 'xxx Integration
> Services' resource.
> The object already exists.
> Error ID: 5010 (00001392)
> Do you want to ignore this error and continue
> [yes] [yes to all] [no]
> Does anyone know how this should be setup? Should we have seperate
> integration services resources in every group or only one group? Why are
> we getting the error mentioned above?
> Thanks,
> Brad
>
|||Would this work in the case of an active/active scenario where there
are 2 virtual SQL Servers, with one running on each of 2 cluster nodes?
When the integration packages are run, are they run by the integration
services service, or by the SQL Instance? In other words, does
Integration Services simply let you design the packages, or is it
running them?
I am finding this concept hard to grasp for some reason. I am used to
DTS in SQL 2000
I have a 2 node cluster with 2 virtual SQL servers, each with 1 named
instance. The Virtual servers can fail back and forth no problem. The
Integration Services is installed on both nodes, but gives an error
when I try and view the contents of the MSDB folder.
Jasper Smith wrote:[vbcol=seagreen]
> You can only cluster it once. It is not an instanced component so the
> service can only exist once on each node with the registry pointing at the
> config file on the shared disk. Since you have multiple instances, follow
> the instructions for "Configuring Integration Services in a Different Group
> than SQL Server" to avoid dependencies on a specific instance being
> available. If you store all your packages on the file system rather than
> msdb this avoids any depence on any of the instances being available and you
> can still share them out using clustered file shares to control access e.g.
> Disk I
> SSISPackages
> SQL1 (Share)
> SQL2 (Share)
> SQL3 (Share)
> SSISLogs
> SQL1 (Share)
> SQL2 (Share)
> SQL3 (Share)
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Brad Baker" <brad@.nospam.nospam> wrote in message
> news:eNXo%23ac7GHA.140@.TK2MSFTNGP05.phx.gbl...

Configure Integration Services on a Cluster

We are attempting to setup Integration Services on a Cluster per the
following article:
http://msdn2.microsoft.com/en-us/library/ms345193.aspx
We have the following Cluster Groups/resources
"Cluster Group"
Disk Q - Quorum
Disk R - MSDTC
MSDTC
MSDTC IP Address
Cluster Name
Cluster IP
"SQL1 Group"
Disk S - Data Files
Disk T - Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL1)
SQL Server Agent (SQL1)
SQL Full Text (SQL1)
SQL1 Integration Services
"SQL2 Group"
Disk U - Data Files
Disk V- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL2)
SQL Server Agent (SQL2)
SQL Full Text (SQL2)
SQL2 Integration Services
"SQL3 Group"
Disk W - Data Files
Disk X- Log Files
SQL IP Address
SQL Network Name
SQL Server (SQL3)
SQL Server Agent (SQL3)
SQL Full Text (SQL3)
SQL3 Integration Services
The problem is that the integration services resources in the various
cluster groups won't come online on all nodes - only certain nodes. We're
getting an error indicating:
An error occured attempting to read properties for the 'xxx Integration
Services' resource.
The object already exists.
Error ID: 5010 (00001392)
Do you want to ignore this error and continue
[yes] [yes to all] [no]
Does anyone know how this should be setup? Should we have seperate
integration services resources in every group or only one group? Why are we
getting the error mentioned above?
Thanks,
Brad
Ops I just realized there is a seperate group for SQL Server Clustering.
Sorry for the cross posting!
"Brad Baker" <brad@.nospam.nospam> wrote in message
news:%23oc4RZc7GHA.1256@.TK2MSFTNGP04.phx.gbl...
> We are attempting to setup Integration Services on a Cluster per the
> following article:
> http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> We have the following Cluster Groups/resources
> "Cluster Group"
> Disk Q - Quorum
> Disk R - MSDTC
> MSDTC
> MSDTC IP Address
> Cluster Name
> Cluster IP
> "SQL1 Group"
> Disk S - Data Files
> Disk T - Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL1)
> SQL Server Agent (SQL1)
> SQL Full Text (SQL1)
> SQL1 Integration Services
> "SQL2 Group"
> Disk U - Data Files
> Disk V- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL2)
> SQL Server Agent (SQL2)
> SQL Full Text (SQL2)
> SQL2 Integration Services
> "SQL3 Group"
> Disk W - Data Files
> Disk X- Log Files
> SQL IP Address
> SQL Network Name
> SQL Server (SQL3)
> SQL Server Agent (SQL3)
> SQL Full Text (SQL3)
> SQL3 Integration Services
> The problem is that the integration services resources in the various
> cluster groups won't come online on all nodes - only certain nodes. We're
> getting an error indicating:
> An error occured attempting to read properties for the 'xxx Integration
> Services' resource.
> The object already exists.
> Error ID: 5010 (00001392)
> Do you want to ignore this error and continue
> [yes] [yes to all] [no]
> Does anyone know how this should be setup? Should we have seperate
> integration services resources in every group or only one group? Why are
> we getting the error mentioned above?
> Thanks,
> Brad
>
|||Hello Brad,
I understand that you are not able to configure cluster resource for SSIS
properly. As I know, SSIS is not cluster-aware, so you'll have to install
on all nodes. However, SSIS can be installed within a virtual server on a
failover cluster as described here:
http://www.sqljunkies.com/WebLog/kni...06/16015.aspx.
That's much the way Analysis Services was done in 2000 -make it a generic
resource. It still requires installing the stuff local on each node.
Hope this is helpful. Please let's know if you have further questions or
concerns.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
|||This works as advertised...
SQL Server 2005 Books Online
How to: Configure Integration Services on a Cluster
http://msdn2.microsoft.com/en-us/library/ms345193.aspx
Terry Duffy
Frontline DBA
Peter Yang [MSFT] wrote:
> Hello Brad,
> I understand that you are not able to configure cluster resource for SSIS
> properly. As I know, SSIS is not cluster-aware, so you'll have to install
> on all nodes. However, SSIS can be installed within a virtual server on a
> failover cluster as described here:
> http://www.sqljunkies.com/WebLog/kni...06/16015.aspx.
> That's much the way Analysis Services was done in 2000 -make it a generic
> resource. It still requires installing the stuff local on each node.
> Hope this is helpful. Please let's know if you have further questions or
> concerns.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no rights.
> ================================================== ====
|||Terry -
We followed the instructions in the article you mentioned. (Thats the same
link I included in my original post). However failover functionality does
not seem to be working right in all cases.
I have to assume we did something wrong I'm just not sure what we missed or
misunderstood. Do you have any idea if there should be an instances of the
Integration Services per SQL virtual server or one instance for tht cluster
total?
Brad
"Terry" <tduffy@.calamos.com> wrote in message
news:1160658501.995672.58180@.k70g2000cwa.googlegro ups.com...
> This works as advertised...
> SQL Server 2005 Books Online
> How to: Configure Integration Services on a Cluster
> http://msdn2.microsoft.com/en-us/library/ms345193.aspx
> Terry Duffy
> Frontline DBA
> Peter Yang [MSFT] wrote:
>
|||Peter -
Thanks for the link. Unfortunately its much the same information as the link
in my original post except with pictures. I've loaded SSIS on all nodes so I
don't believe that's a problem. I'm still a little unclear if we have a 4
node active/active/active/passive cluster with three SQL virtual servers -
do we have three SSIS cluster resources (one for each sql virtual server) or
just one SSIS cluster resource? Maybe I will go back and try to start from
scratch and see if that makes any difference. Any additional suggestions you
might have would be appreciated.
Thanks
Brad
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:UY5GxTe7GHA.4348@.TK2MSFTNGXA01.phx.gbl...
> Hello Brad,
> I understand that you are not able to configure cluster resource for SSIS
> properly. As I know, SSIS is not cluster-aware, so you'll have to install
> on all nodes. However, SSIS can be installed within a virtual server on a
> failover cluster as described here:
> http://www.sqljunkies.com/WebLog/kni...06/16015.aspx.
> That's much the way Analysis Services was done in 2000 -make it a generic
> resource. It still requires installing the stuff local on each node.
> Hope this is helpful. Please let's know if you have further questions or
> concerns.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> ================================================== ====
>
|||Hello Brad,
Since SSIS does not support multiple instances, only a single resource is
necessary. You need to remove redudent ones to test the sitaution. Also, it
is suggested that you install it a seperate group since you have mauliple
instances installed. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Configure HTTPS Access to SQL Server 2005 Analysis Services

Hi All,

There's material for configure HTTP to access SSAS 2005. http://www.microsoft.com/technet/prodtechnol/sql/2005/httpssas.mspx
It's applied and success with basic authentication.
but i have no idea about using HTTPS to access it. can't find related doc in microsoft website too.
could you kindly share your experience or related material?

Thanks!
Best regards,
Tommy
can anyone help?

configuration tool

In SSRS 2005, where do I find Reporting Services Configuration tool ?
Thanks

Assuming you installed SSRS 2005 on C-drive then the path should be "C:\Program Files\Microsoft SQL Server\90\Tools\binn\RSConfigTool.exe"

configuration tool

In SSRS 2005, where do I find Reporting Services Configuration tool ?
Thanksstart > programs > ms sql server 2005 > configuration tools > reporting
services configuration
OR
"\Program Files\Microsoft SQL Server\90\Tools\Binn\RSConfigTool.exe"
"farshad" <farshad@.discussions.microsoft.com> wrote in message
news:75425385-15FE-4C68-B634-1F953713D9E6@.microsoft.com...
> In SSRS 2005, where do I find Reporting Services Configuration tool ?
> Thanks|||I am now in E-mail settings for SSRS 2005
It is asking for Sender address and SMTP address.
I guess I can get hold of the SMTP address which is probably the server the
send the emails but not sure what to place for Sender address ?
Thanks
"Immy" wrote:
> start > programs > ms sql server 2005 > configuration tools > reporting
> services configuration
> OR
> "\Program Files\Microsoft SQL Server\90\Tools\Binn\RSConfigTool.exe"
> "farshad" <farshad@.discussions.microsoft.com> wrote in message
> news:75425385-15FE-4C68-B634-1F953713D9E6@.microsoft.com...
> > In SSRS 2005, where do I find Reporting Services Configuration tool ?
> > Thanks
>
>|||whatever you want.
e.g Reporting@.server.com
"farshad" <farshad@.discussions.microsoft.com> wrote in message
news:A21C5499-5FA5-4FFB-BD7E-78972D0E8D15@.microsoft.com...
>I am now in E-mail settings for SSRS 2005
> It is asking for Sender address and SMTP address.
> I guess I can get hold of the SMTP address which is probably the server
> the
> send the emails but not sure what to place for Sender address ?
> Thanks
>
> "Immy" wrote:
>> start > programs > ms sql server 2005 > configuration tools > reporting
>> services configuration
>> OR
>> "\Program Files\Microsoft SQL Server\90\Tools\Binn\RSConfigTool.exe"
>> "farshad" <farshad@.discussions.microsoft.com> wrote in message
>> news:75425385-15FE-4C68-B634-1F953713D9E6@.microsoft.com...
>> > In SSRS 2005, where do I find Reporting Services Configuration tool ?
>> > Thanks
>>|||If the emails do not get sent, is there a place to check for status? i.e.
error message as for why email was not sent?
Thanks
"Immy" wrote:
> whatever you want.
> e.g Reporting@.server.com
> "farshad" <farshad@.discussions.microsoft.com> wrote in message
> news:A21C5499-5FA5-4FFB-BD7E-78972D0E8D15@.microsoft.com...
> >I am now in E-mail settings for SSRS 2005
> > It is asking for Sender address and SMTP address.
> > I guess I can get hold of the SMTP address which is probably the server
> > the
> > send the emails but not sure what to place for Sender address ?
> > Thanks
> >
> >
> > "Immy" wrote:
> >
> >> start > programs > ms sql server 2005 > configuration tools > reporting
> >> services configuration
> >>
> >> OR
> >>
> >> "\Program Files\Microsoft SQL Server\90\Tools\Binn\RSConfigTool.exe"
> >>
> >> "farshad" <farshad@.discussions.microsoft.com> wrote in message
> >> news:75425385-15FE-4C68-B634-1F953713D9E6@.microsoft.com...
> >> > In SSRS 2005, where do I find Reporting Services Configuration tool ?
> >> > Thanks
> >>
> >>
> >>
>
>sqlsql

Configuration SQL Server 2005 Report Services

I have SQL Server 2005 and SQL Reporting Services installed on my server
with IIS.
I would like to obtain instructions to configure the SQL Reporting Services
on this server.
Thank You,Hi,
I suggest you start your reading with the page "Configuring Reporting
Services Components " in SQL Server 2005 Books Online. Several
relevant topics link from there.
Andrew Watt [MVP]
On Thu, 20 Apr 2006 16:40:02 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:
>I have SQL Server 2005 and SQL Reporting Services installed on my server
>with IIS.
>I would like to obtain instructions to configure the SQL Reporting Services
>on this server.
>Thank You,

Configuration problem (WMIProviderException)

I installed Reporting Services on stand-alone server from a SQL Server 2005
Standard CD. It supposed to connect to a remote SQL Server that would host
the ReportServer database.
I went through the steps of the Configure Report Server till I got to
"Database Setups". I created a new ReportServer database and when click on
"Apply", I get the following warning:
-- QUOTE --
Setting Connection Info for Report Server
Although saving tghe database succeeded, the report server cannot access
internal information about deployment to determine whether the current
configuration is valid for this edition. [...]
-- UNQUOTE --
When I click on the link to show me more information, I get the following
reported exception:
-- QUOTE --
ReportServicesConfigUI.WMIProvider.WMIProviderException: The report server
cannot open a connection to the report server database. A connection to the
database is required for all requests and processing.
(rsReportServerDatabaseUnavailable)
at
ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
at
ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ListReportServersInDatabase(RSReportServerInfo[]& serverInfos)
-- UNQUOTE --
I'm not trying to set up a scale-out deployment. I want this single instance
of RS to connect to a remote database, and I verifiied that this is supported
by the Standard Edition of SQL Server 2005.
When I ignored this warning and moved the encryption keys stage of the
configuration, I found that only the Restore and Delete are enabled. If
clicked on "Delete", I get this error:
-- QUOTE --
ReportServicesConfigUI.WMIProvider.WMIProviderException: The report server
cannot open a connection to the report server database. A connection to the
database is required for all requests and processing.
(rsReportServerDatabaseUnavailable)
at
ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
at
ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ListReportServersInDatabase(RSReportServerInfo[]& serverInfos)
-- UNQUOTE --
I verified that the Reporting Server was actually able to connect to the
database to create the Reporting Server database. But somehow it cannot
connect anymore.
Any ideas as to why?
ThanksI am experiencing the exact same problem. Did you get this resolved? Also, I
am curious as to whether you are running a side-by-side installation with SQL
2000 (as I am) or simply using SQL 2005?
Anyone know the solution to this? I've been working on this for several days
now, to no avail
"Homam" wrote:
> I installed Reporting Services on stand-alone server from a SQL Server 2005
> Standard CD. It supposed to connect to a remote SQL Server that would host
> the ReportServer database.
> I went through the steps of the Configure Report Server till I got to
> "Database Setups". I created a new ReportServer database and when click on
> "Apply", I get the following warning:
> -- QUOTE --
> Setting Connection Info for Report Server
> Although saving tghe database succeeded, the report server cannot access
> internal information about deployment to determine whether the current
> configuration is valid for this edition. [...]
> -- UNQUOTE --
> When I click on the link to show me more information, I get the following
> reported exception:
> -- QUOTE --
> ReportServicesConfigUI.WMIProvider.WMIProviderException: The report server
> cannot open a connection to the report server database. A connection to the
> database is required for all requests and processing.
> (rsReportServerDatabaseUnavailable)
> at
> ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
> at
> ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ListReportServersInDatabase(RSReportServerInfo[]& serverInfos)
> -- UNQUOTE --
> I'm not trying to set up a scale-out deployment. I want this single instance
> of RS to connect to a remote database, and I verifiied that this is supported
> by the Standard Edition of SQL Server 2005.
> When I ignored this warning and moved the encryption keys stage of the
> configuration, I found that only the Restore and Delete are enabled. If
> clicked on "Delete", I get this error:
> -- QUOTE --
> ReportServicesConfigUI.WMIProvider.WMIProviderException: The report server
> cannot open a connection to the report server database. A connection to the
> database is required for all requests and processing.
> (rsReportServerDatabaseUnavailable)
> at
> ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ThrowOnError(ManagementBaseObject mo)
> at
> ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.ListReportServersInDatabase(RSReportServerInfo[]& serverInfos)
> -- UNQUOTE --
>
> I verified that the Reporting Server was actually able to connect to the
> database to create the Reporting Server database. But somehow it cannot
> connect anymore.
> Any ideas as to why?
>
> Thanks
>

Configuration of Reporting Services

obviously until I get this sorted I cannot deploy my reports

in my Reporting Services Configuration

All ticked except initialisation (3)

in Database services ticked but on the right hand side there are warnings/errors

Verifying Database Edition - ticked

Verifying Database Version - warning

The current connection string is not valid and can't be used to connect to SQL Server

Where do I check this? (1)

Creating A Grant Rights script for LocalSystem = ticked

Assigning Reporting Services Rights to User = error (2)

System.Data.SqlClient.SqlException: The role 'RSExecRole' does not exist in the current database.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script)
at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)

Can someone tell me what I should be checking please?

thanks

HI !!!

I am also facing the same problem, The database server is sql 2000 personal edition while reporting server is sql 2005 evaluation.

In 'Database Setup' option displayes 'success'; but 'Verify Database Version' and 'Setting Connection Info For Reporting Server' are warnings while 'Create grants rights script' task is failing.

The 'Initialization' option is also failing. The message displayed is 'The report server cannot open connection to report server database (rsReportServerDatabaseUnavailable)'

Which setting should I check in report configuration? Or is this backward compatibility issue?

|||

On your reporting database,

Make sure under Security/Roles - that the RSExecRole exist, if not create it. It should also be assigned to the schema of RSExecRole. Finally, on your report service login User make sure that this user is assigned under the RSExecRole.

I hope this helps.

Ham

|||

HI Ham,

Thanks a lot for the reply.

I'll try out the solution suggested by you and let you know the outcome after that.

-Nishad

|||

Ham,

I am having the same problem. I need extra clarification, though. When you say check under Security/Roles, are you talking about doing this on the Reporting Services server or the database engine server that reporting services is connected to?

For instance, in my Object Explorer I see my reporting services server and under that in the tree is Security -> Roles. However, when I try to right-click on anything there it gives me the following message:

Unhandled exception has occured in a component in your application. If you click Continue, the application will ignore this error and attempt to continue.

The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. the expected version is 'C.0.8.40'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase).

Furthermore, when I try to upgrade the database by clicking 'Upgrade' in the Database Setup section of the Reporting Services Configuration Manager it gives me a green check on Verifying Database Edition and a yellow exclamation mark on Verifying Database Version. The warning says:

The current connection string is not valid and can't be used to connect to SQL Server.

So, if you mean I should add the RSExecRole to Security/Roles under my reporting services server, then I am in even deeper trouble than I thought. However, if you mean I should add them to Security/Server Roles under my database engine tree then I'm probably back in business.

I am loathe to try anything until I get clarification. Thanks for your kind help!

|||

Nevermind, I solved my problem.

I just needed to click the New button on the Database Setup page before trying to connect. Pretty silly on my part, but all's well that ends well.

|||

Ham and Philip,

I am having similary problem. When i am trying to get connection with my existing database, It says in task status

1- Varifying Database Edition (Pass)

2- Verifying Database Version (! Exclamitaion)

3- Creating Grant Rights for ServerName\UserName (Not Pass X)

In the browser when i am typing http://LocalServer/ReportServer That generate error that is

Reporting Services Error

---

The report server cannot open a connection to the report server database. The logon failed. (rsReportServerDatabaseLogonFailed) Get Online Help

Logon failure: unknown user name or bad password. (Exception from HRESULT: 0x8007052E)|||

Irfan,

If I remember correctly from when I was having my troubles, this may be due to ASPNET permissions on your web server.

You might google ASPNET reporting services server persmissions or something like that.

Best luck to you!

|||

Irfan,

This is stating that the "Report Database is failing" - First, verify that the login setup for Report Server logon in the Report Configuration Manager is correct.

You can verify that by connecting to the SQL2005 via studio management with the report server logon credentials

let me know if this works for you.

Configuration of Reporting Services

obviously until I get this sorted I cannot deploy my reports

in my Reporting Services Configuration

All ticked except initialisation (3)

in Database services ticked but on the right hand side there are warnings/errors

Verifying Database Edition - ticked

Verifying Database Version - warning

The current connection string is not valid and can't be used to connect to SQL Server

Where do I check this? (1)

Creating A Grant Rights script for LocalSystem = ticked

Assigning Reporting Services Rights to User = error (2)

System.Data.SqlClient.SqlException: The role 'RSExecRole' does not exist in the current database.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Common.DBUtils.ApplyScript(String connectionString, String script)
at ReportServicesConfigUI.SqlClientTools.SqlTools.ApplyScript(String connectionString, String script)

Can someone tell me what I should be checking please?

thanks

HI !!!

I am also facing the same problem, The database server is sql 2000 personal edition while reporting server is sql 2005 evaluation.

In 'Database Setup' option displayes 'success'; but 'Verify Database Version' and 'Setting Connection Info For Reporting Server' are warnings while 'Create grants rights script' task is failing.

The 'Initialization' option is also failing. The message displayed is 'The report server cannot open connection to report server database (rsReportServerDatabaseUnavailable)'

Which setting should I check in report configuration? Or is this backward compatibility issue?

|||

On your reporting database,

Make sure under Security/Roles - that the RSExecRole exist, if not create it. It should also be assigned to the schema of RSExecRole. Finally, on your report service login User make sure that this user is assigned under the RSExecRole.

I hope this helps.

Ham

|||

HI Ham,

Thanks a lot for the reply.

I'll try out the solution suggested by you and let you know the outcome after that.

-Nishad

|||

Ham,

I am having the same problem. I need extra clarification, though. When you say check under Security/Roles, are you talking about doing this on the Reporting Services server or the database engine server that reporting services is connected to?

For instance, in my Object Explorer I see my reporting services server and under that in the tree is Security -> Roles. However, when I try to right-click on anything there it gives me the following message:

Unhandled exception has occured in a component in your application. If you click Continue, the application will ignore this error and attempt to continue.

The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'Unknown'. the expected version is 'C.0.8.40'. To continue, update the version of the report server database and verify access rights. (rsInvalidReportServerDatabase).

Furthermore, when I try to upgrade the database by clicking 'Upgrade' in the Database Setup section of the Reporting Services Configuration Manager it gives me a green check on Verifying Database Edition and a yellow exclamation mark on Verifying Database Version. The warning says:

The current connection string is not valid and can't be used to connect to SQL Server.

So, if you mean I should add the RSExecRole to Security/Roles under my reporting services server, then I am in even deeper trouble than I thought. However, if you mean I should add them to Security/Server Roles under my database engine tree then I'm probably back in business.

I am loathe to try anything until I get clarification. Thanks for your kind help!

|||

Nevermind, I solved my problem.

I just needed to click the New button on the Database Setup page before trying to connect. Pretty silly on my part, but all's well that ends well.

|||

Ham and Philip,

I am having similary problem. When i am trying to get connection with my existing database, It says in task status

1- Varifying Database Edition (Pass)

2- Verifying Database Version (! Exclamitaion)

3- Creating Grant Rights for ServerName\UserName (Not Pass X)

In the browser when i am typing http://LocalServer/ReportServer That generate error that is

Reporting Services Error

---

The report server cannot open a connection to the report server database. The logon failed. (rsReportServerDatabaseLogonFailed) Get Online Help

Logon failure: unknown user name or bad password. (Exception from HRESULT: 0x8007052E)|||

Irfan,

If I remember correctly from when I was having my troubles, this may be due to ASPNET permissions on your web server.

You might google ASPNET reporting services server persmissions or something like that.

Best luck to you!

|||

Irfan,

This is stating that the "Report Database is failing" - First, verify that the login setup for Report Server logon in the Report Configuration Manager is correct.

You can verify that by connecting to the SQL2005 via studio management with the report server logon credentials

let me know if this works for you.

Configuration Help

I am running 2005 reporting services and 2005 visual studio. I have a
c# .net web application located in the default web directory in IIS
that uses forms authentication, but that authentication is to active
directory using an ldap call inside the code. We set it up that way so
the user can log in to a normal page instead of the windows pop-up
login. I have reporting services running inside the web site directory.
It is setup to use windows authentication. I have the user login into
the web site and then when they go to run a report a windows login box
appears and they have to login again. Does anyone know how I can
authenticate to reporting services in the background so the user does
not see the windows pop-up login.
Thanks in advance,
JasonWrite a FormsAuthentication extension to MSRS.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ufairs.asp
When you app hits LDAP, you can also authenticate against MSRS and grab
the auth cookies for MSRS. Your other options is to hijacks the
ASP.NET pipeline when a user requests an MSRS report and perform the
auth on demand.
Andy Pottersqlsql

Configuration files do not work in CmdExec mode

I have created an Integration Services package on my development machine. The package contains a configuration file witch let's say is stored in c:\projects\MyIntegrationServicesProject\myConfigfile.dtsConfig (on my dev machine).

Then I have another "Production" machine where I import the SSIS package into an SQL database. I then create an sql-job with only one step, to run my SSIS package. This works fine if I configure the step to be an "SQL Integration services package" and configure it to use my configurationfile.

However I would like to configure this package as a CmdExec step. In the commandline, I specify /CONFIGFILE "d:\....\myConfigfile.dtsConfig" (the correct path on the prod machine). But it seems to be ignored, because when I execute the package I get an error telling me that the configuretion file c:\projects\MyIntegrationServicesProject\myConfigfile.dtsConfig cannot be found.

What I try to say is, it seems like it ignors the config-file I specify on the command-line and tries to reach the config-file on a location that's probably stored somewhere in the SSIS package from the time it was created on my development machine.

Is there a way around this?

Does anyone know if this is the correct behaviour of a SSIS package? Shouldn't the config file in the command line overrule any command-line inside the package?|||Can't help with an answer, but curious as to why you need to use CmdExec instead of Integration Services Package?

Greg.|||

Hi GregAbd!

The reason to why I need CmdExec is that running it as an Integration Services Package doesn't give any good output if something goes wrong. And, I also have the problem that my package takes twice as long time if I run it inside a job as if I run it "manually". I'm curious to know if this changes if I run it in the job, but as a cmdexec task.

Regards Andreas

|||Can't help with the run time issue, but you can enable logging in your package - you can literally log everything that happens. Just right-click on the package (i.e. a blank area of the Control Flow) and choose Logging. You can add a Log Provider for Text Files, create a File Connection Manager, and choose which events you want logged.

You can then configure the Log file location from the Data Sources tab on the SQL Agent Job Step.

Greg.|||

You have set a config in your package, the command line allows you to add configurations in addition to the one in the package, it does not change the package structure.

There are similar issues covered in the answers here-

Re: Implementing IDTSLogging - MSDN Forums
(http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=816241&SiteID=1)

Configuration Error on Encryption Key

I came across an issue while configuring SQL Server 2005 Reporting Services. In the reporting services configuration tool, there is a step the either Backup, Restore, or Change the encryption key for the new Reporting Services instance. I backed up the key since this is the first installation of SQL Server and Reporting Services on this machine.

I had expected that the configuration would then allow me to progress to the next step in the configuration, "Initialization". But regardless of how I handle the Encryption Key step, the option to move "Initialization" does not become available.
Has anyone had this happen to them? Or better yet, does anyone have a solution to this?

The report server only needs to be manually initialized if you are connecting the server to a web farm. It is needed to ensure that all nodes in the farm are using the same encryption key. In your case, since you have backed up the key, the report server is already initialized. The option is likely disabled because you are using an edition of SQL Server that does not support web farms (it is only available in enterprise and developer editions).|||Yep, I found that to be the case yesterday while trying to troubleshoot this.
I was checking on another server trying to find where the setup went wrong and determined that the issue is actually that the Initialization step didn't become available as it should. There must have been an error in one of the preceding steps that didn't show up as it should have.
At this point I am going to try and run the command-line initialization to complete the setup. And if that doesn't work, I'll probably have to uninstall and re-install Reporting Services.

Configuartion & Deployment

Please kindly recommend good links/tutorials on configuration & deployment of reporting services.
Thanks much.

I posted the script required to deploy reports and other dependant files here ...http://www.bassicsoftware.com/CodeLibrary.aspx?searchcategory=29
Click on"Publish Reports to SQL Reporting Services"
Sorry for not having place much comments in the code

|||Hey i wanna know if there is any way that you can specify in the deployment script, the data source that your reports will be using.
Thanks|||The datasource should be defined in your RDL file.
btw, you can edit the rdl file using Notepad.|||One more thing...Does the datasource have to be in the same folder as the .rdl file or not.
If not then how can i give that in my .rss file.|||In the publishReports.rss you only specify the datasource by name.
In order to setup your datasources on the server (Local or Remote) you can do this manualy or create an rss script to publish them similiar to the publishReports.rss.
If I have time I'll post a sample of the script.|||Thanks,
You were right, the data source has to be defined in the .rdl file. there is a <datasourcereference> tag in the .rdl file that you need to edit in order to specify a datasource that is not in the same folder as the .rdl file, because, by default it is configured for data sources that are in the same folder. Still i havent found any way of specifying data sources on the fly at the time of deployment, and i dont think there is any...
Thanks Anyways...|||Note that a data source doesn't always mean referencing an RDS file, you may use a shared datasource.

Config Help for Server2003/sql2000/iis6 Reporting Services

Newbie alert. Need some configuration help please. Have web server with 3
websites on box above with same ip using host headers (well-established sites
working fine). Installed reporting services, went to one website and created
virtual directories with IIS to same folder/name as items under Default Web
Site. Authentication is set to IUSR_svrname, Integrated Windows on website
and virtual directories.
Created a basic report & deployed to http://serverName/reportServer. I can
view report by url similar
to:http://serverName/Reportserver?%2fFolderName%2freportName&rs:Command=Render Data connection and report retrieval is fine.
But, I need folks to be able to enter the www.domainname.com type of url and
that gives me a HTTP Error 403.8 - Forbidden: DNS name of the client is
rejected. As soon as I remove the report services part of the url, site
pops up fine. And, if possible, I'd like to be able to use more than one of
my domains, just referencing the virtual directory in IIS for each site.
Also, I cannot get to the http://serverName/Reports/Pages/Folder.aspx to set
roles and permissions because it only sees me as IUSR and I can't "see"
anything under that role. Even though I'm a domain administrator, believe
it's seeing me as IUSR.
Tried a few things but may be making it worse. Thanks for any advice-would
really like to be able to use this tool, but can't at this point.Update - think I'm really close, but still need help. Uninstalled,
reinstalled RS. Deployed sample. Added <ReportServerExternalURL>
http://www.domainname.org/Reports </ReportServerExternalURL> to
rswebapplication.config Deployed.
Created new role for IUSR_MachineName and gave it browse capabilities on
home folder.
Added virtual directory is IIS under web site above for Reports, with
documents of home.aspx and enable IUSR in IIS for directory. (Deployment is
still okay.) If I view Reports via machinename, site settings are there.
Browse to folder, click on report, the folder pops up again. Report will not
generate. If I view via www, site settings are not there (good, good).
Report does the same thing - popping up another folder view but not
generating.
Added virtual directory is IIS under web site above for ReportServer, with
document sof default.htm, default.asp, index.htm, iisstart.htm, default.aspx.
Same thing happens again.
Think I'm very close. Anybody?|||Finally got a reply on another forum so thought I'd pass it along. Even
though the documentation explicitly says:
"To access a report server on the Internet, you must add a
ReportServerExternalURL configuration setting that specifies the fully
qualified domain name of the report server. The external client uses this
value to access a report server. In this scenario, the browser that is
hosting Report Manager must send a fully qualified domain name when making
requests on behalf of Report Manager to the report server. The following
example illustrates the syntax for ReportServerExternalURL:
<ReportServerExternalURL> reports.adventure-works.com
</ReportServerExternalURL>"
When I replaced the UI with the following, things started rendering properly.
<UI>
<ReportServerVirtualDirectory>/ReportServer</ReportServerVirtualDirectory>
</UI>
Hope it helps out someone else.

Confguring Report Builder

--Hello All ,
I am having a problem generating data models with SQL Reporting services
2005 from the web .I installed and configured 2005 Reporting services ,
created few reports and gave access to the users to view the reports from the
browser .Everything goes well till this point . But I don't see the "Generate
Model" button to generate a model from my current Data Source and use it in
the Report Builder feature from the web.I am trying to set this thing right
as it gives the end users to build their own reports from the web using
Report Builder.
Does it have to do with the security feature of Reporting Services ? If so
how can I configure the accounts ? [ I am using windows 2003 server with
IIS 6.0 and SQL Server 2005 CTP ]
Any help is highly appreciated .
thank you
K.This is controlled through the "Manage models" role, similar to all the
other RS security roles (e.g. "View reports", "Manage reports", etc.). Here
is a starting point for managing role level security in RS 2005:
http://msdn2.microsoft.com/ms228284(en-US,SQL.90).aspx
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kallis" <Kallis@.discussions.microsoft.com> wrote in message
news:B9D96877-14FC-44FC-A99F-B2E63566E501@.microsoft.com...
> --Hello All ,
> I am having a problem generating data models with SQL Reporting services
> 2005 from the web .I installed and configured 2005 Reporting services ,
> created few reports and gave access to the users to view the reports from
> the
> browser .Everything goes well till this point . But I don't see the
> "Generate
> Model" button to generate a model from my current Data Source and use it
> in
> the Report Builder feature from the web.I am trying to set this thing
> right
> as it gives the end users to build their own reports from the web using
> Report Builder.
> Does it have to do with the security feature of Reporting Services ? If so
> how can I configure the accounts ? [ I am using windows 2003 server with
> IIS 6.0 and SQL Server 2005 CTP ]
> Any help is highly appreciated .
> thank you
> K.|||Robert,
Thank You for responding to my question . I gave the 'Content Manager' role
all the permissions inside Reporting server roles . From the browser , I
assigned the 'Content Manager' role to a group { local to the server where
IIS is installed}.Even then I am not able to view the 'Generate Models'
button besides 'Apply',Move & 'Delete' buttons . In addition to the above ,
when I run a report from report builder I get he following msg 'An attempt
has been made to use a data extension 'OLEDB-MD' that is not registered for
this report server'. How can I overcome this issue ?
Thanks for helping !!
-K
--
K
"Robert Bruckner [MSFT]" wrote:
> This is controlled through the "Manage models" role, similar to all the
> other RS security roles (e.g. "View reports", "Manage reports", etc.). Here
> is a starting point for managing role level security in RS 2005:
> http://msdn2.microsoft.com/ms228284(en-US,SQL.90).aspx
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Kallis" <Kallis@.discussions.microsoft.com> wrote in message
> news:B9D96877-14FC-44FC-A99F-B2E63566E501@.microsoft.com...
> >
> > --Hello All ,
> >
> > I am having a problem generating data models with SQL Reporting services
> > 2005 from the web .I installed and configured 2005 Reporting services ,
> > created few reports and gave access to the users to view the reports from
> > the
> > browser .Everything goes well till this point . But I don't see the
> > "Generate
> > Model" button to generate a model from my current Data Source and use it
> > in
> > the Report Builder feature from the web.I am trying to set this thing
> > right
> > as it gives the end users to build their own reports from the web using
> > Report Builder.
> >
> > Does it have to do with the security feature of Reporting Services ? If so
> > how can I configure the accounts ? [ I am using windows 2003 server with
> > IIS 6.0 and SQL Server 2005 CTP ]
> >
> > Any help is highly appreciated .
> >
> > thank you
> >
> > K.
>
>

Conections

Hi I'm very new in this stuff, i have a question, how can i connect mi db, or what services i need to connect my db remotly, i have a Static IP, a Sql Server Developer Edition, so the thing is i need to acces to db wherever i go via Internet, i can acces from the same Network so how can i do for acces external?SQL Server does accepts remote connections over the internet. For SQL Server 2005 you can use the Surface Area Configuration tool to ensure this, see http://msdn2.microsoft.com/en-us/library/ms178763.aspx. Also make sure that the firewall (if you have any) allows SQL Server application to access the internet.|||There is a thread discussing a similar issue. Look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1144922&SiteID=1 for more information.sqlsql

Conditional sum in reporting services

I need to find a conditional sum of sales if fields!Example.value = 0
(And same if fields!Example.value = 1)
How do I do that in reporting services?
Thanks
CS=Sum(IIf(Fields!Example.value=0,0,Fields!Example.Value))
"chetanasamal@.gmail.com" wrote:
> I need to find a conditional sum of sales if fields!Example.value = 0
> (And same if fields!Example.value = 1)
> How do I do that in reporting services?
> Thanks
> CS
>