on 10-21-2015 3:28 PM
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?
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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)?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
94 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.