Solved

SQL Server backup fails from vb6 application

Posted on 2010-11-09
4
549 Views
Last Modified: 2012-05-10
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
0
Comment
Question by:JDL129
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 7

Expert Comment

by:tlovie
ID: 34096653
Does the VB6 application run with the same login credentials as when you run it from the server?  What does the proc do?
0
 

Author Comment

by:JDL129
ID: 34097569
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

0
 
LVL 2

Accepted Solution

by:
SQLLover earned 125 total points
ID: 34114355
run a profiler to monitor what happed , i think the problem is in passing @BackupPath parameter from vb  
0
 

Author Closing Comment

by:JDL129
ID: 34138403
Thanks for the input!!!!!!!!!!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

710 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