SQL Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

The Man - 23 Feb 2007 17:58 GMT

Hello All:
I am in desparate need of assistance. We are using an application
called PaperVision Enterprise which takes scanned invoices and puts
them into a database for the purpose of easy reference to any scanned
invoice in the db. 2 weeks ago while trying to perform a 'match and
merge' of the newly scanned docs, we received a Run-time error
'-2147217871 (80040e31)": Timeout expired message. After being told by
the company who supports this software that they couldn't help me
since it is using a MS SQL Server Table Lookup I started googling the
error with minimal success. I am not very proficient with SQL so I'm
kind of stuck. I have narrowed this down to an ODBC run-time error,
but looking for some kind of guidance as to how I can resolve it. Any
info would be greatly appreciated. We are using MS SQL Server 2000.

Reply to this Message

Greg D. Moore (Strider) - 23 Feb 2007 18:46 GMT

> Hello All:
> I am in desparate need of assistance. We are using an application
[quoted text clipped - 9 lines]
> but looking for some kind of guidance as to how I can resolve it. Any
> info would be greatly appreciated. We are using MS SQL Server 2000.

Really not much to go on here.

It's not clear to me, did this happen just once? Or all the time?

Can you open query analyzer and do a sp_who2 on the server in question?
It's possible something has a lock on that table. Killing that (and
determining who/why) should solve your problem.

Signature

Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Reply to this Message

The Man - 23 Feb 2007 19:50 GMT

On Feb 23, 1:46 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@greenms.com> wrote:

> > Hello All:
> > I am in desparate need of assistance. We are using an application
[quoted text clipped - 22 lines]
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com

ok i did what you have ask me to do and these are the results. Thank
you for your assistance on this issue.
also i am in the sql server enterprise manger and i killed all the
process. i tried to run the query again and i get the same error. i
opened up the sql server enterprise manager and checked the locks/
objects tree and that database is in there. Its seems like whenever i
try and run the query the database is getting locked with locked type
(DB).
there are the results of the command you told me to run:
53 sleeping sa
PVE . pvedb AWAITING COMMAND 1052 239
02/23 14:18:08 PaperVision DM Automation
Server 53

54 sleeping sa
PVE . pvedb AWAITING COMMAND 0 0
02/23 14:21:18 Microsoft(R) Windows (R) 2000 Operating
System 54

55 sleeping sa
PVE . pvedb AWAITING COMMAND 0 0
02/23 14:21:11 Microsoft(R) Windows (R) 2000 Operating
System 55

56 sleeping sa
PVE . pvedb AWAITING COMMAND 0 0
02/23 14:21:15 Microsoft(R) Windows (R) 2000 Operating
System 56

57 sleeping sa
PVE . pvedb AWAITING COMMAND 0 0
02/23 14:21:15 Microsoft(R) Windows (R) 2000 Operating
System 57

58 sleeping sa
PVE . pvedb AWAITING COMMAND 0 0
02/23 14:21:15 Microsoft(R) Windows (R) 2000 Operating
System 58

59 sleeping sa
PVE . pvedb AWAITING COMMAND 0 0
02/23 14:21:12 Microsoft(R) Windows (R) 2000 Operating
System 59

60 sleeping sa
PVE . pvedb AWAITING COMMAND 16 0
02/23 14:22:38 Microsoft(R) Windows (R) 2000 Operating
System 60

61 sleeping sa
PVE . pvedb AWAITING COMMAND 0 0
02/23 14:21:14 Microsoft(R) Windows (R) 2000 Operating
System 61

62 sleeping sa
PVE . pvedb AWAITING COMMAND 0 8
02/23 14:22:38 Microsoft(R) Windows (R) 2000 Operating
System 62

63 sleeping sa
PVE . pvedb AWAITING COMMAND 0 0
02/23 14:21:14 Microsoft(R) Windows (R) 2000 Operating
System 63

64 sleeping sa
PVE . pvedb AWAITING COMMAND 0 0
02/23 14:21:14 Microsoft(R) Windows (R) 2000 Operating
System 64

