November 30, 2011 at 9:55 am
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".
November 30, 2011 at 10:08 am
Incorrect syntax near '?'.
thats an invalid character. some error in the display. Remove all of them
---------------------------------------------------------------------
November 30, 2011 at 10:10 am
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
November 30, 2011 at 10:51 am
Thanks. I am running 2008 so I will have a look for another. If you see anying let me know. Many thanks
November 30, 2011 at 10:51 am
Thanks. I am running 2008 so I will have a look for another. If you see anying let me know. Many thanks
November 30, 2011 at 11:00 am
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
November 30, 2011 at 11:10 am
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
November 30, 2011 at 11:16 am
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
November 30, 2011 at 11:19 am
Thanks. I will have a go at it. Cheers
November 30, 2011 at 3:26 pm
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
December 2, 2011 at 1:51 am
I am still trying to sort how to do this. Any ideas??
December 2, 2011 at 10:10 am
Could any one help me on my last post?
December 2, 2011 at 10:15 am
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 nameI 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
December 3, 2011 at 3:27 am
Thanks again.! Sorry so obvious!
December 3, 2011 at 7:49 am
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply