Stored Proc errors for usp_Admin_Delete_Files_By_Date

Question

Post reply

Stored Proc errors for usp_Admin_Delete_Files_By_Date

kyle.doouss

SSCommitted

Points: 1513
More actions
November 30, 2011 at 9:55 am

#263774

I am wanting to create the stored procedure on the link below but getting the following errors

Please could someone point out my errors.

http://qa.sqlservercentral.com/scripts/Administration/62729/

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near ' '.

Msg 102, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 41

Incorrect syntax near ' '.

Msg 137, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 50

Must declare the scalar variable "@ProcessName".

Msg 137, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 51

Must declare the scalar variable "@CurrentFileDate".

Msg 137, Level 15, State 2, Procedure usp_Admin_Delete_Files_By_Date, Line 52

Must declare the scalar variable "@CurrentFileDate".

Msg 137, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 53

Must declare the scalar variable "@SourceDirFOR".

Msg 137, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 54

Must declare the scalar variable "@Error".

Msg 102, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 59

Incorrect syntax near ' '.

Msg 102, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 65

Incorrect syntax near ' '.

Msg 137, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 67

Must declare the scalar variable "@OSVersion".

Msg 102, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 73

Incorrect syntax near ' '.

Msg 102, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 77

Incorrect syntax near ' '.

Msg 137, Level 15, State 2, Procedure usp_Admin_Delete_Files_By_Date, Line 77

Must declare the scalar variable "@SourceDirFOR".

Msg 102, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 80

Incorrect syntax near ' '.

Msg 137, Level 15, State 2, Procedure usp_Admin_Delete_Files_By_Date, Line 86

Must declare the scalar variable "@OSVersion".

Msg 102, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 89

Incorrect syntax near ' '.

Msg 137, Level 15, State 2, Procedure usp_Admin_Delete_Files_By_Date, Line 98

Must declare the scalar variable "@OSVersion".

Msg 102, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 101

Incorrect syntax near ' '.

Msg 102, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 117

Incorrect syntax near ' '.

Msg 137, Level 15, State 2, Procedure usp_Admin_Delete_Files_By_Date, Line 123

Must declare the scalar variable "@FileName".

Msg 102, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 126

Incorrect syntax near ' '.

Msg 102, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 127

Incorrect syntax near ' '.

Msg 137, Level 15, State 2, Procedure usp_Admin_Delete_Files_By_Date, Line 129

Must declare the scalar variable "@FileName".

Msg 102, Level 15, State 1, Procedure usp_Admin_Delete_Files_By_Date, Line 133

Incorrect syntax near ' '.

Msg 137, Level 15, State 2, Procedure usp_Admin_Delete_Files_By_Date, Line 134

Must declare the scalar variable "@FileName".

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

george sibbald

SSC Guru

Points: 104210

November 30, 2011 at 10:08 am

#1414814

Incorrect syntax near '?'.

thats an invalid character. some error in the display. Remove all of them

---------------------------------------------------------------------

Lowell SSC Guru Points: 323482 More actions · Answer 2

it's not obvious.

