Microsoft SQL Server offers the ability to export data into CSV, Excel, PDF and other formats with the help of xp_cmdshell in T-SQL.

While typically the size of the file depends on the amount of data that is exported, when furnishing to domain-specific, the size of the data export will be larger in size than normal. This requires more server-side resources, such as CPU, memory, file system, etc., and may cause issues on the end-user side. If the file size is much larger than needed, it will take longer to download. While download time depends on the end user’s internet bandwidth, it is much more dependent on the server side’s configured resources:

The prevalent way to store and share sets of multiple files is to use the ZIP or RAR file format. The ZIP or RAR command compresses one or more files and bundles them into a single file. The most modern operating systems, including Microsoft Windows, MAC OS, and Unix/Linux, can handle ZIP files. If you use a Linux operating system with SQL Server, you may want to use the Unix zip command-line tool to manage these files.

If bandwidth is limited, creating a ZIP or RAR file will allow faster downloads and will reduce the chance of data corruption. In this article, we’ll show you how to use T-SQL fundamentals to compress files to achieve various output requirements using xp_cmdshell (T-SQL) in SQL Server.

xp_cmdshell

A generated Windows command shell is passed in a string for execution by SQL Server. Looking at it closely, we see that it is an extended stored procedure, provided by Microsoft and stored in the master database. This stored procedure allows you to pass the operating system commands directly to the Windows command shell with the help of T-SQL code.

In this case, the output of this command must be returned to the calling routine as rows of text. SQL Server can block access to procedure ‘sys.xp_cmdshell‘ by component ‘xp_cmdshell‘ because this component is turned off as part of the security configuration. A database administrator or system administrator can enable the use of ‘xp_cmdshell‘ by using sp_configure in SQL Server.

Enable xp_cmdshell SQL Server

Using the above T-SQL command, we can enable the xp_cmdshell to use further Windows commands inside SQL Server. If a user does not have adequate permission to execute the above code, then it will return an error: “User does not have permission to perform this action”. If the user does have permission to perform this action, it will return an affirmative response.

Compress file or group of files with ZIP or RAR

You can use a stored procedure to create the zip files; however, you would be writing T-SQL which writes files to disk and executes Windows system commands as well. You could read up on xp_cmdshell, but you’ll still have a large zip file coming back to your server in that model. You may ask, couldn’t the users still overload your system? Yes, but you can get around this issue by using streaming, which can be done with the zip files you create.

Windows does not come with a command-line zip program, so I recommend the zip utility, WinRAR, which includes a command-line executable and supports many various archive file types. You can also use other open-source Windows utilities in a similar manner:

• Download WinRAR
• Open a CMD using SQL Server xp_cmdshell and use different parameters to compress the files

Add file to archive using xp_cmdshell

When adding a file to the archive, WinRAR supports the “a” parameter within the command statement. The command can be segregated into four distinctive parts, Utility Path, WinRAR parameter, Destination Address, and Source Address:

In actual scenarios, files are generated from SQL Server with a reformulated location and unique filename. SQL Server compresses the files into ZIP or RAR formats using a function or procedure. The T-SQL statement shown below will archive the file:

My Source File path is: V:\FileCompress\Source Directory\1109_2019-09-23 155716.140.csv

Expected Destination File: V:\FileCompress\Destination Directory\1109_2019-09-23 155716.140.RAR

Here, the “a” is a parameter to add a file for Archive. As a result, the file is successfully archived with the RAR extension with just a 31 KB size.

Now, the problem is that the archived file has been placed in the RAR Directory as a Source location (FileCompress\Source Directory). Therefore, “-ep1” needs to be added with “a” to avoid use of the RAR base directory, as shown in the T-SQL statement below:

EXEC master.dbo.xp_cmdshell ‘””C:\Program Files\WinRAR\Rar.exe” a -ep1 “V:\FileCompress\Destination Directory\1109_2019-09-23 155716.140.RAR” “V:\FileCompress\Source Directory\1109_2019-09-23 155716.140.csv””‘

We can check in below snap, the file is directly populated in the base, and not within the folder:

Archive multiple files into single ZIP or RAR

To use the ZIP or RAR commands for multiple files, we can include as many filenames as needed in arguments within the command line. We can include multiple files to single compressed ZIP or RAR format in SQL Server with xp_cmdshell:

Syntax:

For example, we have 2 files 1109_2019-09-23 155716.141.csv & 1109_2019-09-23 155716.140.csv on directory address “V:\FileCompress\Source Directory\” and want to compress them to file 1109_2019-09-23 155716.140.RAR in directory “V:\FileCompress\Destination Directory”. We would use this command:

Use double quotes to avoid space issue in source and destination directories

Any of these utilities will return an error when there is a space character in the source or destination folder name or file name. Using “” (double quotes) will avoid the error. Above, we have “” around both the source (“V:\FileCompress\Source Directory\1109_2019-09-23 155716.140.csv”) and destination files (“V:\FileCompress\Destination Directory\1109_2019-09-23 155716.140.RAR”).

WinRAR Parameters

Different utilities have different command-line parameters to invoke various features. For WinRAR, we have a list of parameters below that can greatly expand the functionality of the utility. You can generate a list of these using either of the following commands:

C:\Program Files\WinRAR>RAR –help

Refer to the list below for the commands and related switches for the WinRAR utility:

Commands:

Switches:

Conclusion

Client-specific requirements or bandwidth optimization will determine whether you use file compression. If you do need to compress files within SQL Server, we’ve shown you how to write a simple program using the xp_cmdshell command in T-SQL that can be used with a number of parameters.

• Author
• Recent Posts

Jignesh Raiyani

Jignesh has good experience in Database Solutions and Architecture, working with multiple customers on Database Design & Architecture, SQL Development, Administration, Query Optimization, Performance Tuning, HA and Disaster Recovery.

View all posts by Jignesh Raiyani

Latest posts by Jignesh Raiyani (see all)

• Page Life Expectancy (PLE) in SQL Server - July 17, 2020
• How to automate Table Partitioning in SQL Server - July 7, 2020
• Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020