How to get sql query result into a text file with a specific file name like name_dd-mm-yyyy.txt.

Question

Hello Everyone,

Please suggest me how to get sql query result into a text file with a specific file name with current date like name_dd-mm-yyyy.txt.I want do this automatically using a job or Stored Procedure.

Answer 1

BULK INSERT

Sample XML manifest documents

Answer 2

There are probably two "best" ways to go here, either with Integration Services, where you have a text file output, or with the BCP utility in batch files and/or stored procedures.

I used to manage processes where we dynamically generates the BCP OUT command within a stored procedure and passed that to xp_cmdshell to output was needed into files. It was a legacy process, before SSIS, but it worked for what we needed.

Are you able to use SSIS or do you need a batch process? I can provide some code samples if you're in the batch direction.

EDIT - Here are the script samples.

First, I'll assume you already have a stored proc with the data you want exported, so I'm going to focus on the batch file.

Here is a convenient way to get the date string you're looking for:

  REM  Creates a string in the form of YYYYMMDD-hhmm, with no embedded spaces
SET hh=%time:~0,2%
IF "%time:~0,1%"==" " SET hh=0%hh:~1,1%
SET DateString=%date:~10,4%%date:~4,2%%date:~7,2%-%hh%%time:~3,2%

You'll use this later to name the file.

If you have just one query that needs to be called from one batch file, you could use this:

BCP "exec dbo.MyExportProc" queryout Export_%DateString%.csv -S (local) -T -m0 -t^|

This will run dbo.MyExportProc and create a file Export_YYYYMMDD-hhmm.csv using the pipe | as the delimiter. The -T specifies NT Authentication and -m0 specifies the number of errors that are allowed. -S is the server to connect to.

As with batch processes, you can parameterize this and generate a list and have it run through the list and BCP out a whole group of queries:

FOR /F "tokens=1-2 delims=~" %%A in (RunList.txt) do (
      BCP "%%A" queryout %DateString%.csv -S (local) -T -m0 -t^|
 )

To run this from within SQL Server, you would just need to generate the command line text, as noted above, and execute something like the following:

declare @command varchar(max)

set @command = 'BCP "exec dbo.MyExportProc" queryout Export_' + CAST(CONVERT(DATETIME, GETDATE(), 101) AS VARCHAR(21)) + '.csv -S (local) -T -m0 -t^|'

exec xp_cmdshell @command 

You'll need to make sure you have access to run xp_cmdshell, which is managed within the Surface Area Configuration.

Hopefully this gets you on the right track!