?
Solved

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

Posted on 2002-06-24
6
Medium Priority
?
813 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 600 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

650 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