• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

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!

0
ginabro
Asked:
ginabro
  • 2
1 Solution
 
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now