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
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- Understanding PostgreSQL SUBSTRING function - September 21, 2024
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023