How to run data export from a SQL stored procedure

To export data using a SQL stored procedure with dbForge Data Pump for SQL Server, you need to execute its command-line utility. However, SQL Server stored procedures do not have built-in support for running external command line utilities. As a workaround, you can use the xp_cmdshell extended stored procedure to run the dbForge Data Pump command-line utility from within SQL Server.

Important

xp_cmdshell can pose security risks and is often disabled by default. Make sure that proper security measures are in place before you enable and use the extended stored procedure.

This guide provides steps on how to run data export from a stored procedure that uses xp_cmdshell.

Step 1: Create a template to export data

1. First, you need to configure the settings to be saved as a template. To start this process, in Object Explorer in your SQL Server Management Studio (SSMS), right-click a database, point to Data Pump, and click Export Data.

2. Go through all wizard pages and specify the necessary information and settings.

Note

The actual configuration of settings may differ, depending on the format. You will find the full workflow for each format in the corresponding topic:

• HTML
• Text
• Microsoft Excel (XLS)
• Microsoft Excel 2007+ (XLSX)
• Microsoft Access
• RTF
• PDF
• JSON
• XML
• CSV
• ODBC
• DBF
• SQL
• Google Sheets

3. After you have configured all necessary settings, click Save Template on the needed wizard’s page.

Specify the name and the location of your template file in the Save As dialog and click Save.

Step 2: Create a batch file

In Notepad or any text editor, modify the script to export data as follows:

• Type a path to the datapump.com file located in the dbForge Data Pump for SQL Server installation folder on your PC.
• If you did not set a security password to create a database when configuring the export template in Export Wizard, add the /password parameter to the script.

"C:\Program Files\Devart\dbForge SQL Tools Professional\dbForge Data Pump for SQL Server\datapump.com" /dataexport /templatefile:<filepath> /password:<yourpassword>

• The arguments should be substituted with your actual data:

  • /templatefile: Full path to the template .det file you created in the Data Export wizard, for example, C:\SampleFolder\SampleSubfolder\TemplateFile.det.
  • /password: The password you set for the data export.

Step 3: Enable the xp_cmdshell extended stored procedure

If the xp_cmdshell extended stored procedure is disabled in your SQL Server instance, you can enable it by running the following commands in SSMS:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

Step 4: Create a stored procedure

Next, you need to create a stored procedure that uses xp_cmdshell to execute the batch file. Replace C:\SampleFolder\SampleSubfolder\BatchFile.bat in the following query with the actual path to your batch file:

CREATE PROCEDURE dbo.RunDataPumpExport
AS
BEGIN
    EXEC xp_cmdshell 'C:\SampleFolder\SampleSubfolder\BatchFile.bat';
END;

Step 5: Execute the stored procedure

Finally, execute the created stored procedure to run the data export:

EXEC dbo.RunDataPumpExport; 

After executing the stored procedure, navigate to the destination folder and confirm that the export file containing the necessary data has been created:

For more information about using the command line for data import with dbForge Data Pump, see Exporting data from the command line.

For more information about scheduling data imports, see Scheduling data export.