Monday, March 12, 2012

Programming

Andrew or anyone,
I'm trying to write a short sql stmts to attached to a folder and query that
folder for *.bak and if the*.bak is older than a certain amount of days, then
I want to delete them.
Is this possible using tsql?
"Andrew J. Kelly" wrote:

> Don't use EM, use a script instead. EM has some limitations and things like
> this can be much more controlled through scripts. The restore syntax is
> pretty simple and there are examples in BOL. If you have troubles with it
> post your script and we can help.
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:B202FDF2-5301-4E8E-9558-F2E0505F563C@.microsoft.com...
>
>
You can't do this strictly with TSQL but this is as close as you will get.
There is a sample sp that will delete old log file backups based on the
timestamp in the name of the files.
-- Removing Older Backup Files --
-- Remove any log files older than 7 days
DECLARE @.Error INT, @.D DATETIME
SET @.D = DATEADD(dd,-7,GETDATE())
EXEC @.Error = remove_old_log_files @.D
SELECT @.Error
CREATE PROCEDURE remove_old_log_files
@.DelDate DATETIME
AS
SET NOCOUNT ON
DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
-- Used later when we cast strings to Datetimes
SET DATEFORMAT MDY
-- Create a table to hold the results of the DIR command
IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
DROP TABLE #DirList
CREATE TABLE #dirlist (FName VARCHAR(1000))
-- Create a table to hold any errors
IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
DROP TABLE #Errors
CREATE TABLE #Errors (Results VARCHAR(1000))
-- Insert the results of the dir cmd into a table so we can scan it
INSERT INTO #dirlist (FName)
exec @.Return = master..xp_cmdshell 'dir /OD C:\Data\Backups\*.Bak'
SET @.Error = @.@.ERROR
IF @.Error <> 0 OR @.Return <> 0
BEGIN
IF @.Return = 1
SET @.Error = -1
SET @.Msg = 'Error while getting the filenames with DIR '
GOTO On_Error
END
-- Remove the garbage
DELETE #dirlist WHERE
SUBSTRING(FName,1,2) < '00' OR
SUBSTRING(FName,1,2) > '99' OR
FName IS NULL OR
FName LIKE '%<DIR>%'
-- Create a cursor and for each file name do the processing.
-- The files will be processed in date order.
DECLARE curDir CURSOR READ_ONLY LOCAL
FOR
SELECT SUBSTRING(FName,40,40) AS FName
FROM #dirlist
WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
AND SUBSTRING(FName,40,40) LIKE '%.BAK'
ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)
OPEN curDir
FETCH NEXT FROM curDir INTO @.Fname
WHILE (@.@.fetch_status = 0)
BEGIN
-- Delete the old backup files
SET @.Delete = 'DEL "C:\Data\Backups\' + @.FName + '"'
INSERT INTO #Errors (Results)
exec @.Return = master..xp_cmdshell @.Delete
IF @.@.RowCount > 1 OR @.Return = 1
BEGIN
SET @.Error = -1
SET @.Msg = 'Error while Deleting file ' + @.FName
GOTO On_Error
END
PRINT 'Deleted ' + @.FName + ' at ' +
CONVERT(VARCHAR(28),GETDATE(),113)
FETCH NEXT FROM curDir INTO @.Fname
END
CLOSE curDir
DEALLOCATE curDir
DROP TABLE #DirList
DROP TABLE #Errors
RETURN @.Error
On_Error:
BEGIN
IF @.Error <> 0
BEGIN
SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
RAISERROR(@.Msg,12,1)
RETURN @.Error
END
END
GO
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:041D4B77-15AB-42F8-80B6-AB740408D913@.microsoft.com...[vbcol=seagreen]
> Andrew or anyone,
> I'm trying to write a short sql stmts to attached to a folder and query
> that
> folder for *.bak and if the*.bak is older than a certain amount of days,
> then
> I want to delete them.
> Is this possible using tsql?
> "Andrew J. Kelly" wrote:
|||Thanks.. I'm going through the code and I'm running into an error when you
use the directory list command. I'm not able to view the temporary table to
see if data is being stored.
"Andrew J. Kelly" wrote:

