Stored procedure: Create a fixed-width file to simplify data exports

Stored procedure: Create a fixed-width file to simplify data exports

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

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in November 2005

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 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

 

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top