the post, instead of having spaces, has CHAR(160)(which looks like a space, but isn't) in it, which prevents you from copy/paste/compile

i did a find/replace for you, and here it is, but that post is 11 years old, for SQL 2000.

it's limited to working on Server 2000 and server 2003 (OS 5.0 or 5.2!)

I'm sure there s something more recent and full featured to use instead.



IF EXISTS (SELECT [name] FROM dbo.sysobjects WHERE [name] = 'usp_Admin_Delete_Files_By_Date' AND TYPE = 'P')

    DROP PROCEDURE dbo.usp_Admin_Delete_Files_By_Date

GO

CREATE PROCEDURE dbo.usp_Admin_Delete_Files_By_Date (@SourceDir varchar(1024), @SourceFile varchar(512), @DaysToKeep int)

-- EXEC Admin.dbo.usp_Admin_Delete_Files_By_Date @SourceDir = '\\FooServer\BarShare\'

--                             , @SourceFile = 'FooFile_*'

--                             , @DaysToKeep = 3

AS

/******************************************************************************

**

**    Name: usp_Admin_Delete_Files_By_Date.sql

**

**    Description: Delete files older than X-days based on path & extension.

**

**        Depending on the output from xp_msver, we will execute either a

**        Windows 2000 or Windows 2003 specific INSERT INTO #_File_Details_02

**        operation as there is a small difference in the FOR output between

**        Windows 2000 and 2003 (Operating system versions).

**

**    Return values: 0 - Success

**            -1 - Error

**

**    Author: G. Rayburn

**

**    Date: 03/26/2007

**

**    Depends on: xp_cmdshell access to @SourceDir via SQLAgent account.

**

*******************************************************************************

**        Modification History

*******************************************************************************

**

**    Initial Creation: 03/26/2007 G. Rayburn

**

*******************************************************************************

**

******************************************************************************/

SET NOCOUNT ON

DECLARE @CurrentFileDate char(10)

    , @OldFileDate char(10)

    , @SourceDirFOR varchar(255)

    , @FileName varchar(512)

    , @DynDelete varchar(512)

    , @ProcessName varchar(150)

    , @OSVersion decimal(3,1)

    , @Error int

SET @ProcessName = 'usp_Admin_Delete_Files_By_Date - [' + @SourceFile + ']'

SET @CurrentFileDate = CONVERT(char(10),getdate(),121)

SET @OldFileDate = CONVERT(char(10),DATEADD(dd,-@DaysToKeep,@CurrentFileDate),121)

SET @SourceDirFOR = 'FOR %I IN ("' + @SourceDir + @SourceFile + '") DO @ECHO %~nxtI'

SET @Error = 0

-- Get Windows OS Version info for proper OSVer statement block exec.

CREATE TABLE #_OSVersion

    ( [Index] int

    , [Name] varchar(255)

    , [Internal_Value] varchar(255)

    , [Character_Value] varchar(255) )

INSERT INTO #_OSVersion

    EXEC master..xp_msver 'WindowsVersion'

SET @OSVersion = (SELECT SUBSTRING([Character_Value],1,3) FROM #_OSVersion)

-- Start temp table population(s).

CREATE TABLE #_File_Details_01

    ( Ident int IDENTITY(1,1)

    , Output varchar(512) )

INSERT INTO #_File_Details_01

    EXEC master..xp_cmdshell @SourceDirFOR

CREATE TABLE #_File_Details_02

    (Ident int

    , [TimeStamp] datetime

    , [FileName] varchar(255) )

-- OS Version specifics.

IF @OSVersion = '5.0'

    BEGIN -- Exec Windows 2000 version.

        INSERT INTO #_File_Details_02

            SELECT Ident

                , CONVERT(datetime, LEFT(CAST(SUBSTRING([Output],1,8) AS datetime),12)) AS [TimeStamp]

                , SUBSTRING([Output],17,255) AS [FileName]

                FROM #_File_Details_01

            WHERE [Output] IS NOT NULL

            ORDER BY Ident

    END

IF @OSVersion = '5.2'

    BEGIN -- Exec Windows 2003 version.

        INSERT INTO #_File_Details_02

            SELECT Ident

                , CONVERT(char(10), SUBSTRING([Output],1,10), 121) AS [TimeStamp]

                , SUBSTRING([Output],21,255) AS [FileName]

                FROM #_File_Details_01

            WHERE [Output] IS NOT NULL

            ORDER BY Ident

    END

-- Start delete ops cursor.

DECLARE curDelFile CURSOR

READ_ONLY

FOR

    SELECT [FileName]

        FROM #_File_Details_02

    WHERE [TimeStamp] <= @OldFileDate

OPEN curDelFile

FETCH NEXT FROM curDelFile INTO @FileName

WHILE (@@fetch_status <> -1)

BEGIN

    IF (@@fetch_status <> -2)

    BEGIN

        SET @DynDelete = 'DEL /Q "' + @SourceDir + @FileName + '"'

        EXEC master..xp_cmdshell @DynDelete

    END

    FETCH NEXT FROM curDelFile INTO @FileName

END

CLOSE curDelFile

DEALLOCATE curDelFile

DROP TABLE #_OSVersion

DROP TABLE #_File_Details_01

DROP TABLE #_File_Details_02

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kyle.doouss SSCommitted Points: 1513 More actions · Answer 3

kyle.doouss

SSCommitted

Points: 1513

November 30, 2011 at 10:51 am

#1414851

Thanks. I am running 2008 so I will have a look for another. If you see anying let me know. Many thanks

kyle.doouss SSCommitted Points: 1513 More actions · Answer 4

kyle.doouss

SSCommitted

Points: 1513

November 30, 2011 at 10:51 am

#1414852

Thanks. I am running 2008 so I will have a look for another. If you see anying let me know. Many thanks

SQLRNNR SSC Guru Points: 281324 More actions · Answer 5

SQLRNNR

SSC Guru

Points: 281324

November 30, 2011 at 11:00 am

#1414857

What version of SQL Server are you running?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

kyle.doouss SSCommitted Points: 1513 More actions · Answer 6

I appreciate your help. Is this enough info?

Microsoft SQL Server Management Studio 10.0.1600.22 ((SQL_PreRelease).080709-1414 )

Microsoft Analysis Services Client Tools 2007.0100.1600.022 ((SQL_PreRelease).080709-1414 )

Microsoft Data Access Components (MDAC) 6.1.7600.16385 (win7_rtm.090713-1255)

Microsoft MSXML 3.0 4.0 6.0

Microsoft Internet Explorer 8.0.7600.16385

Microsoft .NET Framework 2.0.50727.4952

Operating System 6.1.7600

SQLRNNR SSC Guru Points: 281324 More actions · Answer 7

You might want to consider using a POSH script.

Here is an example of one used to delete old backup files.

http://codetempest.blogspot.com/2008/10/delete-old-database-bak-files-using.html

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

kyle.doouss SSCommitted Points: 1513 More actions · Answer 8

kyle.doouss

SSCommitted

Points: 1513

November 30, 2011 at 11:19 am

#1414863

Thanks. I will have a go at it. Cheers

kyle.doouss SSCommitted Points: 1513 More actions · Answer 9

The query below is working a dream although I would like to be able to specify the beginning of the file name

I would be very glad of any pointers as to how to do this?

DECLARE @Dos_cmd VARCHAR(1000)DECLARE

@Powershell_cmd VARCHAR(1000)

SET @Powershell_cmd = '"& '+'Get-ChildItem ''D:\WebFiles\'' | '+

'where {$_.lastWriteTime -lt ((Get-Date).AddDays(-5)) -and ($_.Extension -match ''txt'') } | ' +

'Remove-Item -force " '

-- create the full DOS comand

SET

@Dos_cmd = ' ""C:\WINDOWS\system32\windowspowershell\v1.0\powershell.exe" '+ @Powershell_cmd

--Run it using SQL command shell xp

exec master..xp_cmdshell @Dos_cmd

kyle.doouss SSCommitted Points: 1513 More actions · Answer 10

kyle.doouss

SSCommitted

Points: 1513

December 2, 2011 at 1:51 am

#1415746

I am still trying to sort how to do this. Any ideas??

kyle.doouss SSCommitted Points: 1513 More actions · Answer 11

kyle.doouss

SSCommitted

Points: 1513

December 2, 2011 at 10:10 am

#1416050

Could any one help me on my last post?

SQLRNNR SSC Guru Points: 281324 More actions · Answer 12

kyle.doouss (11/30/2011)

The query below is working a dream although I would like to be able to specify the beginning of the file name

I would be very glad of any pointers as to how to do this?

DECLARE @Dos_cmd VARCHAR(1000)DECLARE

@Powershell_cmd VARCHAR(1000)

SET @Powershell_cmd = '"& '+'Get-ChildItem ''D:\WebFiles\'' | '+

'where {$_.lastWriteTime -lt ((Get-Date).AddDays(-5)) -and ($_.Extension -match ''txt'') } | ' +

'Remove-Item -force " '

-- create the full DOS comand

SET

@Dos_cmd = ' ""C:\WINDOWS\system32\windowspowershell\v1.0\powershell.exe" '+ @Powershell_cmd

--Run it using SQL command shell xp

exec master..xp_cmdshell @Dos_cmd



DECLARE @Dos_cmd VARCHAR(1000)DECLARE

 @Powershell_cmd VARCHAR(1000)

 SET @Powershell_cmd = '"& '+'Get-ChildItem ''D:\WebFiles\somefilenamestart*'' | '+

 'where {$_.lastWriteTime -lt ((Get-Date).AddDays(-5)) -and ($_.Extension -match ''txt'') } | ' +

 'Remove-Item  -force " '

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

kyle.doouss SSCommitted Points: 1513 More actions · Answer 13

kyle.doouss

SSCommitted

Points: 1513

December 3, 2011 at 3:27 am

#1416264

Thanks again.! Sorry so obvious!

SQLRNNR SSC Guru Points: 281324 More actions · Answer 14

SQLRNNR

SSC Guru

Points: 281324

December 3, 2011 at 7:49 am

#1416305

You're welcome

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events