Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Stored Procedure Bug

Posted on 2006-07-20
10
Medium Priority
?
653 Views
Last Modified: 2008-02-07
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
Comment
Question by:benc007
10 Comments
 
LVL 7

Expert Comment

by:FDzjuba
ID: 17150143
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
 

Author Comment

by:benc007
ID: 17150495
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17150520
it should be


master.dbo.xp_cmdshell
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:benc007
ID: 17150655
Will master.dbo.xp_cmdshell work on both Windows XP and Windows 2000 Server?
0
 

Author Comment

by:benc007
ID: 17150789
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17151295
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17151297
Also check whether, by itself the sp is running ?
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 17151912
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
 

Author Comment

by:benc007
ID: 17152396
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
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 200 total points
ID: 17152532
>'master.dbo.xp_shellcmd'. The stored procedure will still be created.

this should be    

  MASTER.DBO.XP_CMDSHELL
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question