You may also have many reasons for wanting to avoid osql, but I can think of only one: osql is a command prompt utility and, as such, it is 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
-- but that's not the case. In fact, the routine imposes some significant additional limitations.
The two stored procedures in question have a number of other shortcomings. One or both routines impose:
Both routines use the temporary database in an odd and risky way. One creates a normal user table within tempdb and the other creates a global temporary table, but neither does so by necessity. Both run the risk of having table names conflict if two connections run the stored procedure at the same time, which could have some ugly consequences. One of them fails to ensure that the lines of T-SQL code are executed in the order in which they appear in the file and the other one fails to preserve the format of the T-SQL code. The format of the code 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 80 KB. 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 and it preserves the format of the T-SQL code as it exists in the file. Of course, this stored procedure does not rely on xp_cmdshell or 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 parameter 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 parameter 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.
The example below is for illustration purposes only. The path must be changed to a location appropriate for your environment.
Suppose the contents of Script.sql are
Select Getdate()
Select Count(1) from sysobjects
Select top 10 * from sysobjects
and contents of file.fmt file are
1.0
1
1 SQLCHAR 0 0 "\r\n" 1 Line ""
Where 1.0 is version, 1 is Number of Fields,
1 is Serial Number, SQLCHAR is datatype, 0 is prefix-length, used only for binary files, 0 is the length, and is used for fixed-length fields. "\r\n" is the terminator. 1 is the database column and Line is database column name, which is not used. Last colunm is the collation for the data in the file
As Fmt file is set and script that needs to execute is set, now prepare the procedure to execute it.
EXECUTE sp_ExecuteSQLFromFile 'D:\Scripts\Script.sql',NULL,1
I hope you find this system stored procedure to be useful.
--------------------------------------------------------------------
--Stored Procedure: sp_ExecuteSQLFromFile
--------------------------------------------------------------------
USE master
GO
CREATE PROCEDURE dbo.sp_ExecuteSQLFromFile
@PCFetch varchar(1000),
@PCAdmin varchar(1000) = NULL,
@PCUltra bit = 0
AS
SET NOCOUNT ON
DECLARE @Return int
DECLARE @Retain int
DECLARE @Status int
SET @Status = 0
DECLARE @Task varchar(2000)
DECLARE @Work varchar(2000)
DECLARE @Line varchar(8000)
DECLARE @SQL1 varchar(8000)
DECLARE @SQL2 varchar(8000)
DECLARE @SQL3 varchar(8000)
DECLARE @SQL4 varchar(8000)
DECLARE @SQL5 varchar(8000)
DECLARE @SQL6 varchar(8000)
DECLARE @SQL7 varchar(8000)
DECLARE @SQL8 varchar(8000)
DECLARE @SQL9 varchar(8000)
DECLARE @CRLF char(2)
DECLARE @Save tinyint
DECLARE @Have int
DECLARE @SQLA int
DECLARE @SQLZ int
DECLARE @SQLN int
DECLARE @BOLA datetime
DECLARE @BOLZ datetime
DECLARE @BOLN datetime
CREATE TABLE #DBAT (Line varchar(8000), Work int IDENTITY(1,1))
CREATE TABLE #DBAZ (Batch int, SQLA int, SQLZ int, SQLN int, BOLA datetime, BOLZ datetime, BOLN datetime, Status int)
SET @CRLF = CHAR(13) + CHAR(10)
SET @SQL1 = ''
SET @SQL2 = ''
SET @SQL3 = ''
SET @SQL4 = ''
SET @SQL5 = ''
SET @SQL6 = ''
SET @SQL7 = ''
SET @SQL8 = ''
SET @SQL9 = ''
SET @Save = 1
SET @Have = 0
SET @SQLA = 1
SET @PCAdmin = ISNULL(@PCAdmin,'C:\Program Files\Microsoft SQL Server\Files.fmt')
SET @Task = 'BULK INSERT #DBAT FROM ' + CHAR(39) + @PCFetch + CHAR(39) + ' WITH (FORMATFILE = ' + CHAR(39) + @PCAdmin + CHAR(39) + ')'
EXECUTE (@Task)
--To display the scripts that will be executed.
Select * from #DBAT
SET @Return = @@ERROR
IF @Status = 0
SET @Status = @Return
DECLARE Lines CURSOR FAST_FORWARD FOR SELECT ISNULL(Line,''), Work FROM #DBAT ORDER BY Work
OPEN Lines
FETCH NEXT FROM Lines INTO @Line, @SQLZ
WHILE @@FETCH_STATUS = 0 AND @Status = 0
BEGIN
IF UPPER(LTRIM(RTRIM(@Line))) = 'GO'
BEGIN
SET @BOLA = GETDATE()
SET @Have = @Have + 1
EXECUTE (@SQL1+@SQL2+@SQL3+@SQL4+@SQL5+@SQL6+@SQL7+@SQL8+@SQL9)
SET @Return = @@ERROR -- IF @Status = 0 SET @Status = @Return
SET @BOLZ = GETDATE()
SET @SQLN = @SQLZ - @SQLA
SET @BOLN = @BOLZ - @BOLA
INSERT #DBAZ VALUES (@Have, @SQLA, @SQLZ, @SQLN, @BOLA, @BOLZ, @BOLN, @Return)
SET @SQL1 = ''
SET @SQL2 = ''
SET @SQL3 = ''
SET @SQL4 = ''
SET @SQL5 = ''
SET @SQL6 = ''
SET @SQL7 = ''
SET @SQL8 = ''
SET @SQL9 = ''
SET @Save = 1
SET @BOLA = GETDATE()
SET @SQLA = @SQLZ + 1
END
ELSE
BEGIN
IF @Save = 1
IF DATALENGTH(@SQL1) + DATALENGTH(@Line) < 7998
SET @SQL1 = @SQL1 + @Line + @CRLF
ELSE
SET @Save = 2
IF @Save = 2
IF DATALENGTH(@SQL2) + DATALENGTH(@Line) < 7998
SET @SQL2 = @SQL2 + @Line + @CRLF
ELSE
SET @Save = 3
IF @Save = 3
IF DATALENGTH(@SQL3) + DATALENGTH(@Line) < 7998
SET @SQL3 = @SQL3 + @Line + @CRLF
ELSE
SET @Save = 4
IF @Save = 4
IF DATALENGTH(@SQL4) + DATALENGTH(@Line) < 7998
SET @SQL4 = @SQL4 + @Line + @CRLF
ELSE
SET @Save = 5
IF @Save = 5 IF DATALENGTH(@SQL5) + DATALENGTH(@Line) < 7998
SET @SQL5 = @SQL5 + @Line + @CRLF
ELSE
SET @Save = 6
IF @Save = 6 IF DATALENGTH(@SQL6) + DATALENGTH(@Line) < 7998
SET @SQL6 = @SQL6 + @Line + @CRLF
ELSE
SET @Save = 7
IF @Save = 7
IF DATALENGTH(@SQL7) + DATALENGTH(@Line) < 7998
SET @SQL7 = @SQL7 + @Line + @CRLF
ELSE
SET @Save = 8
IF @Save = 8
IF DATALENGTH(@SQL8) + DATALENGTH(@Line) < 7998
SET @SQL8 = @SQL8 + @Line + @CRLF
ELSE
SET @Save = 9
IF @Save = 9
IF DATALENGTH(@SQL9) + DATALENGTH(@Line) < 7998
SET @SQL9 = @SQL9 + @Line + @CRLF
ELSE
SET @Save = 0
END
FETCH NEXT FROM Lines INTO @Line, @SQLZ END
CLOSE Lines
DEALLOCATE Lines
IF DATALENGTH(@SQL1) > 0 AND @Status = 0
BEGIN
SET @BOLA = GETDATE()
SET @Have = @Have + 1
EXECUTE (@SQL1+@SQL2+@SQL3+@SQL4+@SQL5+@SQL6+@SQL7+@SQL8+@SQL9)
SET @Return = @@ERROR -- IF @Status = 0 SET @Status = @Return
SET @BOLZ = GETDATE()
SET @SQLN = @SQLZ - @SQLA + 1
SET @BOLN = @BOLZ - @BOLA
INSERT #DBAZ VALUES (@Have, @SQLA, @SQLZ, @SQLN, @BOLA, @BOLZ, @BOLN, @Return)
END
IF @PCUltra <> 0
BEGIN
SELECT Batch, SQLA AS LineFrom, SQLZ AS LineThru, SQLN AS CodeSize, CONVERT(char(12),BOLA,14) AS TimeFrom, CONVERT(char(12),BOLZ,14) AS TimeThru, CONVERT(char(12),BOLN,14) AS Duration, Status FROM #DBAZ
ORDER BY Batch
END
DROP TABLE #DBAT
DROP TABLE #DBAZ
SET NOCOUNT OFF
RETURN (@Status)
GO
DECLARE @Task varchar(1000)
DECLARE @Work varchar(2000)
SET @Task = ' PRINT ' + CHAR(39) + '7.0' + CHAR(39)
+ ' PRINT ' + CHAR(39) + '1' + CHAR(39)
+ ' PRINT ' + CHAR(39) + '1 SQLCHAR 0 8000 ' + CHAR(39) + ' + CHAR(34) + ' + CHAR(39) + '\r\n' + CHAR(39) + ' + CHAR(34) + ' + CHAR(39) + ' 1 Line SQL_Latin1_General_CP1_CI_AS' + CHAR(39)
SET @Work = 'osql -E -Q "' + @Task + '" -o "C:\Program Files\Microsoft SQL Server\File.fmt" -s "" -w 8000'
EXECUTE master.dbo.xp_cmdshell @Work, NO_OUTPUT
GO
------------------------------------------------------------
NOTE: Make sure xp_cmdshell is enabled on Surface Area Configuration options.
Happy SQL Coding.
No comments:
Post a Comment