IF OBJECTPROPERTY(OBJECT_ID('dbo.CopyContentsFromFile'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.CopyContentsFromFile
    IF OBJECTPROPERTY(OBJECT_ID('dbo.CopyContentsFromFile'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.CopyContentsFromFile >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.CopyContentsFromFile >>>'
END
go
CREATE PROCEDURE CopyContentsFromFile (
        @SearchForFiles char(1) = 'I',
        @FilesMask varchar(30) = NULL
)
AS
/********************************************************************************/
/*      Created By :    Umachandar Jayachandran (UC)                            */
/*      Created On :    16 Jan 2000                                             */
/*      Description:    This SP is used to copy any modified files or new files */
/*                      to the database. This uses the 'FmtFile.Cmd' script to  */
/*                      replace any HTML tags.                                  */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
DECLARE @cmdstr nvarchar(4000), @tempcmd nvarchar(4000),
        @relativepath varchar(255), @filename varchar(255),
        @write datetime
CREATE TABLE #status([Filename] varchar(255) null, Messages varchar(255) null)

SELECT @tempcmd = Value,
        @SearchForFiles = UPPER(@SearchForFiles)
FROM Settings
WHERE Setting = 'Format File CMD Script'

DECLARE files CURSOR FOR
SELECT r.[RelativePath], r.[Filename], w.[Write]
FROM [My Resource Files] r Join WebTechFiles w
ON r.[Filename] = w.[Filename]
WHERE PATINDEX('<<%>>', r.[Filename]) = 0 And
        (@SearchForFiles  = 'I' or      /* To update new or modified contents */
                (@SearchForFiles = 'U' And Datediff ( mi, r.[Write] , w.[Write]) > 0 )) And
        (@SearchForFiles  = 'U' or      /* To initialize contents */
                (@SearchForFiles = 'I' And [Contents] Is Null)) And
        (@FilesMask IS NULL or          /* To filter specific files */
                r.[Filename] Like '%' + @FilesMask + '%')
ORDER BY r.[RelativePath] DESC, r.[Filename]

OPEN files
WHILE(1 = 1)
BEGIN
        FETCH files INTO @relativepath, @filename, @write
        IF @@FETCH_STATUS < 0 BREAK

        UPDATE [My Resource Files]
        SET [Contents] = '', [Write] = @write
        WHERE [Filename] = @filename And [RelativePath] = @relativepath

        SELECT @cmdstr = STUFF(@tempcmd, CHARINDEX(N'%s', @tempcmd), 2,
                                        @relativepath + '\' + @filename)
        INSERT #status (Messages) EXEC master..xp_cmdshell @cmdstr

        -- Insert the filename as a comment
        UPDATE #status
        SET [Filename] = @filename
        WHERE [Filename] IS NULL
END
DEALLOCATE files
SELECT * FROM #status
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.CopyContentsFromFile'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.CopyContentsFromFile To Public
    PRINT '<<< CREATED PROCEDURE dbo.CopyContentsFromFile >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.CopyContentsFromFile >>>'
go

This page was last updated on May 01, 2006 04:28 PM.