August 2, 2007 at 3:06 pm
If some error occurs in a stored procedure, or the values passed to parameters are wrong, does SQL Server return the error in some parameter? The reason why I am asking is that I've got a SP which inserts data into a table. There are 19 parameters to this SP. I am implementing an ASP.NET application which uses the SP to store data, and I know, at this point, that some of the values being passed are wrong. I'm getting an error, when the attempt is made to insert the data, that more parameters are being passed than the SP allows for. I've checked everything carefully and I just can not see where an additional parameter is coming from. Then I thought, perhaps SQL Server passes some unknown parameter when it encounters an error.
Am I correct about that?
Kindest Regards, Rod Connect with me on LinkedIn.
August 3, 2007 at 1:34 am
Sorry - not totally sure what you are asking.
Why not invoke the stored proc from SSMS first -
EXEC sp_someproc @param1=1 ....
this should help define exactly where the error is - running it with the incorrect data should give useful errors. If you get no error then it must (!!) be an application issue. Try posting the error here...
August 3, 2007 at 1:47 am
If I understand correctly then unfortunately your guess is not right, SQL server doesn't add any parameters on its own.
August 3, 2007 at 4:40 am
Best bet for this is to run profiler against the DB and trigger the error in the front end, if you can (not sure where your web server/DB are). Profiler will show you exactly what the front end is passing into the sproc.
In your code, watch for re-using command objects without clearing the parameters collection first, or a simle typo in one of the parameter adds.
Either way, profiler will let you see exactly what's getting passed to the proc.
August 3, 2007 at 6:03 am
I'd go with what Ian said. You need to try chucking some test data at the SP to see exactly what's going wrong. If you can, try it with a range of different sets of parameters so see what effect this has on any error(s) that occur. These will appear in the output pane.
Best of luck with it.
Bart
August 3, 2007 at 7:44 am
I would agree with Greg on this one. You need to see what is being passed to the SQL Server from the web page. SQL Server does not add any parameters to a stored procedure call.
Basically the error you are seeing is before the SP ever runs. SQL Server is telling you that you are sending an extra parameter to the SP. This is why using Profiler to see EXACTLY what is being passed by the application is the best way to solve the problem.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 3, 2007 at 9:28 am
Seems to me that you are being ambitious, sending 19 parameters. How do you verify that the 19 values are all valid, prior to sending them to the SP? Are best practices being used or is this a seat of the pants exercise on the part of the .ASP side of the house? Is the .ASP expecting the SP to do what the SP expects the .ASP side to do? Is responsibility clearly defined for what happens where?
Of course, this could end up being something as simple as an extra comma being inserted into the string used to invoke the SP - that would easily be overlooked in desk checking.
August 3, 2007 at 9:35 am
I finally discovered the problem. It had to do with the items being returned by the stored procedure (SP) that performs the SELECT, and the items that are sent to the table in the INSERT SP. The SP that performs the SELECT returns 20 items, the last of which is known as DateOfService. the SP that inserts the data does not update the DateOfService. Apparently, the FormView wizard uses the SELECT statement to determine all fields that are to be used in both the UPDATE and INSERT templates. (I would have thought it would use the SP that I specified for performing insertions, rather than SELECT, but I guess I am wrong about that.)
Kindest Regards, Rod Connect with me on LinkedIn.
August 4, 2007 at 7:55 am
Just 19 parameters, one of developer at our place has used 127 parameters in one SP to update data and once spent 4 hours just to identify with which parameter he is sending wrong value. LOL
August 5, 2007 at 1:57 pm
I always perform read ahead checks on the values passed into a procedure to confirm that they won't cause any integrity violations. This can be a little time-consuming, especially if you are passing 127 parameters, but it does allow me to return a specific error that identifies the value that had an error.
As a best practice, all of our procedures return a return value of 0 for success or -1 for an error. In addition they all populate an ErrorID output parameter if an error has been identified. The error can then be retrieved from our error table.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply