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 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 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 preferred (and it's the
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
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_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 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 (@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 to use to create a data file. 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.
Click for the stored procedure: sp_SaveFixedColumn
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