65 RUNNABLE sa
PVE . pvedb UNKNOWN TOKEN 123015 2253
02/23 14:23:25 PaperVision DM Automation
Server 65

66 sleeping sa
PVE . pvedb AWAITING COMMAND 0 0
02/23 14:21:15 Microsoft(R) Windows (R) 2000 Operating
System 66

67 RUNNABLE sa
PVE . pvedb SELECT INTO 31
9 02/23 14:23:01 SQL Query
Analyzer 67

Reply to this Message

Erland Sommarskog - 23 Feb 2007 23:20 GMT

> I am in desparate need of assistance. We are using an application
> called PaperVision Enterprise which takes scanned invoices and puts
[quoted text clipped - 8 lines]
> but looking for some kind of guidance as to how I can resolve it. Any
> info would be greatly appreciated. We are using MS SQL Server 2000.

How much do you pay for that support contract? If you are paying anything
at all, I suggest that you withhold any payments, until they have helped
you with this issue, or sent you a fix.

The error you get is one that happens in the client API. By default
many client API has a command timeout of 30 seconds, which means that
if no data has come back in this time, the client gives up waiting.

There are two possible reasons why this timeout elapses:
1) Blocking.
2) The query takes a long time to run.

Judging from the output you posted, there is no blocking. (The DB
lock you mentioned is perfectly normal. All processes take out a lock
on their current database.)

On the other hand, process 65 appears to be busy. It has a lot of CPU
time accumulated, and I don't like UNKNOWN TOKEN. That's usually a
sign of something performing less well.

So call the vendor again, and be more insistent that they help you
to sort this out. At very least, they should change their app, so
that it sets the command timeout 0, which means wait forever. Then
at least, you would get a result, even if it would take some time.

Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Reply to this Message

The Man - 27 Feb 2007 14:37 GMT

> > I am in desparate need of assistance. We are using an application
> > called PaperVision Enterprise which takes scanned invoices and puts
[quoted text clipped - 39 lines]
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Thank you very much for your help. I will do that and explain what you
told me to our vendor. Hopefully they can help with this information.
The one thing that they have helped me to do is set the time out to
zero and it seems i still get the same error.

Reply to this Message

Erland Sommarskog - 27 Feb 2007 16:06 GMT

> Thank you very much for your help. I will do that and explain what you
> told me to our vendor. Hopefully they can help with this information.
> The one thing that they have helped me to do is set the time out to
> zero and it seems i still get the same error.

Hm, exactly how did you set that timeout? Usually the command timeout is
set in code only, or not all, which means that only way to change it is
to ship a new binary. But there are other timeouts, that the confused(?)
support staff at PaperVision may have gotten you to fiddle with.

Signature

Reply to this Message

The Man - 28 Feb 2007 17:05 GMT

How else can i change the time out? I went to the query analyzer to
change the time out status...Also i don't know if this could help me
but i was thinking of purging the data in my MS SQL server 2000. Do
you know how i can go about purging this data?

> > Thank you very much for your help. I will do that and explain what you
> > told me to our vendor. Hopefully they can help with this information.
[quoted text clipped - 11 lines]
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books

Reply to this Message

Erland Sommarskog - 28 Feb 2007 22:16 GMT

> How else can i change the time out?

If the time out is the one I think it is, I would not expect that you can.

> I went to the query analyzer to change the time out status...

And you can definitely not change it from Query Analyzer, as this is not
a timeout in SQL Server. It's a timeout in the client, or more precisely
in the client API.

(There are actually a couple of possible timeouts, whereof some are in
SQL Server. However, the one that by far is the most common that people
run into, is the default 30-second timeout in the client API. On the SQL
Server you can run into lock timeouts, but these are not enabled by
default. You can also have timeouts when accessing linked servers. But
I don't get the impression that PaperVision uses linked servers.)

> Also i don't know if this could help me but i was thinking of purging
> the data in my MS SQL server 2000. Do you know how i can go about
> purging this data?

Reducing the amount of data could indeed help, but do that would require
knowledge of the data model, and I almost guaranteed that doing this on
your own is entirely unsupported by PaperVision. If you want to do such a
purge, you need to talk to PaperVision's support.

Signature

Reply to this Message