This tip continues the system stored procedure series with a routine to create a comma-delimited file containing data from several columns.
A comma-delimited data file, also known as a comma-separated value (CSV) file, is typically opened with Microsoft Excel and it often contains a simple data extract. It gets its name from the fact that a comma is placed between adjacent column values within each row. Excel uses each comma as a signal to place the following value in the next column of the worksheet.
SQL Server provides at least four different ways to create a data file, but none are easy to perform using T-SQL code. A DTS package could be used, but that must be developed outside of T-SQL and it involves a cumbersome execution process. A data file can be created using a FileSystemObject through OLE automation, but that can be tricky to do properly. The osql command prompt utility can be used, but that involves a cumbersome execution process. The bcp (bulk copy program) command prompt utility can be used, but that also involves a cumbersome execution process.
This stored procedure acts as a wrapper around two of the above methods and shields you from the details of implementation. It offers a choice between the OLE automation method and the bcp method.
Using OLE automation to create a data file is an interesting approach because very similar code can be used to perform other file manipulations. However, the method is not necessarily recommended for this particular task. There are serious security implications if you allow users to execute the extended stored procedures that support OLE automation. The method is also very slow with large sets of data. It does give you the ability to precisely control what goes into the file, but such flexibility is not necessary in most cases. The OLE automation method is included in this routine as much for the sake of example as for adding useful functionality.
Using bcp to create a data file is strongly pr
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
eferred (and it's the default method) because it generally provides much better performance than the OLE automation method. This stored procedure uses the xp_cmdshell extended stored procedure to run the bcp utility. There are security implications with using xp_cmdshell. Normally, only a sysadmin can execute xp_cmdshell, but others can be granted permission. The SQL Agent proxy account is another way to control access to the potentially dangerous tool. See MSDN for further details about xp_cmdshell. The osql method would be very similar to the bcp method for this purpose, in both usage and effect.
The SQL code in Listing 1 creates a system stored procedure named sp_SaveDelimitedColumns. The routine creates a file at the specified location and puts the specified set of data into the file.
The sp_SaveDelimitedColumns stored procedure accepts five parameters, but only one of them is required.
The first parameter (@PCWrite) specifies the location for the data file. The parameter must provide a complete path, including file name, to a location where the SQL Server service account is allowed to create a file. If no value is provided the results are returned as text.
The second parameter (@DBFetch) specifies the set of data for the file. The parameter can be a table name (including a temporary table if using the bcp method), view name, user-defined function call or SELECT statement.
The third parameter (@DBWhere) is optional and it specifies a WHERE clause to filter the data specified by the @DBFetch parameter.
The fourth parameter (@DBThere) is optional and it specifies an ORDER BY clause to sort the data specified by the @DBFetch parameter.
The fifth parameter (@DBUltra) is optional and it specifies which data-file-creation method to use. A value of zero (0) uses the bcp method. A value of one (1) uses the OLE automation method.
It's awkward to embed single quotes in a string literal with T-SQL. For convenience when specifying string parameters, caret symbols (^) are converted to single quotes in @DBFetch, @DBWhere and @DBThere. This feature is demonstrated in an example below.
The sp_SaveDelimitedColumns stored procedure creates a file at the @PCWrite location (using the @DBUltra method) and places in it the columns from the @DBFetch data set (optionally filtered/sorted by @DBWhere/@DBThere). The values for any column that is not numeric are enclosed in quotation marks (such as "String") in case the values contain the column delimiter character (a comma). The values for each column are delimited, or separated, with commas (such as "String1",0,"String2").
The T-SQL code for this stored procedure is structured much like that of the previous tip, but it's much more involved in the middle because it must convert values of various data types into values of a character data type before putting them into a text file.
The examples below use a NULL value for the destination file parameter. Please substitute a complete path, including file name, that's appropriate for your environment.
This example exports selected customers from the Northwind database.
This example exports a product list sorted by ProductID from the Northwind database.
I hope you find this system stored procedure to be useful.
Click for the stored procedure: sp_SaveDelimitedColumns
About the author: Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has more than 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Walker is a software developer, database developer, database administrator and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.
More information from SearchSQLServer.com
Fast Guide: Stored procedures
Tips: View our complete collection of stored procedures
Topic: Get stored procedure tips and tricks in this topic section
