Post reply

SQL giving Stored Procedure Not found when executed through a Web Service

  • May 17, 2016 at 5:23 am

    #310764

    I have a Web Service which through a DLL calls a function and this function calls multiple Stored Procedures. The function works without any problem but at least 3 to 4 times a day, I get an error message Stored Procedure Not Found. Sometimes it gives a specific SP name and sometime some other SP name. These procedures do exists and when the same function is called again with the same login credentials it goes through.

    Web Service is called by multiple users and it is hosted with Application Pool. All the procedures have try and catch and from the catch we have been able to figure out this error.

    What could cause suddenly SQL giving such error. This is not a normal phenomenon. This happens suddenly at any time of the day.

    Can someone help me.

  • May 17, 2016 at 5:48 am

    #1879447

    I've seen this before due to connection pooling where the database context becomes the default database, instead of the one in the connection string;

    ie the connection to the database goes to the database [production], but due to connection pooling, it defaults to the users [master] database setting instead.

    fixes I've seen have been change the login used by the calling applications to have a default database to be the production database instead of master, and also to make explicit calls to three part names, ie [Production].dbo.[procedurename] to address it,.

    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!

  • May 24, 2016 at 5:20 am

    #1880996

    Thank you. Now SP not found error has stopped. However, sometimes, I do get #table not available whereas the same is available or when the same function is called again it goes through. Parameters do not match. Any help possible.

Viewing 3 posts - 1 through 2 (of 2 total)

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