Calling a Sybase SP, error "SP not found"

Question

I am connecting to a Sybase 9 database to retrieve data. I can query the database without issue using ODBC connections but I am having an issue calling stored procedures. The procedure was written probably 6-7 years ago. This is what I have to execute the stored procedure.

OdbcCommand itemWeightAve = conn.CreateCommand();
itemWeightAve.CommandText = "ComputeLastCost";
itemWeightAve.CommandType = CommandType.StoredProcedure;
itemWeightAve.Parameters.AddWithValue("@OwnerId", "BananaHammock");//company number
itemWeightAve.Parameters.AddWithValue("@InventoryId", InventoryNumberHere);//inventory id from query results
itemWeightAve.Parameters.AddWithValue("@EndDate", EndDateHere);//end date from query results
OdbcDataReader itemAveReader = itemWeightAve.ExecuteReader();

I am not very familiar with Sybase or ODBC and the version these guys are using is extremely old and is no longer officially supported. Upgrading the Sybase database is out of the question. The error I get when attempting to execute this command is...

ERROR [42S02] [Sybase][ODBC Driver][Adaptive Server Anywhere]

Procedure 'ComputeLastCost' not found

I know that the procedure exists, it is typed correctly, and that the parameter names exist and are typed correctly. Does anyone have any tips/hints/suggestions for what I'm doing wrong here?

I do not know. How can I check this? I am connecting to the database via ODBC DSN and I can see the stored procedure on the list of available queries but I don't know if that actually means my app has permission to execute it. — Chase, Aug 21 '12 at 15:42
Who is the DBA in your group. ask them if that stored proc exist.. also "BananaHammock" is that really and ID ?? — MethodMan, Aug 21 '12 at 15:42
@DJKRAZE I know for certain that the SP exists. BananaHammock is just a joke placeholder I used when I typed this out here. hehehe. If there was someone to ask I wouldn't be here. This is a random old legacy system. — Chase, Aug 21 '12 at 15:44
show more code.. I have a feeling it's something to do with your Connection String.. looking at the code above looks fine.. but I think you need to show more and are you sure that you are getting a valid connection .. step thru the code and see if it connects.. also try wrapping your code around a using(){} and use a try{} catch{} catch{} — MethodMan, Aug 21 '12 at 15:44
What is the default database of the login that you are using? Is the stored procedure in the same database? If not, you need to prefix your procedure name with the database name "sharedDB.ComputeLastCost". You can check this by logging in with the same user/password through isql and try and exec it by hand. if you have to do a use database (ie. use database sharedDB) before you execute it, you need to put the db name in front. Or you can change your default database for the user. Either way should work. — Mike, Aug 21 '12 at 15:45
@Mike That was the solution. Thanks for helping me with that. I had to prepend the proc with the correct information. Thank you for the help all. — Chase, Aug 21 '12 at 15:55
@Mike - you should turn your comment into an answer (and then Chase can accept it) so that this doesn't look like an unanswered question. — Graeme Perrow, Aug 21 '12 at 17:05

score 2 · Accepted Answer · answered Aug 21 '12 at 19:12

Turned the comment into an answer...

What is the default database of the login that you are using?

Is the stored procedure in the same database?

If not, you need to prefix your procedure name with the database name "sharedDB.ComputeLastCost".

You can check this by logging in with the same user/password through isql and try and exec it by hand. if you have to do a use database (ie. use database sharedDB) before you execute it, you need to put the db name in front.

You can also change your default database for the user. Either way should work.

Calling a Sybase SP, error "SP not found"

1 Answers1