Procedure <procName> expects paramter Error
After moving a database to a new SQL 2000 installation from a SQL 7 machine I ran into a stored procedure that consistently told me it was not getting one of its parameters. I checked the value being set with some response.writes and it was the correct value, I checked the parameters collection in the command object and the parameter was there, and had a value, I changed the connection to the old SQL server and it ran with no problems. Ok, fine the procedure got messed up in the transfer somehow, I deleted it and re-entered it, it still failed. I ran it from query analyzer, it ran just fine. I beat my head on desk, it hurt. But the page kept telling me Procedure 'procName' expects parameter 'paramName', which was not supplied.
I did what any other person would do, I decided to share the misery. So my co-worker came over and I showed him everything I'd already tried, he had me try a few more things - no luck. We both beat our heads on the desk and they both hurt, but the procedure wouldn't run....
I checked the reference manual (www.google.com) and found all the usual things about checking the values, and spelling, and default values, and lots of other things that I knew couldn't be it since it was working on the old SQL machine. I was skimming one such page and ran across the usual...
- make sure you spell the name of the stored procedure correctly, well of course I did that
- is the parameter name spelled right, duh, I wasn't born yesterday
- make sure there is an @ sign in front of the parameter name, yea, yea I checked that and there wasn't one but the code is 7 years old and been working fine so that isn't it
- make sure you specify that the command type is a stored procedure well, that is so obvious it shouldn't even need to be said.... oh crap!!!
Sure enough, apparently in the good old days when you didn't need @ signs, you also didn't need to specify the command type. Being a born and raised in the good old days SQL 7 had no problems with this, however the 2000 version looks on this sort of like how my daughter looks at groups like Genesis and Pink Floyd. So I added the command text, removed all the response.writes I'd thrown in, crossed my fingers, clicked the button, and poof, there is the page with no errors. Too bad my head still hurts from all the banging.
If you have a question, comment, bug fix, or addition let us know. We'll add it
to the demo with the proper credit. Just drop us an
email at comments@directedinsight.com