[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 655
  • Last Modified:

Stored Procedure Bug

I have a table tblBackupFiles in the Master database that stores the creationDate and BackupFileName of my files.  sp_DeleteOldBackupFiles gets files that are older than 7 days and deletes them.  When I try to compile sp_DeleteOldBackupFiles, I get:

Server: Msg 170, Level 15, State 1, Procedure sp_DeleteOldBackupFiles, Line 17
Line 17: Incorrect syntax near '+'.

Code:
Create Procedure sp_DeleteOldBackupFiles
AS
Declare @FileName as varchar(2000)


DECLARE CurDBNames CURSOR FOR Select BackupFileName From Master.dbo.tblBackupFiles Where CreationDate < getdate()-7


   OPEN CurDBNames
   FETCH NEXT FROM CurDBNames INTO @FileName
 
   WHILE @@FETCH_STATUS = 0
   BEGIN
            
            EXEC master.xp_shellcmd 'Del "' + @FileName +'"'
            FETCH NEXT FROM CurDBNames INTO @FileName
   END

   CLOSE CurDBNames
   DEALLOCATE CurDBNames


GO
0
benc007
Asked:
benc007
1 Solution
 
FDzjubaCommented:
you can only pass variables or completed string to SP

Create Procedure sp_DeleteOldBackupFiles
AS
Declare @FileName as varchar(2000)


DECLARE CurDBNames CURSOR FOR Select BackupFileName From Master.dbo.tblBackupFiles Where CreationDate < getdate()-7


   OPEN CurDBNames
   FETCH NEXT FROM CurDBNames INTO @FileName
 
DECLARE @passString nvarchar(600);

   WHILE @@FETCH_STATUS = 0
   BEGIN
          SET @passString  = 'Del "' + @FileName +'"';
          EXEC master.xp_shellcmd @passString ;
          FETCH NEXT FROM CurDBNames INTO @FileName
   END

   CLOSE CurDBNames
   DEALLOCATE CurDBNames

0
 
benc007Author Commented:
The stored procedure gets created but I get an error:

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'master.xp_shellcmd'. The stored procedure will still be created.
0
 
nmcdermaidCommented:
it should be


master.dbo.xp_cmdshell
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
benc007Author Commented:
Will master.dbo.xp_cmdshell work on both Windows XP and Windows 2000 Server?
0
 
benc007Author Commented:
I am trying to call sp_DeleteOldBackupFiles from another stored procedure sp_TEST tjhat is in the master database using:

exec sp_DeleteOldBackupFiles

but it's not working and I don't get an error when I compile sp_TEST.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
benc007,
> Will master.dbo.xp_cmdshell work on both Windows XP and Windows 2000 Server?

Yes, if you have access to this

> but it's not working and I don't get an error when I compile sp_TEST

can you post the code
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Also check whether, by itself the sp is running ?
0
 
LowfatspreadCommented:
have you checked this...


declare @rc int
WHILE @@FETCH_STATUS = 0
   BEGIN
          SET @passString  = 'Del "' + @FileName +'"';
          print @passstring   -- for debug use certainly ... but i'd want to know what had been deleted...
          EXEC @rc = master.dbo.xp_shellcmd @passString ;
          print @rc
          FETCH NEXT FROM CurDBNames INTO @FileName
   END

 
0
 
benc007Author Commented:
LowFatSpread,

I get an error:

Dropping Procedure sp_DeleteOldBackupFiles
Creating Procedure sp_DeleteOldBackupFiles

Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'master.dbo.xp_shellcmd'. The stored procedure will still be created.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>'master.dbo.xp_shellcmd'. The stored procedure will still be created.

this should be    

  MASTER.DBO.XP_CMDSHELL
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now