weird intermittent 207 error

[jayrijnbergen]

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?

[jayrijnbergen]

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.

[jayrijnbergen]

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.

[former_member188958]

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)?

[jayrijnbergen]

thanks for the suggestion, will try to get some more debug info logged about the temp table structures