There are several ways to write data to a file from a Microsoft SQL Server database. Some of them include complex code using the Office Automation (OA) components. However, I like the simplicity of employing BULK COPY (BCP). The code is relatively simple to write, requires fewer lines of code, and executes fast.
While BCP can be called from a command-line (cmd.exe), I find it much more challenging to employ and manipulate complex variables in that environment (since I’m not a real programmer, nor do I play one on TV). Therefore, I like the option of calling BCP from within SQL Server itself using Transact-SQL’s xp_cmdshell extended stored procedure.
Here is an example of how to make a call from T-SQL (Transact-SQL) to write data out to a text file (e.g., TXT, CSV):
/******************************************************************************
WRAPPER QUERY to execute stored procedure and employ BCP to write a text (or CSV) file to a specified directory.
*******************************************************************************
WARNING: The output file will be OVERWRITTEN with each execution of this query UNLESS additional variables are added to modify the variable @iPathFile for each execution.
*******************************************************************************/
-- DECLARE Variables
declare @iCompanyID varchar(3)
declare @iServerName varchar(30)
declare @iDatabaseName varchar(30)
declare @iPathFile varchar(100)
declare @StartDate varchar(10)
declare @EndDate varchar(10)
declare @sql varchar(8000)
-- SET Variable values
set @iCompanyID = '<parameterValue>'
set @iServerName = '<SQLServerName>'
set @iDatabaseName = '<DatabaseName>'
set @iPathFile = '<ExportPath><ExportFilename>.TXT'
/******************************************************************************
In this case, we wanted to fetch data from the preceding calendar month. The following code fetches the last day of the preceding month into @EndDate and then uses that the make the first day of the same month fill the @StartDate variable.
*******************************************************************************/
select @EndDate = convert(varchar(10),dateadd(d,-1,dateadd(mm, datediff(m,0,getdate()),0)),101)
select @StartDate = cast(month(@EndDate) as varchar) + '/'
+ '1' + '/'
+ cast(year(@EndDate) as char(4))
/******************************************************************************
Build our SQL string for execution
*******************************************************************************/
set @sql = 'BCP "exec '
+ @iDatabaseName + '.dbo.spglExportX3_GAS_RKL '''
+ @iCompanyID
+ ''', ''' + @StartDate
+ ''', ''' + @EndDate
+ '''" queryout '
+ '"' + @iPathFile + '" -c -T -q -S '
+ '"' + @iServerName + '"'
/******************************************************************************
Un-remark the following line if you'd like to see what the SQL string looks like before it is executed.
*******************************************************************************/
-- print @sql
exec master.dbo.xp_CmdShell @sql
go
There are some limitations of which you should be aware.
Here are some ways such calls can be used:
Remember, exports such as these, employing variables to limit or define the data, can easily be configured as SQL Agent Jobs to automate the process of preparing exported data on a routine basis.
Let us know your thoughts on this. Was it helpful? Can you see a use for this in your organization? Contact us with your ideas and thinking on this matter.