Rajendra Gupta

Viewing SQL Server FILESTREAM data with SSRS

February 12, 2019 by

Importing and exporting

SQL Server FILESTREAM stores objects into the file system and applications can retrieve these objects to benefit from the IO streaming capability of the Windows OS. In my earlier articles we used the SSIS package to import and export data from the a FILESTREAM table

  • Import objects into SQL Server FILESTREAM table
  • Export objects to file system from SQL Server FILESTREAM table

Displaying

When we query a FILESTREAM table using t-SQL, we cannot see the objects. In the below screenshot, you can notice that it shows the metadata for that particular object. As an alternative to display the objects from the FILESTREAM tables, we can use SSRS.

Requirements

We have the below requirements for this article

  • We should have SQL Server instance with enabled FILESTREAM feature
  • FILESTREAM table with the image files into it
  • SQL Server Data Tools (SSDT): If you do not have SSDT tools installed, go to the Download and install SQL Server Data Tools (SSDT) for Visual Studio and install SQL Server Reporting Services
  • We will assume that you have done the SSRS configuration

Steps to prepare the SSRS report to display FILESTREAM images

  • Launch Visual Studio 2017 (SSDT)

  • Click on ‘Create New Project.’

Select the ‘Report Server Project Wizard’ from the Business Intelligence -> Reporting Services. You can provide a relevant name for the SSRS report and select the appropriate location.

It creates the solution in the directory and opens the SSRS Wizard.

In the next page, we need to define the data source. The Data Source needs to point out to the FILESTREAM database with the required authentication method.

Enter the details in the connection manager (instance name, Windows or SQL authentication) and database name.

Click on the ‘Test Connection’

We have created the data source now.

In the next page, we need to specify the query for the SSRS report.

Click on the ‘Query Builder’, and it opens the Query Designer. In this wizard, we can select the tables or the stored procedure. We have already had a query for this, therefore, click on the ‘Edit as Text’

Enter the t-SQL to retrieve the records from the FILESTREAM table and execute it from the run icon as shown below.

Click on ‘OK’ to return to report wizard.

Click on the ‘Next’ and select the report type as ‘Tabular’

In the next page, we need to design the SSRS report table to display the required fields.

In this article, we do not want to create any group from the available columns, therefore, drag all the columns into the details page.

You can view the report summary with the data source and data set information in the next page. Earlier we have given the name to the solution; now we can assign an appropriate name for this SSRS report.

You can preview the report here, as well, however we need to do further customization so click on the ‘Finish’. Below is the layout you can see in the SSRS report.

Drag the border of the table to give it an appropriate look.

Drag the ‘Image’ from the SSIS toolbox to the ‘DocumentBin’ column.

It opens the image properties window.

Enter the following details in the image properties window.

  • Name: Enter the appropriate name, I named it as ‘FILESTSREAMImages.’
  • Select the image source: Select the Image source as ‘Database’ from the drop-down
  • Use this field: We have images metadata in the FILESTREAM table in the ‘DocumentBin’ column, therefore select this field from the drop-down
  • Use this MIME type: In this, we can select the image format. We can use various formats such as JPEG, PNG, GIF etc

Click on ‘OK’, and you can see a small image icon in the ‘DocumentBin’ column.

Click on the ‘Preview’, and you can see the images from the FILESTREAM table.

In this screenshot, you can notice that image size is not equal. It does not give an excellent view of the SSRS report. Therefore, click on the ‘Design’ again to do further customization.

Right click on the ‘DocumentBin’ column and select image properties.

In the image properties, go to size. In this page, you can change the display size and the padding options for the images. As you can see by default, it displays the images as ‘Fit Proportional’ which means that SSRS will resize the images inside the items while maintaining the aspect ratio. You can also set the padding option here. We will leave the padding option as default.

We want to display all the images in a fixed size, therefore, select the option ‘Fit to Size’. It will resize the images to fit inside the item width.

Note: You should not change the display option as ‘Original Size’. It will display the complete image as per the original size therefore if we have large size images, the report will not be able to show correctly with other items.

Click ‘OK’ and preview the report again.

In the above screenshot, you can see that images size is equal for all. It gives a nice look to the SSRS report.

Let us do further customization. Currently, the report tile is displayed in a white background. We can change it to the desired colour from the properties.

Change the background color as per the requirement.

Now, we will deploy the report to the SSRS. In the SSDT, right click on the solution and properties

It opens the below configuration page in which we need to provide the ReportServer URL.

We need to provide the Report Server URL configured in the Report Server Configuration Manager.

Modify the TargetServerURL in the configuration page.

Now right click on the Solution and deploy it.

You can see the deploy progress in the Output window. It first builds the project and then deploys it on SSRS portal.

Now we can open the SSRS report folder in the SSRS report portal (You can check the URL from the Reporting Service Configuration Manager->Web Portal URL)

Click on the folder, and you can see SRS report inside it. Click on the SSRS report to execute it.

You can see the SQL Server FILESTREAM images using the SSRS report.

Conclusion:

In this article, we explored a way to view SQL Server FILESTREAM images using SSRS. We can use a custom application to view the files such as videos, documents etc. We will continue exploring the SQL Server FILESTREAM with additional articles in this series so please stay tuned.

Table of contents

FILESTREAM in SQL Server
Managing data with SQL Server FILESTREAM tables
SQL Server FILESTREAM Database backup overview
Restoring a SQL Server FILESTREAM enabled database
SQL Server FILESTREAM database recovery scenarios
Working with SQL Server FILESTREAM – Adding columns and moving databases
SQL Server FILESTREAM internals overview
Importing SQL Server FILESTREAM data with SSIS packages
SQL Server FILESTREAM queries and Filegroups
Viewing SQL Server FILESTREAM data with SSRS
SQL Server FILESTREAM Database Corruption and Remediation
Export SQL Server FILESTREAM Objects with PowerShell and SSIS
SQL FILESTREAM and SQL Server Full Text search
SQL Server FILESTREAM and Replication
SQL Server FILESTREAM with Change Data Capture
Transaction log backups in a SQL FILESTREAM database
SQL FILESTREAM Compatibility with Database Snapshot, Mirroring, TDE and Log Shipping
SQL Server FILETABLE – the next generation of SQL FILESTREAM
Managing Data in SQL Server FILETABLEs
SQL Server FILETABLE Use Cases

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

128 Views