sql - Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '-'. I am missing something

Could someone please explain to me why I am getting this error? I know it has something to do with the DB name having a dash in it.

declare @dname varchar(80)
declare @cmnd varchar(2000)

DECLARE get_db CURSOR FOR
   select name from sysdatabases where dbid > 9

open get_db
FETCH NEXT from get_db into @dname
WHILE(@@FETCH_STATUS = 0)
begin
          set @cmnd = 'if (select count(name) from ' + @dname + '.dbo.sysobjects where name = ''SQLLOG'')=1 begin TRUNCATE TABLE ' + @dname + '.dbo.sqllog end'
          exec (@cmnd)
          --print @cmnd
FETCH NEXT from get_db into @dname
end
close get_db
deallocate get_db

1 Answer

  1. Joe- Reply

    2019-11-15

    @dname will hold values from sysdatabases. If one of the databases contains -, then you need to either enclose the variable into ` or use quotename. Basically, whenever you run a string as a command, you need to make sure that there are no possible problems, especially if the data was added by users.

    set @cmnd = 'if (select count(name) from `' + @dname + '`.dbo.sysobjects where name = 'SQLLOG')=1 begin TRUNCATE TABLE `' + @dname + '`.dbo.sqllog end'
    

    or

    set @cmnd = 'if (select count(name) from quotename(\'' + @dname + '\').dbo.sysobjects where name = 'SQLLOG')=1 begin TRUNCATE TABLE ' + @dname + '.dbo.sqllog end'
    

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>