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'

closed as off-topic by Max Vernon, RLF, Michael Green, Julien Vavasseur, RolandoMySQLDBA May 28 '16 at 10:24

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, RLF, Michael Green, Julien Vavasseur, RolandoMySQLDBA
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    If the error happens when you change the directory paths... Are you making them longer? If so maybe you are exceeding 400 characters with the final concatenated string and getting truncation. – Martin Smith May 27 '16 at 17:43
  • @MaxVernon I have check the ProjectDB column and it doesn't have any single quotes. MartinSmith yes, the directory paths are longer but the final path is not over 400 characters which I was hoping was the issues :( – unreal May 27 '16 at 18:22
  • @MartinSmith It was the 400 characters after all I bumped it up and it seem to work just fine. Thanks! – unreal May 27 '16 at 18:59
  • 3
    NOUN instead of NOUNLOAD definitely looks like truncation to me. – Martin Smith May 27 '16 at 19:00
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.