Skip to main content

How to Create SQL Job for Saving Query Results in TXT File

Viktória Seprenyi
  • Updated

With the help of the following SQL Server Agent job, query results can be exported to a txt file.

1. Create an SQL Server Agent job

Create a new SQL Server Agent job. Adjust its parameters then add the following script to the ‘Command’ field of the step:

--USE [SBO_DATABASE]
-- Save records to a file:
DECLARE @fileName VARCHAR(100)
DECLARE @sqlStr VARCHAR(1000)
DECLARE @sqlStrDocnum VARCHAR(1000)
DECLARE @sqlCmd VARCHAR(1000) 
SET @fileName = 'C:\Produmex\Interface\testExportTVS.txt'
SET @sqlStr = 'select TOP 10 * from [SBO_DATABASE].dbo.OCRD'
SET @sqlCmd = 'bcp "' + @sqlStr + '" queryout ' + @fileName + ' -w -T'
EXEC xp_cmdshell @sqlCmd

In the example we used the following parameters:
Database: SBO_DATABASE
Query: 'select TOP 10 * from [SBO_DATABASE].dbo.OCRD'
Export file path: 'C:\Produmex\Interface\testExportTVS.txt'
The text file will contain the first ten records from the Business partners table.

1.1. Job error

When executing the job, you might get an error:

1.png

To see the error message, open the history log by selecting the ‘History’ option from the right-click menu of the job.

The error message:

SQL Server blocked access TO PROCEDURE 'sys.xp_cmdshell' OF component 'xp_cmdshell' because this component IS turned off AS part OF the security configuration FOR this server. A system administrator can enable the USE OF 'xp_cmdshell' BY USING sp_configure. FOR more information about enabling 'xp_cmdshell', SEARCH FOR 'xp_cmdshell' IN SQL Server Books Online.

In this case run the following query on the database:

EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

2. Execute the job

Run the job or create an automatic schedule for it on the ‘Schedule’ tab of the job.
The txt file will be exported to the defined folder.

Example: First ten records from the Business partners table of the ‘SBO_DATABASE’ database.

txttxt

Related articles

Comments

0 comments

Please sign in to leave a comment.