Skip to Content

weird intermittent 207 error

I've got a weird 207 error (invalid column name) from a complicated procedure

The procedure is creating a temporary table from a template table, like this:  select * into #temptable from template_table

Then doing an insert in the temp table and calling other procs to do further processing

This procedure is running every minute and most of the time everything is ok.

But sometimes the proc throws the 207 error. After a while it's running fine again...

We've already recompiled the proc and checked the tables and all columns involved.

I don't have a repro, it just happens intermittently in production.

It's on 15.0.3, so don't have much hopes for an investigation of a shared memory dump for this error

I've got a case open with support, but without a repro it will be quite difficult to get anywhere.

What I noticed is that the temporary table is very wide, has 70+ columns

Server version is 15.0.3 ESD #4.1, configured with opt goal allrows_mix, options ase_current and traceflag 299

(Yes, we will upgrade at some point to 15.7, but that's not possible right now)

Anybody any suggestions?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Oct 21, 2015 at 03:12 PM

    Could you add some diagnostics to the procedure?
    Perhaps just before the statement that raises the 207 error, check if the tempdb..syscolumns table has a column of that name belonging to your temp table.  If not (the condition that would raise the error), output (or store in a table) the list of all the columns that do exist for that table and see if that list suggests anything to you - i.e. is it a completely different table definition? the same but this one column name is different somehow (truncated, garbage value, just different)?

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 21, 2015 at 03:30 PM

    Does the 207 display the name of the proc in question? Or is your application reporting a 207 sans the proc name and you're assuming which proc is generating the error?

    Have you checked all of the other procs that touch this table to see if one of them has the reference to the invalid column?

    Does the 'invalid column' actually exist in template_table, or is the error referencing a column that does not exist in template_table?

    I've worked at a few clients (usually large banks) that were notorious for having a huge stored proc inventory with #temp tables being created/referenced in tons of subordinate procs.  It wasn't uncommon to find various 'parent' procs creating the same #table but with different column-names/column-orders/datatypes and then calling a common subordinate proc that sufferered performance issues from constant recompilation due to different #table structures (even with TF299 enabled). Also pretty interesting when a #table was created multiple times during the processing of a chain of procs (eg, parent, child and grandchild procs creating a #table of the same name but different structures).

    Add comment
    10|10000 characters needed characters exceeded

    • same complexity here. multiple procs with many temp tables.

      but we've been several times through all the procs called, and can't find any typos, mixed up columns etc.

      the column does exist in the template table, so should be in the temp table as well.

      Error is reporting the proc name with current line.

  • Oct 23, 2015 at 09:26 AM

    FYI for someone who reads this post in the future

    With auditing and logging of the 207 error we got a lot of useful info

    enable logging: sp_altermessage 207, 'with_log', true

    audit options enabled (assuming you have already configured and activated auditing)

    - table access

    - cmdtext

    - errors

    - login

    - logout

    The errors reported in the ASE logfile show the spid, date and error. With that we can find the same info in the audit log and see what's happening just before and after.

    Add comment
    10|10000 characters needed characters exceeded