SQLTeam.com | Weblogs | Forums

Incorrect syntax near 'C:'

sql2008r2

#1

Hi Experts !

I am unable to point the issue error

SET @StandByFile='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_TEST.BAK'

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

USE master;
GO
SET NOCOUNT ON 
--- variable declaration
DECLARE  @dbName sysname
DECLARE  @backupPath NVARCHAR(500) 
DECLARE  @cmd NVARCHAR(500)
DECLARE  @filelist TABLE (backupFile NVARCHAR(255))
DECLARE  @StandByFile NVARCHAR(max)
DECLARE  @NewLocation nvarchar(50)
DECLARE  @backupFile NVARCHAR(500)

 --2 - initialze variables
SET @dbName='TEST'
SET @backupPath='D:\ReplicationViaFTPServer\'  
SET @NewLocation='D:\ReplicationViaFTPServer\LogBackups\' 
---SET @StandByFile='D:\MSSQL10_50MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_AVM.BAK'
SET @StandByFile='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_AVM.BAK'

BEGIN TRY

--- Get List of Files
SET @cmd = 'DIR/b' + @backupPath INSERT INTO @filelist(backupFile)

EXEC master.sys.xp_cmdShell @cmd
---4 check for log backups
DECLARE backupFiles CURSOR FOR SELECT  backupFile FROM @fileList WHERE backupFile  LIKE '%.TRN' AND backupFile LIKE @dbName + '%'
OPEN backupFIles

--- Loop through all th files for the database
FETCH NEXT FROM backupFiles INTO @backupFile
WHILE @@FETCH_STATUS =0
BEGIN

--- Restore log files
SET @cmd ='RESTORE LOG' + @dbName + 'FROM DISK =''' + @backupPath + @backupFile + '''WITH NORECOVERY' 
EXECUTE (@cmd) 
--- delete for move log file
SET @cmd ='del' +@backupPath + @backupFile 
SET @cmd = 'MOVE' +@backupPath + @backupFile + '' + @NewLocation

EXEC master.sys.xp_cmdshell @cmd

FETCH NEXT FROM backupFiles INTO @backupFile
END

END TRY
BEGIN CATCH

CLOSE backupFiles
DEALLOCATE backupFiles
---5  put database on standby state
SET @cmd ='RESTORE DATABASE ' + @dbName +'WITH STANDBY=' + @StandByFile 
EXECUTE (@cmd) 
exec master..xp_cmdshell 'echo Error in Restore logs > D:\ReplicationViaFTPServer\LogBackups\ErrorLog\log.txt'
END CATCH
CLOSE backupFiles
DEALLOCATE backupFiles

--- 5  put database on standby state
SET @cmd ='RESTORE  DATABASE ' + @dbName +'WITH STANDBY=' + @StandByFile 
EXECUTE (@cmd)

#2

You need to PRINT (or display/log in some other way) the Dynamic SQL and Dynamic @CMD which you are generating, and then try running the generate code stand-alone. That will enable you to find the bugs in it, and THEN, once you've got it working standalone, feed those fixes/changes back into the original code that generates the dynamic SQ / @CMD


#3

Thanks Kristen,
When i try to Comment this area

SET @StandByFile='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_AVM.BAK'

the code run successfully but when try to uncomment having the syntax error ? not sure


#4

Did you bother to try using PRINT to capture the ACTUAL SQL / CMD being use and then test that by running it standalone?

If you did, and you cannot see what is bust, then post THAT command and we can comment on what is wrong with that.


#5

By using PRINT @cmd finding below

DIR/bD:\ReplicationViaFTPServer\
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'C:'.
RESTORE  DATABASE TESTWITH STANDBY=C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_TEST.BAK

#6

STANDBY path / filename needs to be quoted.

Also needs a FROM clause etc.


#7

Changing areas works...

SET @StandByFile='''C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ROLLBACK_UNDO_TEST.BAK''' 

SET @cmd ='RESTORE DATABASE '+ @dbName + ' '   + 'WITH STANDBY =' + @StandByFile

#8

Personally I would have changed the SET @cmd command, rather than the SET @StandByFile because you have added the extra quotes in the construction of the command elsewhere, and if someone needs to change the filename, in future, it would be less obvious that it needs to, also, be double-quoted (at the definition point, rather than in the command construction).

Either way, I would make all the "File Path", "File Name", definition syntax consistent.

Or, if they need to be different, add a comment indicating that the data must be double quoted.