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

SQL Server Script Syntax error

I'm having trouble with a script...  I'm fairly new at SQL Server, but determined to get better.
The Purpose of my script is to take a database, back it up to a file without a time/date stamp on it.  I want to do this so I can use an SFTP script to push the file to one of my customers on a weekly basis.  The SFTP script being used has a requirement for the file to be the same name for it to automatically run each Saturday.  The script is below... i am getting this error:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'E'.
Msg 319, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

Can someone point me in the right direction?  I must be missing something easy.  The directory does exist.  Why is it having a problem with the E for my E drive, and what is wrong with my WITH.  (This is SQL 2005 Std).  


DECLARE @Filename varchar(256)
DECLARE @BkpPath varchar(256) 
DECLARE @DBName varchar(256)

SET @BkpPath = 'E:\SQL Backups\TEST\'

SELECT @DBName = 'MyDatabase'
SELECT @FileName = @DBName + 'Backup.BAK'
--SELECT @BkpPath = 'E:\SQL Backups\TEST\'
SELECT @FileName = @BkpPath + @FileName

DECLARE @SQL varchar(max)

SELECT @SQL = 
'BACKUP DATABASE ' + @DBName + ' TO 
	DISK ='+ @FileName 
	WITH NOFORMAT, INIT,SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

EXEC (@SQL)

Open in new window

0
Robert Hamel
Asked:
Robert Hamel
  • 4
  • 2
2 Solutions
 
Rajkumar GsSoftware EngineerCommented:
In first look, I feel this change

SELECT @SQL =
'BACKUP DATABASE ' + @DBName + ' TO
 DISK ='+ @FileName + ' WITH NOFORMAT, INIT,SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
0
 
Rajkumar GsSoftware EngineerCommented:
seems like you missed appending + ' after @FileName

Raj
0
 
Rajkumar GsSoftware EngineerCommented:
Also if required , try this change also


SET @BkpPath = 'E:\SQL Backups\TEST\'

to


SET @BkpPath = @'E:\SQL Backups\TEST\'

Raj
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Rajkumar GsSoftware EngineerCommented:
>> Incorrect syntax near the keyword 'with'.

Regarding this error, I cannot see any 'with' in the posted query

If any
change
WITH
to
;WITH

Raj
0
 
SharathData EngineerCommented:
try this.
DECLARE @Filename varchar(256)
DECLARE @BkpPath varchar(256) 
DECLARE @DBName varchar(256)

SET @BkpPath = 'E:\SQL Backups\TEST\'

SELECT @DBName = 'MyDatabase'
SELECT @FileName = @DBName + 'Backup.BAK'
--SELECT @BkpPath = 'E:\SQL Backups\TEST\'
SELECT @FileName = @BkpPath + @FileName

DECLARE @SQL varchar(max)

SELECT @SQL = 
'BACKUP DATABASE ''' + @DBName + ''' TO 
	DISK ='+ @FileName + '
	WITH NOFORMAT, INIT,SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

exec (@SQL)

Open in new window

0
 
Robert HamelSenior .NET developerAuthor Commented:
I tried the items above from both of you, none worked.
when I added @ in front of 'E:\SQL Backups\TEST\'
it says you must declare scalar variable.

There is a WITH in the @SQL variable, line 17.  Adding the semi colon did make the with error go away.  Thanks.

When I tried Sharath's code, i see this, not liking the database name for some reason.

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'MyDatabase'.



DECLARE @Filename varchar(256)
DECLARE @BkpPath varchar(256) 
DECLARE @DBName varchar(256)

SET @BkpPath = 'E:\SQL Backups\TEST\'

SELECT @DBName = 'MyDatabase'
SELECT @FileName = @DBName + 'Backup.BAK'
--SELECT @BkpPath = 'E:\SQL Backups\TEST\'
SELECT @FileName = @BkpPath + @FileName

DECLARE @SQL varchar(max)

SELECT @SQL = 
'BACKUP DATABASE ''' + @DBName + ''' TO 
	DISK ='+ @FileName + '
	;WITH NOFORMAT, INIT,SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

exec (@SQL)

Open in new window

0
 
SharathData EngineerCommented:
Thats my mistake. Try this.
DECLARE @Filename varchar(256)
DECLARE @BkpPath varchar(256) 
DECLARE @DBName varchar(256)

SET @BkpPath = 'E:\SQL Backups\TEST\'

SELECT @DBName = 'MyDatabase'
SELECT @FileName = @DBName + 'Backup.BAK'
--SELECT @BkpPath = 'E:\SQL Backups\TEST\'
SELECT @FileName = @BkpPath + @FileName

DECLARE @SQL varchar(max)

SELECT @SQL = 
'BACKUP DATABASE ' + @DBName + ' TO 
	DISK ='''+ @FileName + '''
	WITH NOFORMAT, INIT,SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

exec (@SQL)

Open in new window

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

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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