Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1920
  • 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
 
Philip PinnellCommented:
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
 
Philip PinnellCommented:
Ha!

try
declare @statement nvarchar(200)
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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
 
Philip PinnellCommented:
Easily done.

Thanks
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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