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 开发者_JS百科want do this automatically using a job or Stored Procedure.
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!
BULK INSERT
Sample XML manifest documents
精彩评论