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?
 
St3veMaxConnect With a Mentor Commented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.