Solved

SQL Server backup fails from vb6 application

Posted on 2010-11-09
4
548 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
  • 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Degrading on Write 13 72
Is there any Easy way to copy CSV to SQL Server using C# 3 98
How to use TOP 1 in a T-SQL sub-query? 14 48
SQL query with cast 38 59
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

685 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