SQL Server Time Out Expires - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQL Server Time Out Expires

Author

Topic

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-08-24 : 16:19:38

hi guys
I am running a sp and while iam running it from my application it gives me an error after 1 min

Microsoft OLE DB Provider for SQL Server error '80040e31'
Timeout expired

Does anyone know how i will fix it, my database connection time out is 6000 sec
Thnx

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-24 : 16:21:09

You need to optimize your query. 6000 seconds is huge for a database connection timeout setting. What user wants to wait more than 30 seconds to get their data? Post your query, table structures, and indexes if you would like us to help optimize it.

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-08-24 : 16:34:53

here is my procedure, what iam doing here, based on some condition iam deleting duplicate records. MY table have abt 10000 rows but when i execute it from query analyzer i work fine and take around 4 mins

CREATE PROC stp_CUST_DeleteDuplicates
AS
DECLARE @L_Counter as int
--To Delete Entries where city , zip and address 1 is null

Delete From CCESListing WHERE ISNULL(city,'') = '' or isnull(zip,'') = '' or ISNULL(address1,'') = ''

--To Delete Duplicate Entries From CCESListing

SET rowcount 1
SELECT 1
--SET @L_Counter = 1
WHILE @@RowCount > 0
BEGIN
delete C1
FROM CCESListing C1
WHERE EXISTS
(SELECT 1 From CCESListing C2
Where C1.State = C2.State AND left(C1.Address1,3) = left(C2.Address1,3) and left(C1.Zip,5) = left(C2.Zip,5)
AND left(C1.city,2) = left(C2.city,2) and left(C1.FirstName,2) = left(C2.FirstName,2)
and left(C1.lastName,3) = left(C2.LastName,3)
GROUP BY C2.State ,left(C2.Address1,3) ,left(C2.Zip,5),
left(C2.city,2) ,left(C2.FirstName,2)
,left(C2.LastName,3)
Having COUNT(*) > 1)
-- SET @L_Counter = cast(@@rowCount as int)
END
SET rowcount 0

GO

and this is my table
CREATE TABLE [dbo].[CCESListing] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CompanyName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[State] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Zip] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Country] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Fax] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
waiting for reply

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-24 : 16:44:42

Why would you want to delete duplicates from an application? Duplicates should be prevented by a unique constraint on the table.

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-08-24 : 16:54:40

Thanks

I have a excel Sheet So First I Dumped my data from excel into table, then I have to look if there any duplicate entires, if so delete the them, then i also have some other processes which i cant do or chk until this step is done. I cant take unique field caz my data of a certain field may be unique but i want to delete data on certain conditions like
Mike Endrew 1234 eastwood Dr newyork 234564
Mike Endreu 1234 eastwood Drive Newyork 234564

see the above data is duplicate entry its just a typo mistake.
Do u get my point

Thanks again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-24 : 16:58:07

You should import the data into a staging table. The staging table would have the same layout as your table. Then move only the non-duplicate entries into your table.

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-08-24 : 17:04:49

Thnx
Can u explain me what u exactly suggest me let say i import data in to a table and then how would i get rid of dups when i insert into other table, have u seen my condition.

Thnx again

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-24 : 17:14:12

You shouldn't get rid of duplicates. You shouldn't put the rows in the table in the first place. So import the data into another table, we call that staging table. Then you have another step that moves the non-duplicate rows to your real table. So no deletes are required.

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2004-08-24 : 18:35:30

thnx
That work good.

Thanks Alot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources