This tip continues the system stored procedure series with a routine to execute T-SQL code from a file.

The tone of this tip is a temporary departure from the norm because I want to comment on a situation that I have noticed.  I recently came across two different stored procedures that execute T-SQL code from a file without using the osql command prompt utility.  Both routines are available from well-known SQL Server web sites and both are well-rated.  I will not identify either of them specifically because this tip is not complimentary about their functionality.

There might be many reasons for wanting to execute T-SQL code from a file, but there's one reason that comes to mind for me.  I use such a feature when building a loaded database from scratch.  I have one master script that creates an empty database and performs several small tasks.  In between those tasks the master script also executes T-SQL code from several files.  The files contain code to create a variety of database objects and copy data.  The same files are also used for other database administration chores.

There might be many reasons for wanting to avoid osql, but I can think of only one.  Because osql is a command prompt utility it's used through the xp_cmdshell extended stored procedure.  Many database administrators strictly control which users can execute that stored procedure to avoid a potential security risk.  The idea behind writing a custom routine to execute T-SQL code from a file is to avoid a reliance on xp_cmdshell.  However, one of the stored procedures mentioned above uses xp_cmdshell to import the file into a table.  I might be able to overlook that inexplicable choice if the routine offered features that osql lacks.  That's not the case.  In fact, the routine imposes some significant additional limitations.

The two stored procedures mentioned above have a number of shortcomings.  One imposes a maximum file size of 80K.  One imposes a maximum batch size of 8K.  One imposes a limitation of a single batch within the file and it enforces that limitation by removing any batch separators ("GO").  Such an action would render many script files useless.  Both routines impose a maximum line length of 250-some characters.  Both use the temporary database in an odd and risky way.  One does nothing to ensure that the lines of T-SQL code are executed in the order in which they appear in the file.  One does not preserve the format of the T-SQL code, which matters when database objects (such as stored procedures) are created by that code.

The system stored procedure presented in this tip addresses those shortcomings.  It does not impose a maximum file size.  It allows batch sizes up to 80K.  It allows an effectively unlimited number of batches in the file.  It allows thousands of characters in a line of T-SQL code.  This routine uses the temporary database in a standard and safe way.  It ensures that the lines of T-SQL code are executed in the correct order.  It preserves the format of the T-SQL code, just as it exists in the file.  Of course, this stored procedure does not rely on xp_cmdshell.  It does not use osql and it offers a feature that osql lacks.

The SQL code in Listing 1 creates a system stored procedure named sp_ExecuteSQLFromFile.  The routine reads a specified file and processes the contents as batches of T-SQL code.  Each batch should end with the word "GO" on a line by itself, which is the standard batch separator in T-SQL files.  The routine optionally returns a result set containing information about each T-SQL batch in the file.  The result set includes batch numbers, locations within the file (first and last lines), line counts, execution periods (start and end times), elapsed times, and error codes.

The SQL code in Listing 1 also creates a format file in the SQL Server program directory ("C:\Program Files\Microsoft SQL Server\File.fmt").  The location can be changed by modifying Listing 1 in two places.  The xp_cmdshell extended stored procedure is used to create the format file.  The ability to execute xp_cmdshell is required only when the sp_ExecuteSQLFromFile stored procedure is created and only by the user doing the creation.  That's a very different operation than actually executing sp_ExecuteSQLFromFile.

The format file is used when importing the T-SQL code from a file into a temporary table with the BULK INSERT statement.  The format file allows an IDENTITY column to exist in the temporary table.  The IDENTITY column provides a way to order the rows of the temporary table when assembling the T-SQL code to be executed.  Unless an ORDER BY clause is used in the involved SELECT statement there's no guarantee that the rows are returned in the order they appear in the file.

The sp_ExecuteSQLFromFile stored procedure accepts up to three parameters, but only one of them is required.

The first parameter (@PCFetch) specifies the location for the T-SQL code file.  The paramter must provide a complete path, including file name, to the desired T-SQL code file.  The SQL Server service account must be allowed to read files in that location.

The second parameter (@PCAdmin) is optional and it specifies the location of the format file.  The paramter must provide a complete path, including file name, to the format file.  The default location is a file named "File.fmt" in the SQL Server program directory ("C:\Program Files\Microsoft SQL Server\File.fmt").  The SQL code in Listing 1 creates a format file in that location.

The third parameter (@PCUltra) is optional and it specifies whether a batch information result set is returned.  A value of zero (0) means no result set.  A value of one (1) means a result set is returned with a row for each batch in the file.

Please be aware that Web page formatting may have made single lines in Listing 1 wrap to a second line.  If so, remove the extra CR/LF before executing the code.

The example below is for illustration purposes only.  The path must be changed to a location appropriate for your environment.

EXECUTE sp_ExecuteSQLFromFile 'D:\Scripts\Script.sql',NULL,1

I hope you find this system stored procedure to be useful.

This tip is different from my previous tips because it's a reaction to current conditions.  Please take that as an indication that I'm willing to explore topics suggested by readers.  I have many ideas for future tips, but I also invite reader feedback.  If you have an idea for a general purpose stored procedure please send it my way at SQLTips@comcast.net.  I'm interested in ideas for new routines or improved versions of existing routines (including the stored procedures already presented in this series).