asked on

SQL Server backup fails from vb6 application

I have a backup stored procedure gratiously provided by a member of this great establishment. It runs fine from the server but fails occasionally when run from my vb6 app. It doesn't timeout because it only runs about 20 seconds and the timeout is set for 60 seconds.

The error message simply says: 'Backup Failed'

Any ideas would be appreciated!!!

Jerry

tlovie

Does the VB6 application run with the same login credentials as when you run it from the server? What does the proc do?

JDL129

ASKER

Thanks for the response!!!

Attached is the stored procedure that I run from vb6. The error I get is:Backup Failed
The application logs into the SQLServer database and calls the attached.

Thanks,

Jerry

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Backup]
@BackupPath nvarchar(200)
As
Declare @WeekDay tinyint
     Declare @Backup_File nvarchar(200)
     Declare @Desc nvarchar(255)                 
     Declare @Name nvarchar(128)                 

SET @WeekDay = DATEPART(weekday, CURRENT_TIMESTAMP)

IF @WeekDay = 7 -- change this value to meet the weekday you want to perform FULL BACKUP
BEGIN

     Set @Backup_File = @BackupPath + '\BarCodeSQL.bak'
     Set @Desc = 'Full Backup'
     Set @Name = 'Barcode FULL BACKUP'

     BACKUP DATABASE [BarCodeSQL] TO DISK = @Backup_File 
      WITH 
       DESCRIPTION = @Desc
     ,NAME = @Name
Return @@Error
END
ELSE
BEGIN

     Set @Backup_File = @BackupPath + '\BarCodeSQL.bak_' + DATENAME(weekday, CURRENT_TIMESTAMP) + '.bak'
     Set @Desc = 'Incremental Backup'
     Set @Name = 'Barcode ' + DATENAME(weekday, CURRENT_TIMESTAMP) + ' DIFF BACKUP'

     BACKUP DATABASE [BarCodeSQL] TO DISK = @Backup_File
     WITH DIFFERENTIAL,
      DESCRIPTION = @Desc, NOFORMAT, NOINIT
     ,NAME = @Name
     ,SKIP, NOREWIND, NOUNLOAD
Return @@Error
END

Open in new window

ASKER CERTIFIED SOLUTION

SQLLover

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JDL129

ASKER

Thanks for the input!!!!!!!!!!