Error in Connection

When I try to execute a stored procedure , error "Timeout expired " comes

[deepraj]

Hi All,

Need a help from you people.

Problem:

When I try to execute a stored procedure I am getting this error.

Error:
------

"System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. "

Can we clear this error by setting the ConnectionTimeout property of Connection object.

Can it arise due to the error in stored procedure.

In the stored procedure I created it like this :

CREATE PROCEDURE [DBO.SP_INSERTDETAILS] .......

Our server is hosted in US and the database is also set there.
When we run this stored procedure there , will this "DBO.SPNAME" create any problem?

Waiting for a reply from you guys.

Please help me.

DeepRaj

[Ocean10000]

This basic connection time out error. All it means is that the sql command in this case a stored procedure didn't return results in the timeout window it was given. Doesn't say anything is truely wrong with the sql command you gave, it just didn't return fast enought is all. You might look into seeing how much data the command is changing or trying to pull down. And see if you can reduce the cost in resources that it is taking, which will usually lead to a faster response time, and stop the time out errors.

[aspdaddy]

What are you developing with? If you use interdev you can test the stored procs manually to make sures theres no bugs before running them in .NET

[deepraj]

I am not using interdev. I am using SQL Server.

Thanks

DeepRaj D

[aspdaddy]

By the name of the proc id guess it just return a single number? so it must be a bug in the code or connection. You tried running it manually in enterprise manager?

If youre using SQL2K its not much fun debugging procs, I wouldnt ever use SQL server 2K for developing procs or views anymore, its one of the worst/slowest dev tools I'v ever come across.

[deepraj]

Hi

We are trying to update 4 tables using the stored procedure.We have tried this in our local server. It is working fine. But it is showing error when we run it from the server where we have hosted our application. What will be the issue. We have changed the ConnectionTineout also. Then also it is not working. Can you suggest a solution.

At present, we can't use any other database other than SQL2K. If we want to change SQL2K what is the best option , according to you?

Regards,

DeepRaj D

[Ocean10000]

The question I ask my self in this situation is how many records are being affected by these updates, and how big are the tables being affected.

Could paste the sql code here so we can get a better idea what this stored procedure is doing. So we can may be able to offer suggestions on how to tweak your sql code to perform better.

[edited by: Ocean10000 at 2:59 pm (utc) on Sep. 7, 2006]

[Easy_Coder]

>>SP_INSERTDETAILS

When you prefix your sprocs with sp_ your causing sql server to perform an extra lookup. Plus you could be introducing a bug.

Sql server is gonna look in the master database first to see if that sproc is there and if it is then that one is getting executed and your custom sproc won't.

[deepraj]

Hi all

Sorry for the delayed reply.

Ocean10000

This is the SP we are using.

DECLARE @CONTACTID NUMERIC
BEGIN
SELECT @CONTACTID = CONTACTID FROM T_CONTMASTER WHERE EMAIL = @CONTACTEMAILID
IF (@CONTACTID IS NOT NULL )
BEGIN
BEGIN TRAN

-- To update a master table, 18 fields are updated

UPDATE T_CONTMASTER SET
FIRSTNAME=@FIRSTNAME,
LASTNAME =@LASTNAME ,
REFERENCEFIRSTNAME =@REFERENCEFIRSTNAME ,
REFERENCELASTNAME =@REFERENCELASTNAME ,
REFERENCEEMAIL =@REFERENCEEMAIL ,
HOMEPHONE =@HOMEPHONE ,
WORKPHONE=@WORKPHONE,
CELLPHONE =@CELLPHONE ,
REFERENCECELLPHONE =@REFERENCECELLPHONE ,
ADDRESS1 =@ADDRESS1 ,
ADDRESS2 =@ADDRESS2 ,
STATE =@STATE ,
CITY = @CITY ,
ZIPCODE=@ZIPCODE,
COMPANY=@COMPANY,
NEIGHBORHOOD=@NEIGHBORHOOD,
NEWSLETTERSTATUS=@NEWSLETTERSTATUS,
CAMPAIGNLETTERSTATUS=@CAMPAIGNLETTERSTATUS

WHERE
CONTACTID=@CONTACTID

-- To update a mapping table, One field is updated

UPDATE T_LEADCONTMAPPIN SET

LEADTYPEID=@LEADTYPEID

WHERE

CONTACTID=@CONTACTID

-- To update a mapping table, One field is updated

UPDATE T_SRCCONTMAPPIN SET

SOURCEID=@SOURCEID
WHERE

CONTACTID=@CONTACTID

-- To update a mapping table, One field is updated

UPDATE T_MSGRCONTMAPPIN SET

MESSENGERID=@MESSENGERID,
CHATUSERNAME=@CHATUSERNAME
WHERE
CONTACTID=@CONTACTID

When we use this Stored Procedure, the first updation to T_CONTMASTER works. What else may be the reason?

Easy_Coder

We have not created the SP in master table.The information that, when we name an SP like "SP_" may create problem is new to me. Thanks.

aspdaddy

At present we can't use anyother DB,other than SQL2K. Can you please make it clear why SQL2K is not good.

Waiting for a response from you all, guys...........

Thanks and Regards,

DeepRaj D