• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2096
  • Last Modified:

What is wrong with the following job?

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
Sindibad_123
Asked:
Sindibad_123
  • 3
  • 2
  • 2
1 Solution
 
Atdhe NuhiuCommented:
I think you want

set @statement ='Backup DATABASE menafn TO DISK = ''D:\Development\Menafn_Sql_Backup_daily\menafnfullbackup' +@backupdate+ '.bak'''
0
 
Sindibad_123Author Commented:
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
 
Atdhe NuhiuCommented:
Ha!

try
declare @statement nvarchar(200)
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
JestersGrindCommented:
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
 
Sindibad_123Author Commented:
Good catch
0
 
JestersGrindCommented:
Andy nailed it.  You variable length is too short so it's truncating the string.  

Greg

0
 
Atdhe NuhiuCommented:
Easily done.

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now