Post reply

[MYSTERY] Execution-plan bug? “Schema changed after the target table was created. Rerun the Select Into query.” : Every 6th run!

  • November 2, 2012 at 11:38 pm

    #270446

    Hiyah!

    Before I get to the problem I want to say that we've worked around it by splitting the SELECT INTO up...

    The reason I'm posting here is because I want to learn what is happening internally in SQL Server for the query to fail like it does.

    I'll grab the details on Monday, but the key points are:

    1: There's a SELECT INTO making a hash-table, that inserts some totals via a PIVOT.

    2: It uses a couple of views that are produced by other views.

    3: The table is cleared out before every run. There's no index columns on it.

    4: After googling the error some people mentioned triggers - there are none in this database.

    The server is QUIET - no one logged in to any web-apps, the maintenance plan ran over 7 hours before, one admin user logged in locally...

    Running the SP manually through the Management window, or running via a web-service...

    Every 6th run, the SP FAILS...

    Have we discovered an SQL Server execution plan bug?

    Any theories on what's happening?

    I can't test until Monday, but I wonder if some View optimisation is causing it?

    Damn mystery!

  • November 5, 2012 at 6:24 pm

    #1556265

    Exactly what error it is throwing ?

    Without a test case or at least error message I can give you only a rough guess:

    "Table is cleared..."

    Data change from execution to execution, statistics change, and when statistics change enough (every 6th execution?) execution plan will probably change.

    With different execution plan your query could run into data that he can't "chew".

    E.g. you have a varchar field in your data, and you are trying to convert it into a number or date.

    If execution plan scans whole table and run into data that it cannot convert - it will fail.

    If execution plan uses the index and skips the unconvertable rows - there is no error.

    You can find that out for sure by setting-up a server-side trace that captures execution plans.

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths

  • November 5, 2012 at 10:39 pm

    #1556297

    Hiyah!

    The error in full is:

    "Error 539 : Schema changed after the target table was created. Rerun the select into query."

    I'll grab the full query in a couple of hours.

    Thanks for helping. =)

  • November 5, 2012 at 11:09 pm

    #1556303

    Take a look at this thread http://www.sqlservercentral.com/Forums/FindPost1313092.aspx. Paul White gives a very reasonable explanation (and solution) about this error.

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

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