?
Solved

What is wrong with the following job?

Posted on 2011-09-22
7
Medium Priority
?
1,869 Views
Last Modified: 2012-05-12
Hi, I got this error when I run the bellow job:

"Executed as user: NT AUTHORITY\SYSTEM. Unclosed quotation mark after the character string 'D:\Development\Menafn_Sql_Backup_daily\menafnfullbackup2292011'. [SQLSTATE 42000] (Error 105)  Incorrect syntax near 'D:\Development\Menafn_Sql_Backup_daily\menafnfullbackup2292011'. [SQLSTATE 42000] (Error 102).  The step failed."


Here is th job

DECLARE @backupDate nvarchar(8)
declare @deletedate datetime
declare @statement nvarchar(100)
set @backupDate =cast(day(getdate())as nvarchar)
				+cast (month(getdate())as nvarchar)
				+cast (year(getdate())as nvarchar)
				
 select @deletedate= DATEADD(dd,-3,GETDATE())				
execute xp_delete_file 0, 'D:\Development\Menafn_Sql_Backup_daily', 'bak', @deletedate

set @statement ='BACKUP DATABASE menafn 
		TO DISK = ''D:\Development\Menafn_Sql_Backup_daily\menafnfullbackup'
		+@backupdate+
		'.bak'''
 
execute (@statement)

Open in new window

0
Comment
Question by:Sindibad_123
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 36579866
I think you want

set @statement ='Backup DATABASE menafn TO DISK = ''D:\Development\Menafn_Sql_Backup_daily\menafnfullbackup' +@backupdate+ '.bak'''
0
 

Author Comment

by:Sindibad_123
ID: 36579926
Still the same error:

Executed as user: NT AUTHORITY\SYSTEM. Unclosed quotation mark after the character string 'D:\Development\Menafn_Sql_Backup_daily\menafnfullbackup2292011.bak'. [SQLSTATE 42000] (Error 105)  Incorrect syntax near 'D:\Development\Menafn_Sql_Backup_daily\menafnfullbackup2292011.bak'. [SQLSTATE 42000] (Error 102).  The step failed.
0
 
LVL 13

Accepted Solution

by:
Philip Pinnell earned 2000 total points
ID: 36579963
Ha!

try
declare @statement nvarchar(200)
0
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.

 
LVL 21

Expert Comment

by:JestersGrind
ID: 36579996
You can comment out the execute and substitute in PRINT @Statement to see what exactly is being executed.  The issue usually becomes obvious at that point.  You will also be able to run the output to verify that it works.

Greg

0
 

Author Comment

by:Sindibad_123
ID: 36579999
Good catch
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 36580010
Andy nailed it.  You variable length is too short so it's truncating the string.  

Greg

0
 
LVL 13

Expert Comment

by:Philip Pinnell
ID: 36580064
Easily done.

Thanks
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

719 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