-- Listing 1

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\File.fmt')

SET @Task = 'BULK INSERT #DBAT FROM ' + CHAR(39) + @PCFetch + CHAR(39) + ' WITH (FORMATFILE = ' + CHAR(39) + @PCAdmin + CHAR(39) + ')'

EXECUTE (@Task)

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