September 24, 2009 at 10:16 am
I hope somebody could jump into this and advice me .
I have a (SQL server 2000 )table with 800,000 records without any primary key which has been built previously before my time.
I have a MS project VBA application which deletes and inserts the data to the SQL Server Table through an ODBC connection. It was working good so far but recently it started giving the Timeout expired error message on the DELETE query. I am guessing that delete removes each row and records them in the transactional log causing the delay.
error is not consistent is there a way to fix this?
Table doesn't have a primary key and not indexed as well...When I tried to add an auto increment PK to the table it failed to put that because of the table size.
Can you please advice? how do I add a PK at this time or will it help at this time?
Thanks,
Rey
September 24, 2009 at 10:27 am
did you try this in query analyser ?
alter table yourtable
add SurrogateId int identity(1,1) not null primary key
This should add the wanted column as well as the PK constraint (clustered by default, if there isn't yet a clustering index)
Do it in query analyser and doublecheck your timeout has been set to 0 (=unlimitted)
IF you need more info, best is you post the ddl (create scirpt) for that table ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 24, 2009 at 11:15 am
This is great thanks for the reply. Yes I am able to add the PK and I did set the timeout to 0. Will check how it will work with the MS Project now.Thanks again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply