How to get a SSRS report exported in multiple excel sheets

Hi,

In this post, we will address an issue relating to exporting a SSRS report.

One of my client had a report which showed data from 2 different Datasets in one Report. Everything looked fine to him, by the time he didn't exported it, since the data from 2 different datasets was imported in the same excel sheet.

To resolve the issue, all we really need to do is set a simple setting. You must be using 2 Tablix controls in your report to show the data. Just go to the properties of the first Tablix and set the value of Page Break -> Break Location to End. The default value should be null.

Figure 1 - Default Setting                                    Figure 2 - Changed Setting


Now when you will export the Report data. It will be exported to 2 different sheets in the Workbook. You can do the same for multiple Tablix in the same reports :-). Cool Huh...

Expired Subscriptions in SQL Server

Hi all,
Currently I am working on a huge setup of servers which utilizes replication quite extensively. Each of these servers has about 4 to 10 publication and around 10 to 40 Subscribers running 24x7. An issue, I was facing quite regularly on many subscribers was that they tend to expire and then the second step of the replication job simply goes in the retry mode.
I investigated the issue by first finding the replication and then going into one of the tables in 'Distribution' database of my distributor. The table is dbo.MSsubscriptions. It is a very usefull table with the complete info about all the Subscriptions configured on this distributor. It contains the following columns of our interest -
publisher_database_id, publisher_db, subscriber_db and status
if you look for the value of the status column corresponding to the right Publisher and Subscriber Database, it would be either 0, 1 or 2.
Status 0 = Inactive,
Status 1 = Subscribed and
Status 2 = Active.
Now all you have to do is to change this value for it to start working normally. You can use a simple update statement like this -
UPDATE distribution.msdb.MSsubscriptions
SET status = 2
WHERE publisher_db = 'MyPubDB' and
subscriber_db = 'MySubscriberDB' and
status = 0
After running this query wait for a couple of minutes for the replication job to retry and it should be running smoothly. I am still trying to find out the way to completely eliminate this problem so that subscriptions do not expire at all. Will update you soon on that. In the meantime, if you have a solution, you can post it in the comments.
Hope it helps.

sp_replcmds Error

Hello all,
I was setting up a transacitonal replication today, when I came accross this error with my LogReader Agent. Both the Publisher and Subscriber setups gave me a success message and then when I checked the data on the subscriber, it wasn't there.
I tried to find the reason for that with the help of job logs and the error it gave me was ---
"The process could not execute 'sp_replcmds' on 'MyMachineName'.
Status: 0, code: 15517, text: 'Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. '

The actual solution to the problem was far away from what it reflected in the error messsage. The real problem was that my published database did not had any owner assigned to it. (and may be SQL Server is using the owner account to execute sp_replcmds in the background which if doesn't exists, defaults to dbo. SQL Server does it quite often!!!!) As soon as I assigned the owner to my database, stopped the service and started it again.... bingo... it started working.
Hope, the post is going to be usefull for you as well.