Solved

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

Posted on 2002-06-24
6
800 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 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
Technology Partners: 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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
drawing animated level bar based on numbers 3 120
how can i search if string exist in array ? 3 77
Base1 Encode/Decode 3 99
Firemonkey webbrowser scrollbars ? 1 68
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

737 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