SSRS report with mirrored data source

Question

question

DSPatrick asked Oct 20, '20 DSPatrick answered Oct 22, '20

SSRS report with mirrored data source

I have an SSRS report server running on SQL Server 2016. There is a report that has a shared data source connection to another SQL server. Report works fine, no issues. They plan to setup mirroring on this data source so I'll add the Failover Partner to the shared source connection string. Just to be clear the report server itself will not be mirrored.

I was wondering if there's a simple method to display on my report at runtime the server name it connected to? Just wanting to be able to see on report did I connect to Primary or to Mirror?

sql-server-reporting-services

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2020-10-21T03:43:28.943Z

ZoeHui-MSFT answered Oct 21, '20

Hi @DSPatrick,

I could only find a way to meet your needs and it will show the server name in the report. In other words, you need to modify the report.

1.Create a new dataset in the report with

SELECT @@SERVERNAME AS ServerName

2.Add the field to the report like shown.

And then you may see which server has been connected.

Regards，

Zoe

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

screenshot-2020-10-21-114130.jpg (26.7 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2020-10-22T00:53:57.537Z

DSPatrick answered Oct 22, '20 ZoeHui-MSFT commented Oct 22, '20

Thank you. I'll be testing this shortly. I didn't have a mirror to test with so I set it all up today and will test this in the next day or two.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ZoeHui-MSFT · Oct 22, 2020 at 06:31 AM

Thanks for your reply. Hope it'll work.

0 ·

Answer 3 · 2020-10-22T20:50:08.11Z

DSPatrick answered Oct 22, '20

This is going to work perfectly. Thanks for your assistance.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question