> You can't do this strictly with TSQL but this is as close as you will get.
> There is a sample sp that will delete old log file backups based on the
> timestamp in the name of the files.
> -- Removing Older Backup Files --
> -- Remove any log files older than 7 days
> DECLARE @.Error INT, @.D DATETIME
> SET @.D = DATEADD(dd,-7,GETDATE())
> EXEC @.Error = remove_old_log_files @.D
>
> SELECT @.Error
> --
> CREATE PROCEDURE remove_old_log_files
> @.DelDate DATETIME
> AS
> SET NOCOUNT ON
> DECLARE @.SQL VARCHAR(500), @.FName VARCHAR(40), @.Error INT
> DECLARE @.Delete VARCHAR(300), @.Msg VARCHAR(100), @.Return INT
> -- Used later when we cast strings to Datetimes
> SET DATEFORMAT MDY
> -- Create a table to hold the results of the DIR command
> IF OBJECT_ID('tempdb..#dirlist') IS NOT NULL
> DROP TABLE #DirList
> CREATE TABLE #dirlist (FName VARCHAR(1000))
> -- Create a table to hold any errors
> IF OBJECT_ID('tempdb..#Errors') IS NOT NULL
> DROP TABLE #Errors
> CREATE TABLE #Errors (Results VARCHAR(1000))
> -- Insert the results of the dir cmd into a table so we can scan it
> INSERT INTO #dirlist (FName)
> exec @.Return = master..xp_cmdshell 'dir /OD C:\Data\Backups\*.Bak'
> SET @.Error = @.@.ERROR
> IF @.Error <> 0 OR @.Return <> 0
> BEGIN
> IF @.Return = 1
> SET @.Error = -1
> SET @.Msg = 'Error while getting the filenames with DIR '
> GOTO On_Error
> END
> -- Remove the garbage
> DELETE #dirlist WHERE
> SUBSTRING(FName,1,2) < '00' OR
> SUBSTRING(FName,1,2) > '99' OR
> FName IS NULL OR
> FName LIKE '%<DIR>%'
>
> -- Create a cursor and for each file name do the processing.
> -- The files will be processed in date order.
> DECLARE curDir CURSOR READ_ONLY LOCAL
> FOR
> SELECT SUBSTRING(FName,40,40) AS FName
> FROM #dirlist
> WHERE CAST(SUBSTRING(FName,1,20) AS DATETIME) < @.DelDate
> AND SUBSTRING(FName,40,40) LIKE '%.BAK'
> ORDER BY CAST(SUBSTRING(FName,1,20) AS DATETIME)
> OPEN curDir
> FETCH NEXT FROM curDir INTO @.Fname
> WHILE (@.@.fetch_status = 0)
> BEGIN
>
> -- Delete the old backup files
> SET @.Delete = 'DEL "C:\Data\Backups\' + @.FName + '"'
> INSERT INTO #Errors (Results)
> exec @.Return = master..xp_cmdshell @.Delete
>
> IF @.@.RowCount > 1 OR @.Return = 1
> BEGIN
> SET @.Error = -1
> SET @.Msg = 'Error while Deleting file ' + @.FName
> GOTO On_Error
> END
> PRINT 'Deleted ' + @.FName + ' at ' +
> CONVERT(VARCHAR(28),GETDATE(),113)
> FETCH NEXT FROM curDir INTO @.Fname
> END
> CLOSE curDir
> DEALLOCATE curDir
> DROP TABLE #DirList
> DROP TABLE #Errors
> RETURN @.Error
> On_Error:
> BEGIN
> IF @.Error <> 0
> BEGIN
> SELECT @.Msg + '. Error # ' + CAST(@.Error AS VARCHAR(10))
> RAISERROR(@.Msg,12,1)
> RETURN @.Error
> END
> END
> GO
>
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:041D4B77-15AB-42F8-80B6-AB740408D913@.microsoft.com...
>
>
|||Do you mean this line: INSERT INTO #dirlist (FName)
exec @.Return = master..xp_cmdshell 'dir /OD C:\Data\Backups\*.Bak'
Can you be so kind as to list exactly what the error is that you are
getting? I don't understand why you can't see this table? Are you running
the select in the same connection as the one that created the table? If not
you won't be able to see it since it is a local temporary table. You really
need to be a little more specific on what you are doing and what the real
errors are that you are getting otherwise I am simply guessing.
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:61C4CAE3-8398-4905-AE4C-411D6A55DA12@.microsoft.com...[vbcol=seagreen]
> Thanks.. I'm going through the code and I'm running into an error when you
> use the directory list command. I'm not able to view the temporary table
> to
> see if data is being stored.
> "Andrew J. Kelly" wrote:
|||When I try to query the temp table within the same connection, it says the
object is invalid. So I'm not sure the script is working because I can't view
the table.
I don't have any more specific errors. Sorry if I was vague in my previous
messages.
"Andrew J. Kelly" wrote:

