Post reply

Incorrect syntax near 'Index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required.

  • November 27, 2008 at 3:45 pm

    #73115

    I have written following query in which I have not written Index anywhere...

    declare @strColumns varchar(1000)

    declare @col varchar(30)

    declare @addCol int

    --INSERT INTO test1.Extensions.LayoutDefinitions SELECT * FROM Old_DB.Extensions.LayoutDefinitions

    if exists(SELECT OBJECT_NAME([object_id]) FROM sys.columns WHERE is_identity = 1 and OBJECT_NAME([object_id]) = 'LayoutDefinitions')

    begin

    SET IDENTITY_INSERT test1.Extensions.LayoutDefinitions ON

    end

    set @addCol = 0

    DECLARE c1 CURSOR READ_ONLY FOR

    select name from sys.all_columns where object_id = (select object_id from sys.tables where name = 'LayoutDefinitions')

    OPEN c1

    FETCH NEXT FROM c1 INTO @col

    set @strColumns = @col

    WHILE @@FETCH_STATUS = 0

    BEGIN

    if @addCol=0

    begin

    set @addCol = 1

    end

    else

    begin

    set @strColumns = @strColumns + ',' + @col

    end

    FETCH NEXT FROM c1 INTO @col

    END

    CLOSE c1

    DEALLOCATE c1

    exec('INSERT INTO test1.Extensions.LayoutDefinitions (' + @strColumns + ') SELECT ' + @strColumns + ' FROM Old_DB.Extensions.LayoutDefinitions')

    if exists(SELECT OBJECT_NAME([object_id]) FROM sys.columns WHERE is_identity = 1 and OBJECT_NAME([object_id]) = 'LayoutDefinitions')

    begin

    SET IDENTITY_INSERT test1.Extensions.LayoutDefinitions OFF

    end

    but it is giving following error

    Msg 1018, Level 15, State 1, Line 1 Incorrect syntax near 'Index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required.

    Please Help.

    -Nishant

  • November 27, 2008 at 3:58 pm

    #904501

    just guessing, but one of your columns that you are gathering dynamically might be named INDEX and causes a keyword error in the syntax check;

    PRINT your SQL statement just before you execute it, and change this line to the second one in red:

    set @strColumns = @strColumns + ',' + @col

    set @strColumns = @strColumns + ',' + '[' + @col + ']'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 27, 2008 at 6:32 pm

    #904520

    Thanks a ton Lowell...that was the exact problem

    -Nishant

  • April 3, 2015 at 11:12 am

    #1788623

    Hi Lowell...

    Thanks a LOT..

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply