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
SQL Server Bulk Copy Caveats
There are some limitations of which you should be aware.
- BCP cannot make use of Temp-tables in a call to a stored procedure. So I always code procedures that will be called from BCP to employ permanent tables. Just be sure that you truncate them before use or provide some other method to assure that you return only the data you require from the BCP call.
- Be certain that the service credentials used to run the SQL Server service have the proper permission on the destination directory for the output file, so that it can create and change the file during the write-out process.
- If your server name, procedure name, or database name have any space characters or other symbols (e.g., hyphens) in them, be sure to invoke the –q switch in your call. This switch sets quoted_identifiers on. Then enclose the names in full quotation marks.
- The –T switch is handy in many instances. It enables a “trusted connection” to the server so that you need not supply user credentials in your call.
Possible Uses
Here are some ways such calls can be used:
- Export files to be used for EFT (electronic funds transfer) or positive-pay with your bank, especially if your bank employs a non-standard format that cannot be configured readily using standard EFT or positive-pay software
- Export files for use by other systems
- Export archives of specific data as text
- Export files in specified formats for EDI (electronic data interchange) or non-standard exchanges between trading partners
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.
