Very Strange Database Problem Regarding Stored Proc

Giganews Newsgroups
Subject: Very Strange Database Problem Regarding Stored Proc
Posted by:  Darrell Elliott (darrell.j.ellio…
Date: Fri, 28 Jun 2013

I have two database servers and two IIS web servers, one database and one w=
eb server are in a test environment and the other two are in production. I =
have a stored procedure that looks up a person by potentially all sorts of =
criteria and the stored procedure works as it should in test and in product=
ion, well, most of the time in production. Same stored procedure and the sa=
me code.

What happens on the production server is this: you can use all of the other=
criteria too look up the results and they work immediately, except one par=
ameter of the criteria. Sometimes when you search by that criteria, the err=
or that I receive is a SqlClient error from IIS that the script timed out. =
I have tested the stored procedure directly on the production server, with =
the criteria in question, and I get the results within two seconds. But if =
I run the process in my code, it times out.

What I have done, that doesn't make any sense to me is this: When this isn'=
t working, I copy the stored procedure from the test server over to the pro=
duction server and THEN, everything works as it should.

As far as I can tell, both database servers are set up the same with all of=
the same settings. If anyone can lend a thought, that would be very apprec=
iated. I can also post a copy of the stored procedure here if that would be=
of any help.

Thanks in advance,