Curious Case of .. Syntax Error. Or may be not?

  • Not sure- to laugh or cry :-)

    I created small SP to rebuild/reorganize indexes on sql server (2008 R2) DBs (only for indexes that require this action certainly)

    Essentially it’s simple cursor- during each iteration (let say 100 indexes to rebuild from 5 different DBs) db_name/tbl_name/index are extracted from ahead prepared table (tblMain) to create dynamic string and execute it. Each iteration completes with tblMain update to report particular index action completion (done = 1, start/end = GETDATE()). Simple like truth.

    Now the tricky part.

    When SP complets (all 100 indexes rebuilt or reorganized successfully, i.e. every one has done = 1) SP returns.. error: “Msg 102, Level 15, State 1, Line 1; Incorrect syntax near '('.”

    Oops :w00t:

    If there is syntax error then SP should not start at all- correct?

    Where is this mystery ‘(‘ coming from?

    Funny enough but the only place where this ‘(‘ bracket used is GETDATE() function.

    But it was used 100 times for 100 indexes to update start/end date in tblMain without any problem?!

    It’s certainly not syntax error IMO, but what is it?

    Intensive Google search returns nothing

    Looking for a help (any clue) from the “best of the best” (dead serious)

    Thanks in advance

    Yuri

  • Well, since we can't see what you see it is a little hard to provide you with any help. How about posting the code for the stored procedure.

  • Here we are (sorry, if this is wrong way to post script):

    ******************************************************************

    [font="Arial"]USE [DBA]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- ============================================================

    --rebuild index using dbo.index_rebuild_log ahead prepared data

    -- ============================================================

    CREATE PROCEDURE [dbo].[usp_IndexRebuild]

    AS

    BEGIN

    SET NOCOUNT ON;

    declare @start datetime

    declare @id int, @dbname varchar(50), @tblname varchar(100), @indexname varchar(100), @schname varchar(20), @action char(10)

    declare @sql nvarchar(1000) = ''

    DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR

    SELECT id, dbname, schname, tblname, indexname, [action]

    FROM dbo.index_rebuild_log --ahead prepared fragmentation data

    WHERE index_done = 0

    ORDER BY id

    OPEN curDB

    FETCH NEXT FROM curDB INTO @id, @dbname, @schname, @tblname, @indexname, @action

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @start = GETDATE()

    set @sql = 'ALTER INDEX ' + @indexname + ' ON ' + @dbname + '.' + @schname + '.' + @tblname + ' ' + @action

    --print @sql

    exec sp_executesql @sql

    update dbo.index_rebuild_log --report action completion

    set

    index_done = 1,

    index_start = @start,

    index_end = GETDATE()

    where

    id = @id

    FETCH NEXT FROM curDB INTO @id, @dbname, @schname, @tblname, @indexname, @action

    END

    CLOSE curDB

    DEALLOCATE curDB

    END[/font]

  • The syntax error is within some dynamic SQL and hence only caught when the dynamic SQL is executed. You'll need to add a PRINT statement to print the dynamic SQL to the client so that you can see exactly what is trying to be executed and failing.

    Since you're not enclosing any of the index or table names in [], probably there's an index or table somewhere that has ( in its name.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail,

    But I printed all statements and do not see any '(' in any one

    Moreover, as all statements were executed successfully, how SP can return error-

    it suppose to fail on any if there is syntax error?

    In addition I checked all values used in dynamic string for '(' existence- nothing

    Or am I missing something?

    Thanks,

    Yuri

  • Yuri55 (5/26/2013)


    Here we are (sorry, if this is wrong way to post script):

    ******************************************************************

    USE [DBA]

    GO

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    -- ============================================================

    --rebuild index using dbo.index_rebuild_log ahead prepared data

    -- ============================================================

    CREATE PROCEDURE [dbo].[usp_IndexRebuild]

    AS

    BEGIN

    SET NOCOUNT ON;

    declare @start datetime

    declare @id int, @dbname varchar(50), @tblname varchar(100), @indexname varchar(100), @schname varchar(20), @action char(10)

    declare @sql nvarchar(1000) = ''

    DECLARE curDB CURSOR FORWARD_ONLY STATIC FOR

    SELECT id, dbname, schname, tblname, indexname, [action]

    FROM dbo.index_rebuild_log --ahead prepared fragmentation data

    WHERE index_done = 0

    ORDER BY id

    OPEN curDB

    FETCH NEXT FROM curDB INTO @id, @dbname, @schname, @tblname, @indexname, @action

    WHILE @@FETCH_STATUS = 0

    BEGIN

    set @start = GETDATE()

    set @sql = 'ALTER INDEX ' + @indexname + ' ON ' + @dbname + '.' + @schname + '.' + @tblname + ' ' + @action

    --print @sql

    exec sp_executesql @sql

    update dbo.index_rebuild_log --report action completion

    set

    index_done = 1,

    index_start = @start,

    index_end = GETDATE()

    where

    id = @id

    FETCH NEXT FROM curDB INTO @id, @dbname, @schname, @tblname, @indexname, @action

    END

    CLOSE curDB

    DEALLOCATE curDB

    END -- << Is there anything after this END?

    Is there anything following the last END statement when you script the stored procedure using Object Explorer?

  • Only GO command 🙂

    I also originally thought there is something extra in text and therefore hard printed whole SP- nothing

    Funny enough but when I tried to reproduce error I cannot do it.

    I do not mean to rerun whole SP- too expensive for 100 indexes.

    But after whole SP for 100 indexes was run and I got error I updated couple indexes (last in the ORDER by ID) completion flag (is_done = 0). Then I started SP again- it suppose to rebuild only these 2 indexes. Guess what? No error.

    But every week when all indexes were rebuilt- same story- SP completed successfully but same error returned every time

    Thanks,

    Yuri

  • Yuri55 (5/26/2013)


    Moreover, as all statements were executed successfully, how SP can return error-

    it suppose to fail on any if there is syntax error?

    Because the syntax error was in the dynamic SQL, hence SQL can't see it when it parses the procedure, only when that piece of dynamic SQL executes. The syntax error will cause that piece of dynamic SQL to fail (with the error), the rest of the procedure will carry on running.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Make sense, thanks

    But what confuses me is that ALL statements completed successfully (i.e. seems no single failed)

    I saw some posts regarding mysterious syntax errors that were results of different DBs compatibility level (CL)- certainly not sure it's my case. SP runs on DB with CL 100 (2008) but some of affected indexes live on DBs with CL 80 and 90 (i.e. 2000 and 2005).

    But frankly I have no idea how it can result in this error

    Thanks,

    Yuri

  • Change the proc to print not execute the dynamic SQL and run it with exactly the parameters that result in the error every 2 weeks

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Actually I did this already- when yesterday night SP returned error I rerun it again (same indexes) with Print only (no execute)- no errors, all printed statements looks good (no '(')

    This is the reason I called- Curious Case 🙂

    Anyhow, thanks for help

  • Can you post all the printed statements?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Seems I do not need to post all 82 statements as while preparing them and carefully reading one after one I found 2 indexes (same DB and table- probably same creator) with names like :

    "ind1 (Non-Unique, Non-Clustered)" and "ind2 (Non-Unique, Non-Clustered)".

    No comments :w00t:

    My bad- I did not find them till today.

    As I found they were ReBuilt/ReOrganized every week- therefore SP failed every week run

    (as these 2 indexes somewhere in the middle of total index list looks like sql failed on them keeping in mind error, proceeded with other indexes but at the very end returned this error)

    Anyhow I renamed these 2 indexes (removing "(Non-Unique, Non-Clustered)" from name) and hope next run everything should be fine

    Thanks everybody for help (as always)

Viewing 13 posts - 1 through 12 (of 12 total)

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