Solved

Timeout Error Trying to Add Identity Column to Sql Server via code

Posted on 2002-06-24
6
798 Views
Last Modified: 2012-05-04
I have a utility dll which modifies the schema of Sql Server and Ms-Access databases. In one case I need to add a temporary column to a table as an IDENTITY column (autonumber in Access) and then later delete it.

The following statement worked just fine on smaller databases, but when I ran it against a table with 80,000 records I got a timeout error.

ALTER TABLE tblTimeTable ADD TmpCol INT NOT NULL IDENTITY(1,1)

I copied the statement to QueryAnalyzer and it took 50 seconds, but it ran to successful completion. Why am I timing out via code?

Thanks

Here is how I create the objects prior to use:
  FqryColCreate := TADOQuery.Create(Nil);
  ADOConnection1 := TADOConnection.Create(Nil);
  ADOConnection1.LoginPrompt := false;
  ADOConnection1.Close;
  ADOConnection1.ConnectionString := sConnStr;
  ADOConnection1.Open;
  ADOConnection1.CommandTimeout := 50000;
  FqryColCreate.Connection := ADOConnection1;
  sSql := ALTER TABLE tblTimeTable ADD TmpCol INT NOT NULL IDENTITY(1,1)';
  FqryColCreate.SQL.Clear;
  FqryColCreate.SQL.Add(sSql);
  FqryColCreate.ExecSQL;


0
Comment
Question by:bjames
  • 3
  • 2
6 Comments
 
LVL 14

Expert Comment

by:DragonSlayer
ID: 7109536
Try setting a longer timeout period?
0
 
LVL 2

Accepted Solution

by:
isstorr earned 200 total points
ID: 7109976
Try using a TADODataset instead. TADOQuery seems to ignore the timeout value :(
0
 

Author Comment

by:bjames
ID: 7111286
TADODataset didn't work for most of my queries as it needs to return a recordset. But in checking the help on TADODataset I was pointed to TADOCommand, which was just what I needed to bypass the timeout error. TADOCommand has its own timeout property.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 2

Expert Comment

by:isstorr
ID: 7137170
Thanks but can you explain why I only got a B grade?
TADOCommand.ExecuteQuery should work with the timeout if you don't want a recordset.
0
 

Author Comment

by:bjames
ID: 7137558
Sorry for the delay. Been on vacation.

I gave the B grade because TADODataset did not work. TADODataset requires a recordset, which my code example showed I was not after.

If it is important to you, and you do not believe my reasoning is logical, let me know how and I will change the grade up.

Thanks
0
 
LVL 2

Expert Comment

by:isstorr
ID: 7138289
Also back from holiday! Don't worry, sounds reasonable.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question