> Do you mean this line: INSERT INTO #dirlist (FName)
> exec @.Return = master..xp_cmdshell 'dir /OD C:\Data\Backups\*.Bak'
> Can you be so kind as to list exactly what the error is that you are
> getting? I don't understand why you can't see this table? Are you running
> the select in the same connection as the one that created the table? If not
> you won't be able to see it since it is a local temporary table. You really
> need to be a little more specific on what you are doing and what the real
> errors are that you are getting otherwise I am simply guessing.
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:61C4CAE3-8398-4905-AE4C-411D6A55DA12@.microsoft.com...
>
>
|||Sonya,
The temp table is created inside of a stored procedure. If you run the sp
and it creates a temp table it only lives for the duration of the sp
execution. So by the time you query for it the table has been destroyed.
This is how local temp tables work and is the whole purpose of them. If you
wan to play around with it you need to remove the code from the sp and run
it as a standard batch in query analyzer.
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:D7770A92-6EEA-4B7D-8C9C-6A9DFBC92046@.microsoft.com...[vbcol=seagreen]
> When I try to query the temp table within the same connection, it says the
> object is invalid. So I'm not sure the script is working because I can't
> view
> the table.
> I don't have any more specific errors. Sorry if I was vague in my previous
> messages.
> "Andrew J. Kelly" wrote:
|||Ok, I was doing that as well. I'll have to remove or reference the return
function differently. Thank you for all of your help. The message that I
received is the following:
Server: Msg 178, Level 15, State 1, Line 92
A RETURN statement with a return value cannot be used in this context.
Server: Msg 178, Level 15, State 1, Line 99
A RETURN statement with a return value cannot be used in this context.
"Andrew J. Kelly" wrote:

> Sonya,
> The temp table is created inside of a stored procedure. If you run the sp
> and it creates a temp table it only lives for the duration of the sp
> execution. So by the time you query for it the table has been destroyed.
> This is how local temp tables work and is the whole purpose of them. If you
> wan to play around with it you need to remove the code from the sp and run
> it as a standard batch in query analyzer.
> --
> Andrew J. Kelly SQL MVP
>
> "Sonya" <Sonya@.discussions.microsoft.com> wrote in message
> news:D7770A92-6EEA-4B7D-8C9C-6A9DFBC92046@.microsoft.com...
>
>
|||You can't use RETURN if you are not in a stored procedure. Just comment
that line out for your testing.
Andrew J. Kelly SQL MVP
"Sonya" <Sonya@.discussions.microsoft.com> wrote in message
news:D62D330E-685E-4048-8635-FC6943AC9F50@.microsoft.com...[vbcol=seagreen]
> Ok, I was doing that as well. I'll have to remove or reference the return
> function differently. Thank you for all of your help. The message that I
> received is the following:
> Server: Msg 178, Level 15, State 1, Line 92
> A RETURN statement with a return value cannot be used in this context.
> Server: Msg 178, Level 15, State 1, Line 99
> A RETURN statement with a return value cannot be used in this context.
>
> "Andrew J. Kelly" wrote:

No comments:

Post a Comment