Solved

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

Posted on 2002-06-24
6
785 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now