Blog Archives
Moving SSRS Reports
Yesterday I was tasked with migrating our Citrix EdgeSight database off of a 2005 SQL Server and migrating it to a new 2008R2 Server. No problem, I have done that a million times before. But wait a minute, that database has SSRS reports installed on that server and not our main reporting server. If you follow my blog, you will probably see that I have not had much experience with SSRS, my reporting experience has been with Crystal and other minor packages.
I started researching how to migrate these reports and was not pleased to see Citrix’s response. They recommended migrating the ReportServer and ReportServerTempDB databases to the new server and then reinstalling SSRS on the original server. So what happens then if you have other jobs on the new and old servers? This is not a good recommendation. After an exhaustive search the only option I saw within the tool was to download reports one at a time and upload them into the new server. This was also not an option with a fair amount of reports to migrate.
Enter Reporting Services Scripter from SQLdbatips.com. Run the program on the original server and select your Report folder and scripts are generated and placed into a folder of your choosing. These scripts can then be copied over to the new server and executed by command shell. In this particular instance we had to reopen the data source and enter new credentials. Problem solved, reports migrated successfully.
Enjoy!
SQL Server Auditing: A Learning Series Part Nine, Reporting
Continuing our learning series on auditing, today we look at the next logical step: Reporting. We have created audits for the server and for the database as well as being able to query the audits, but what good is an audit if you cannot print a report and hand it to the auditors?
Having been a developer and admin in my past life, you may find it strange that I have never before today created a SQL Server Reporting Services report. Now don’t get me wrong, I have written a boat load of reports in many other formats (crystal, web, actuate, etc) but have never really had the opportunity or the pleasure to write in my preferred environment.
Given the following function, I thought reporting would be a little more difficult:
SELECT * FROM fn_get_audit_file (‘g:\auditing\*’, DEFAULT, DEFAULT)
This arguments for this function are (file_pattern, initial_file_name, audit_file_offset). The file_pattern argument must include a path and a filename but can include a wildcard. The initial_file_name argument must specify the path and name of a specific file in the file set to start reading the records from or the DEFAULT|NULL value. The audit_file_offset argument will beginning reading the first record after the specified offset (which is a field that can be queried with the function).
Now on to the report….
- Open Visual Studio and choose File –> New Project.
- Select Report Server Project Wizard and name the project appropriately.
- Enter your connection information and credentials.
- When prompted to design the query enter the query above as shown in Figure 1.
- Select additional reporting options as your preferences allow and then save the report.
Figure 1 – Report Wizard Query String
This concludes our series on auditing, I hope you found it helpful!
SQL Swampland 