This tip continues the system stored procedure series with a routine to create a fixed-width file containing data from a single column. A fixed-width (or fixed-length field) data file is typically used to provide data to a separate system. It gets its name from the fact that every row of the data is exactly the same width. If the rows contain data from multiple columns it's up to the receiving system to extract the data from fields based on their consistent positions within the rows. SQL Server provides at least four different ways to create a data file, but none of them are easily used from 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 involves a cumbersome execution process as well. 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. The OLE automation method of creating a data file is an interesting approach, but it's not necessarily recommended for this particular task. There are serious security implications with allowing users to execute the extended stored procedures that support OLE automation. The method is also very slow with large sets of data. It does have the advantage of being able to precisely control what goes into the file, but in most cases such flexibility is not necessary. The OLE automation method is included in this routine as much for the sake of example as for adding useful functionality. The bcp method of creating a data file is strongly preferred (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 BOL 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_SaveFixedColumn. The routine creates a file at the specified location and puts the specified set of data into the file. The sp_SaveFixedColumn stored procedure accepts six parameters, but only two of them are required. The first parameter (@PCWrite) specifies the location for the data file. The paramter 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 (@DBField) specifies the column to be placed in the data file. The column must exist with the set of data specified by the @DBFetch parameter. It's assumed that the column values are padded as necessary to result in a fixed length. The fourth parameter (@DBWhere) is optional and it specifies a WHERE clause to filter the data specified by the @DBFetch parameter. The fifth parameter (@DBThere) is optional and it specifies an ORDER BY clause to sort the data specified by the @DBFetch parameter. The sixth parameter (@DBUltra) is optional and it specifies which method of creating a data file 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_SaveFixedColumn stored procedure creates a file at the @PCWrite location (using the @DBUltra method) and places in it the @DBField column from the @DBFetch data set (optionally filtered/sorted by @DBWhere/@DBThere). A fixed-width data file is not always appropriate so the next tip in the series will describe a routine to create a comma-delimited 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 customer identifiers from the Northwind database. USE Northwind EXECUTE sp_SaveFixedColumn NULL, 'Customers', 'CustomerID', 'Country = ^USA^' This example exports a product inventory list sorted by ProductID from the Northwind database. USE Northwind EXECUTE sp_SaveFixedColumn NULL, 'SELECT STR(ProductID,10)+STR(UnitsInStock,10) AS Inventory, ProductID FROM Products', 'Inventory', NULL, 'ProductID' I hope you find this system stored procedure to be useful.