Solved

What is wrong with the following job?

Posted on 2011-09-22
7
1,819 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 500 total points
ID: 36579963
Ha!

try
declare @statement nvarchar(200)
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

624 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