1

I have this script that works just fine but when I change the directory paths it seems to get this syntax error "unclosed quotation mark after the character string" I have looked over this for hours and can't fine the unclosed quotation mark and any other issues that would cause this.

DECLARE @db VARCHAR(50), 
@sql NVARCHAR(400),
@country varchar(100)
DECLARE barcardi cursor for 
select [ProjectDB] from [DBA].[dbo].[BacardiExport] order by [Country] asc
open barcardi 
fetch next from barcardi into @db
while @@FETCH_STATUS = 0
    begin
        set @country = (select country from [DBA].[dbo].[BacardiExport] where [ProjectDB] = @db)
        set @sql = 'BACKUP DATABASE ['+@db+'] TO  DISK = N''D:\ConfirmitN02BackupNew\BacardiProjectBackup\Bacardi_'+@country+'_'+@db+'.bak'' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
        exec sp_executesql @sql
        --print @sql

        set @country = (select country from [DBA].[dbo].[BacardiExport] where [ProjectDB] = @db)
        set @sql = 'RESTORE DATABASE [Bacardi_'+@country+'_'+@db+'] FROM  DISK = N''D:\ConfirmitN02BackupNew\BacardiProjectBackup\Bacardi_'+@country+'_'+@db+'.bak'' WITH  FILE = 1,  MOVE N'''+@db+'_dat'' TO N''D:\ConfirmitN02-MDF\confirmit_sql_data\Bacardi_'+@country+'_'+@db+'.mdf'',  MOVE N'''+@db+'_log'' TO N''D:\ConfirmitN02-LDF\confirmit_sql_log\Bacardi_'+@country+'_'+@db+'.ldf'',  NOUNLOAD,  REPLACE,  STATS = 5'
        exec sp_executesql @sql
        --print @sql

        set @country = (select country from [DBA].[dbo].[BacardiExport] where [ProjectDB] = @db)
        set @sql = 'UPDATE [DBA].[dbo].[BacardiExport] set [CopyProjectDB] =  ''Bacardi_'+@country+'_'+@db+''' where [ProjectDB] = '''+ @db +''''
        exec sp_executesql @sql
        --print @sql

        update [DBA].[dbo].[BacardiExport] set [Copied] = 1 where [ProjectDB] = @db
        update [DBA].[dbo].[BacardiExport] set [ProcessDate] = getdate() where [ProjectDB] = @db

    fetch next from barcardi into @db
    end
close barcardi
deallocate barcardi

Here is a bit of the output.

SSIS package "D:\ConfirmitN02BackupNew\Bacardi\DataExport2\DataExport2\Package.dtsx" starting. Error: 0xC002F210 at Copy Bacardi Databses, Execute SQL Task: Executing the query "DECLARE @db VARCHAR(50), @sql NVARCHAR(400), @c..." failed with the following error: "Unclosed quotation mark after the character string 'D:\ConfirmitN02-LDF\confirmit_sql_log\Bacardi_Belgium_survey_p1622247398.ld'. Incorrect syntax near 'D:\ConfirmitN02-LDF\confirmit_sql_log\Bacardi_Belgium_survey_p1622247398.ld'. Unclosed quotation mark after the character string 'D:\ConfirmitN02-LDF\confirmit_sql_log\Bacardi_Germany_survey_p1622259397.ld'. Incorrect syntax near 'D:\ConfirmitN02-LDF\confirmit_sql_log\Bacardi_Germany_survey_p1622259397.ld'. 'NOUN' is not a recognized RESTORE option. Incorrect syntax near ','. 11 percent processed. 20 percent processed. 30 percent processed. 40 percent processed. 50 percent processed. 60 percent processed. 70 percent processed. 80 percent processed. 90 percent processed. Processed 11584 pages for database 'survey_p1622247398', file 'survey_p1622247398_dat' on file 1. 100 percent processed. Processed 5 pages for database 'survey_p1622247398', file 'survey_p1622247398_log' on file 1. BACKUP DATABASE successfully processed 11589 pages in 1.909 seconds (47.425 MB/sec). BACKUP DATABASE [survey_p1622247398] TO DISK = N'D:\ConfirmitN02BackupNew\BacardiProjectBackup\Bacardi_Belgium_survey_p1622247398.bak' WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10 RESTORE DATABASE [Bacardi_Belgium_survey_p1622247398] FROM DISK = N'D:\ConfirmitN02BackupNew\BacardiProjectBackup\Bacardi_Belgium_survey_p1622247398.bak' WITH FILE = 1, MOVE N'survey_p1622247398_dat' TO N'D:\ConfirmitN02-MDF\confirmit_sql_data\Bacardi_Belgium_survey_p1622247398.mdf', MOVE N'survey_p1622247398_log' TO N'D:\ConfirmitN02-LDF\confirmit_sql_log\Bacardi_Belgium_survey_p1622247398.ld UPDATE [DBA].[dbo].[BacardiExport] set [CopyProjectDB] = 'Bacardi_Belgium_survey_p1622247398' where [ProjectDB] = 'survey_p1622247398'

1
  • 3
    NOUN instead of NOUNLOAD definitely looks like truncation to me. Commented May 27, 2016 at 19:00

1 Answer 1

6

Glad you got the truncation issue figured out, but may I offer an alternative that won't be prone to that, eliminates the need for cursor scaffolding, etc.?

DECLARE @src nvarchar(max) = N'', @sql nvarchar(max) = N'';

SELECT @src = N'
  BACKUP DATABASE $qdb$ TO  DISK 
  = N''D:\ConfirmitN02BackupNew\BacardiProjectBackup\Bacardi_$c$_$db$.bak''
   WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD,  STATS = 10;

  RESTORE DATABASE [Bacardi_$c$_$db$] FROM DISK 
  = N''D:\ConfirmitN02BackupNew\BacardiProjectBackup\Bacardi_$c$_$db$.bak'' 
   WITH  FILE = 1,  MOVE N''$db$_dat'' 
   TO N''D:\ConfirmitN02-MDF\confirmit_sql_data\Bacardi_$c$_$db$.mdf'',
   MOVE N''$db$_log'' TO N''D:\ConfirmitN02-LDF\confirmit_sql_log\Bacardi_$c$_$db$.ldf'',
   NOUNLOAD,  REPLACE,  STATS = 5;

  UPDATE [DBA].[dbo].[BacardiExport] set [CopyProjectDB] = ''Bacardi_$c$_$db$'',
    Copied = 1, ProcessDate = GETDATE() where [ProjectDB] = ''$db$'';';

SELECT @sql += REPLACE(REPLACE(REPLACE(@src,   
  N'$c$',country),N'$db$',ProjectDB),N'$qdb$',QUOTENAME(ProjectDB))
FROM DBA.dbo.BacardiExport;

PRINT @sql; -- this may not display everything, depending on how many rows
-- EXEC sys.sp_executesql @sql;

Not the answer you're looking for? Browse other questions tagged or ask your own question.