Application does no longer see the SQL stored procedures

Hello,
I support the C# 2008 web application, SQL 2005. The web service uses the service account to talk to the database. Suddenly, literally overnight,  we started getting  the error : "Exception Message: System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: The stored procedure 'XXXX' doesn't exist."  launching the app. It can't find any of the stored procs, even though they exist in the database and the service account role's permissions haven't changed (from what we were told). It doesn't have the db_owner  authority but whatever access was given has always worked before. The problem occurs in all  environments including my local. When I debug it fails at the function below, line : parameterCache.SetParameters(objdbCommand, objDatabase);

public static void DiscoverSpInputParameters(SqlDatabase objDatabase, DbCommand objdbCommand)

{

parameterCache.SetParameters(objdbCommand, objDatabase);

foreach (DbParameter param in objdbCommand.Parameters)

{

if (param.Direction == ParameterDirection.ReturnValue)

{

objdbCommand.Parameters.Remove(param);

break;

}

}

}

It's a puzzle, since nothing was changed on the developer's side, the function has worked for years, first in VS 2005. My feeling is that something got changed on the  SQL server/database side  but our DBA's say that nothing was changed. Any input/suggestions/ideas would be greately appreciated!

Thanks!

ginabroAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

St3veMaxCommented:
This definately sounds like a permissions related issue.

What authentication method are you using to connect to the database? Can you verrify it from another machine? Are your stored procedures using the dbo schema or are they MySchema.MyProcedure?

I would talk with your DBA's a bit more and ask them about the permissions...

HTH
0
chapmandewCommented:
Sounds to me like your connection string is connecting to the wrong database.
0
ginabroAuthor Commented:

We use trusted connection to the database. The app was developed without specifying the schema. I took over the app recently and plan on adding the dbo.XXX schema to call the procs. However, not having any schema never caused any issues before. I tried adding the schema to one of the procs while troubleshooting, unfortunatel with no success.

I confirmed that the connection string in the web.config points to the right db.

Thanks!
0
St3veMaxCommented:
Ask your DBA's to setup a Profile trace on the server and monitor what happens when you application attempts to connect. That will give you a better idea of which direction to point in.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.