Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2002-06-24
6
Medium Priority
?
818 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 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
This is an update to some code that someone else posted on Experts Exchange. It is an alternate approach, I think a little easier to use, & makes sure that things like the Task Bar will update.
The video will let you know the exact process to import OST/PST files to the cloud based Office 365 mailboxes. Using Kernel Import PST to Office 365 tool, one can quickly import numerous OST/PST files to Office 365. Besides this, the tool also comes…
To export Lotus Notes to Outlook PST or Exchange and Domino Server files to Exchange Server or PST files with ease, go for Kernel for Lotus Notes to Outlook conversion tool. Through the video, you can watch the conversion process. A common user with…

608 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