Run-Time Error '-214721871(80040e31)'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Run-Time Error '-214721871(80040e31)'

Author

Topic

AFK
Starting Member

26 Posts

Posted - 2005-01-20 : 15:12:47

When I try to run my code in Vb 6.0, and the code gets to the sql stored procedure call, I get tis error..saying
Run-Time Error '-214721871(80040e31)' timeout expired.
But when I run the store procedure in SQL query analyzer.it runs fine. I have Sql 2000 with sp3, and using vb 6.0 calling sp on the Ms SQL DB.
Please help...

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-01-20 : 15:19:22

How long does the query take in QA.
By default QA wont timeout

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-01-20 : 15:36:58

your application and Query Analyzer will each have their own timeout value associated with the connection. In Query analyzer, the Tools->Options->Connection (tab)->Query Timeout value will give you that value (0 ==> Never timeout). Your Connection object will have a timeout value that you can get and get. See what the value for this is.

HTH

=================================================================

Scriptures, n. The sacred books of our holy religion, as distinguished from the false and profane writings on which all other faiths are based.
-Ambrose Bierce, writer (1842-1914) [The Devil's Dictionary]

AFK
Starting Member

26 Posts

Posted - 2005-01-20 : 15:53:09

There are 9324 records that was suppose to be processed by the SP.It took 28 minutes...Is that too much..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-20 : 16:00:07

Holy sh!t batman! 28 minutes. As a user of your application, would you wait 28 minutes for a query?

What is your query doing? Cursors with dynamic sql? I wouldn't worry about that timeout setting right now. I'd worry about how inefficient your query is and concentrate on that.

Tara

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-01-20 : 16:12:37

Oh my god
Post your table structures including PK & IX and number of records and the code of your SP here so you can get your problem sorted

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-01-20 : 16:13:10

And our vocabulary word for the day is: Indexing.

(Although "Holy Sh!t" does run a close second..)

HTH

=================================================================

Scriptures, n. The sacred books of our holy religion, as distinguished from the false and profane writings on which all other faiths are based.
-Ambrose Bierce, writer (1842-1914) [The Devil's Dictionary]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-20 : 16:51:48

Well it's no wonder it takes 28 minutes. I counted 3 cursors. There's really no way for us to help you optimize this as you haven't explained what it is doing. But you need to get rid of the cursors if you want this to be fast.

Tara

AFK
Starting Member

26 Posts

Posted - 2005-01-20 : 17:00:20

Here is what it does..It updates the temp table.
Then cursor opens and it insert data into T1table..Then into t4 table, if the item is in t4 table it inserts into t2.
Then another cursor insert data into t1 table,. One t1 line can have 30 t3 lines.If it is in there then Updates tables..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-20 : 17:02:37

Well you've explained sort of what the code is doing. We can see that from the code. What you'd need to explain is the business requirement, how your tables are designed, why you need to do that, etc... Without more detailed information, we can't help. Only thing we can say right now is get rid of the cursors and use set-based methods instead.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-20 : 17:09:22

Good Lord....

And they're nested

I would break this up in to separate functions...and collapse all the sql and lose the cursors...

Just curious

What Country are you from?

Brett

8-)

AFK
Starting Member

26 Posts

Posted - 2005-01-20 : 17:22:13

whst is use set-based methods?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-20 : 17:23:33

Set-based methods mean you work on sets rather than one row at a time which is the case with cursors and WHILE loops. You typically can get rid of the looping by using joins in your DML statements.

Tara

AFK
Starting Member

26 Posts

Posted - 2005-01-20 : 17:35:27

If someone can show me how can I improve this Query that will be help..I am using that joins when I select columns..Please help me on this Query..

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-20 : 17:38:44

There isn't really a way for us to help you. You still haven't provided enough information for us to be able to help you write a set-based query. When I mentioned joins, I don't mean on selects. I mean on DML statements, which are your DELETE, UPDATE, INSERT statements.

Tara

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-01-20 : 20:13:27

For a start why are you updating the whole table when this is run, why dont you handle the conversions/format on the insert/update SQL or in the application before it is sent to the server.

quote:

Select @CustomerSiteCode = UPPER(RTRIM('18546'))

Update HWSNCOPO
set ShipToLoc = '18546'

Update HWSNCOPO
set WHID = 'A9',
PO# = Upper(RTrim(PO#)),
Release# = Upper(RTrim(Release#)),
Line# = Upper(RTrim(Line#)),
Part# = Upper(RTrim(Part#)),
PartDesc = Upper(RTrim(PartDesc)),
ListPrice = Upper(RTrim(ListPrice)),
OrderQty = Upper(RTrim(OrderQty)),
Supplier# = Upper(RTrim(Supplier#)),
SupplierPartCode = Upper(RTrim(SupplierPartCode)),
PromiseDate = Upper(RTrim(PromiseDate)),
ShipToLoc = Upper(RTrim(ShipToLoc)),
POStatus = Upper(RTrim(POStatus)),
BuyerName = Upper(RTrim(BuyerName)),
BuyerPhone = Upper(RTrim(BuyerPhone)),
BuyerFax = Upper(RTrim(BuyerFax)),
BuyerEmail = Upper(RTrim(BuyerEmail)),
CountryOfOrigin = Upper(RTrim(CountryOfOrigin)),
Customer# = Upper(RTrim(Customer#)),
CustomerSiteCode = Upper(RTrim(@CustomerSiteCode))

Update HWSNCOPO
Set vc_SHIPTOCUSTID = Null

And then you do the same process again to declare PoDetCursor

The statement above can be rewritten to

SELECT @CustomerSiteCode = UPPER(RTRIM('18546')) --just in case
--Is ShipToLoc always the same as CustomerSiteCode??
UPDATE HWSNCOPO
SET WHID = 'A9', ShipToLoc = @CustomerSiteCode, CustomerSiteCode = @CustomerSiteCode, vc_SHIPTOCUSTID = NULL, BeenUpdatedB4 = 'Y'
WHERE BeenUpdatedB4 = 'N'

And if you must use that cursor you wont have to do any conversions again

publiob99
Starting Member

1 Post

Posted - 2008-03-24 : 09:33:43

quote:
Originally posted by AFK

When I try to run my code in Vb 6.0, and the code gets to the sql stored procedure call, I get tis error..saying
Run-Time Error '-214721871(80040e31)' timeout expired.
But when I run the store procedure in SQL query analyzer.it runs fine. I have Sql 2000 with sp3, and using vb 6.0 calling sp on the Ms SQL DB.
Please help...

"Create your connection"

Global Db As New ADODB.Connection
put this property
Db.CommandTimeout = 0
is all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources