Stored proc returning puzzling error - Invalid column name

Question

Post reply

Stored proc returning puzzling error - Invalid column name

Riskworks

Mr or Mrs. 500

Points: 592
More actions
July 26, 2011 at 3:44 pm

#244986

I have a SP that returns a table variable. It compiles and runs with no issues.
When I call it from another Proc I get an error saying that there is an invalid column at line 268.
There is no reference to the column anywhere around that line (268) in the called proc.
Msg 207, Level 16, State 1, Procedure usp_stored_proc_name Line 268
Invalid column name 'svn'
calling proc:
insert into #out
( svc,qtext,score)
exec usp_stored_proc_name V1,v2
Called proc:
SELECT svn
,column2
,column3
from @OUT
Return
John A. Byrnes

Viewing 11 posts - 1 through 10 (of 10 total)

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

Kath Smith Old Hand Points: 324 More actions · Answer 1

I believe your issue is this statement

exec usp_stored_proc_name V1,v2

You are sending in two variables into the proc you need to bracket them

exec usp_stored_proc_name (V1,v2)

Try and let me know if it works.

mtillman-921105 SSCertifiable Points: 7156 More actions · Answer 2

When you INSERT with an EXEC like that, the column names have to be exactly the same and in exactly the same order.

The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 3

Since your pseudocode isn't in a Create Procedure format, we can't tell anything from what you've posted. For one, we don't even know if you've declared your input variables correctly.

Could you please post the entire proc?

Just a note: I've gotten this error before when I've incorrectly aliased column names in a Select statement.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

Riskworks (7/26/2011)
...
When I call it from another Proc I get an error saying that there is an invalid column at line 268.
There is no reference to the column anywhere around that line (268) in the called proc.
Msg 207, Level 16, State 1, Procedure usp_stored_proc_name Line 268
Invalid column name 'svn'
...

Why we should believe you but not B. Gates? Have you made a fortune writing SQL stored procs, so we can trust your words? 😀

Please provide the code!

If you are right, we can write collective letter to MS with our deep concerns and disapproval of their bad behavior... 😀

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 5

Eugene Elutin (7/27/2011)
Why we should believe you but not B. Gates? Have you made a fortune writing SQL stored procs, so we can trust your words? 😀
....
If you are right, we can write collective letter to MS with our deep concerns and disapproval of their bad behavior... 😀

The OP is frustrated and having a bad day. That's no reason for a snarky response.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Lowell SSC Guru Points: 323518 More actions · Answer 6

Riskworks (7/26/2011)
I have a SP that returns a table variable. It compiles and runs with no issues.
When I call it from another Proc I get an error saying that there is an invalid column at line 268.
There is no reference to the column anywhere around that line (268) in the called proc.
Msg 207, Level 16, State 1, Procedure usp_stored_proc_name Line 268
Invalid column name 'svn'
calling proc:
insert into #out
( svc,qtext,score)
exec usp_stored_proc_name V1,v2
Called proc:
SELECT svn ,column2
,column3
from @OUT
Return

could it be asomething as simple as a misspelled column name? you insert into SVC, but select SVN?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 7

OK, OK, Sorry! Just was in playfull mood (not snarky, was need to google up this word to translate:-)) ...

Should be just simple:

Give us the code!

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 8

Lowell (7/27/2011)
Riskworks (7/26/2011)
I have a SP that returns a table variable. It compiles and runs with no issues.
When I call it from another Proc I get an error saying that there is an invalid column at line 268.
There is no reference to the column anywhere around that line (268) in the called proc.
Msg 207, Level 16, State 1, Procedure usp_stored_proc_name Line 268
Invalid column name 'svn'
calling proc:
insert into #out
( svc,qtext,score)
exec usp_stored_proc_name V1,v2
Called proc:
SELECT svn ,column2
,column3
from @OUT
Return
could it be asomething as simple as a misspelled column name? you insert into SVC, but select SVN?

And pulling from a table variable instead of a temp table (which is what was inserted into)? Or was that a typo?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Riskworks Mr or Mrs. 500 Points: 592 More actions · Answer 9

Good call everyone - The importance of making sure the column names and DT match.

I had a mismatched DT, in addition I renamed the temp table that the called SP modified, because I started to get a schema changed error, which blows the TT away in the process.

Thanks again everyone. Hope to repay the favior some day.

John

John A. Byrnes

Brandie Tarvin SSC Guru Points: 173080 More actions · Answer 10

Glad we could help. And very glad it wasn't something worse.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.