?
Solved

Frequent timeouts on SQL Server

Posted on 2009-04-15
34
Medium Priority
?
1,855 Views
Last Modified: 2012-05-06
I have a Microsoft Access database that uses linked tables from a copy of SQL Server 2000 (via ODBC).  When running insert or delete queries on one of the tables, I frequently get errors, primarily timeouts.

Here's one example:

Run-time error '-2147217871 (800040e31)':
[Microsoft][ODBC SQL Server Driver]Timeout expired

This is not terribly consistent, but it frequently happens when I delete two records in a row.  According to SQL Server Enterprise Manager, there's a lock on it from a select query when this happens.

I can't have the database timing out when adding/deleting records... Any idea what I need to do to prevent the delay/lock from happening?
0
Comment
Question by:rogermccoy
  • 22
  • 6
  • 3
  • +1
34 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24153332
are there primary keys/indexes on the tables? triggers?
0
 

Author Comment

by:rogermccoy
ID: 24153367
Yes.  Here's the CREATE TABLE for that table.
CREATE TABLE [dbo].[Equipment Orders] (
	[OrderID] [int] IDENTITY (1, 1) NOT NULL ,
	[CustomerID] [int] NULL ,
	[AgentID] [int] NULL ,
	[EmployeeID] [int] NULL ,
	[OrderDate] [smalldatetime] NOT NULL ,
	[PurchaseOrderNumber] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ShipDate] [smalldatetime] NULL ,
	[ShippingMethodID] [int] NULL ,
	[Tracking #] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FreightCharge] [money] NULL ,
	[SalesTaxRate] [real] NULL ,
	[PaymentReceived] [bit] NOT NULL ,
	[Payment Method ID] [int] NULL ,
	[Placement Type ID] [int] NULL ,
	[Payment Date] [smalldatetime] NULL ,
	[Payment Method] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Merchant #] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Tid#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Platform] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Merchant Name] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Comment] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Attention] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Address] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Address 2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[City] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[State] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Zip] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[View] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Received Swap?] [bit] NOT NULL ,
	[Swap Charged?] [bit] NOT NULL ,
	[Phone Number] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Order Total] [money] NULL ,
	[Rush] [bit] NOT NULL ,
	[Modified Time] [smalldatetime] NULL ,
	[Modified Computer] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Modified User] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Program 1] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Program 2] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Program 3] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Close Notes] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Equipment Returned] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Close Request Date] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Closed] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Closed Date] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Ship Notes] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[ClosureReason] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[DoNotCharge] [bit] NOT NULL ,
	[Security Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FreeShipping] [bit] NOT NULL ,
	[Notes Notes] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Ship To] [int] NULL ,
	[Approved] [bit] NOT NULL ,
	[Weight] [real] NULL ,
	[ReadyToShip] [int] NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
 
 CREATE  INDEX [IX_Equipment Orders] ON [dbo].[Equipment Orders]([CustomerID]) ON [PRIMARY]
GO
 
 CREATE  INDEX [IX_Equipment Orders_1] ON [dbo].[Equipment Orders]([AgentID]) ON [PRIMARY]
GO
 
 CREATE  INDEX [IX_Equipment Orders_2] ON [dbo].[Equipment Orders]([EmployeeID]) ON [PRIMARY]
GO
 
 CREATE  INDEX [IX_Equipment Orders_3] ON [dbo].[Equipment Orders]([OrderDate]) ON [PRIMARY]
GO
 
 CREATE  INDEX [IX_Equipment Orders_4] ON [dbo].[Equipment Orders]([ShipDate]) ON [PRIMARY]
GO
 
 CREATE  INDEX [IX_Equipment Orders_5] ON [dbo].[Equipment Orders]([ShippingMethodID]) ON [PRIMARY]
GO
 
 CREATE  INDEX [IX_Equipment Orders_6] ON [dbo].[Equipment Orders]([FreightCharge]) ON [PRIMARY]
GO
 
 CREATE  INDEX [IX_Equipment Orders_7] ON [dbo].[Equipment Orders]([Payment Method ID]) ON [PRIMARY]
GO
 
 CREATE  INDEX [IX_Equipment Orders_8] ON [dbo].[Equipment Orders]([Placement Type ID]) ON [PRIMARY]
GO
 
 CREATE  INDEX [IX_Equipment Orders_9] ON [dbo].[Equipment Orders]([Payment Method]) ON [PRIMARY]
GO
 
 CREATE  INDEX [IX_Equipment Orders_10] ON [dbo].[Equipment Orders]([Merchant #]) ON [PRIMARY]
GO
 
 CREATE  INDEX [IX_Equipment Orders_11] ON [dbo].[Equipment Orders]([Merchant Name]) ON [PRIMARY]
GO
 
 CREATE  INDEX [IX_Equipment Orders_12] ON [dbo].[Equipment Orders]([Order Total]) ON [PRIMARY]
GO
 
CREATE TRIGGER tr_EquipmentOrders_INSERT
ON [Equipment Orders]
AFTER INSERT
AS
DECLARE @OrderID int
SELECT TOP 1 @OrderID = [OrderID] FROM inserted
exec spCalculateEquipmentOrderTotal @OrderID
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
 
CREATE TRIGGER tr_EquipmentOrders_UPDATE
ON [Equipment Orders]
AFTER UPDATE
AS
DECLARE @OrderID int
SELECT TOP 1 @OrderID = [OrderID] FROM inserted
exec spCalculateEquipmentOrderTotal @OrderID
GO

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24153379
as you get timeouts on inserts, I would inspect the trigger for insert:


CREATE TRIGGER tr_EquipmentOrders_INSERT
ON [Equipment Orders]
AFTER INSERT
AS
DECLARE @OrderID int
SELECT TOP 1 @OrderID = [OrderID] FROM inserted
exec spCalculateEquipmentOrderTotal @OrderID

aka the procedure it calls.
please check how much time the procedure call takes for a single orderid ...
that one might be the culprit !
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!

 

Author Comment

by:rogermccoy
ID: 24153385
It looks like the problem only happens when the form is open.  If I close the form and insert/delete from the query as it appears in Access, there's no delay at all.  Re-open the form and delete (either using or not using the form) and the delay happens about half of the time.  This seems to support that the SELECT query from the form might be blocking it, but I'm not sure how I would get the query to release so I can do other updates.
0
 

Author Comment

by:rogermccoy
ID: 24153389
I tried deleting the trigger entirely just in case... No dice.  Still has the same problem.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24153398
does the form itself  enable "update" or "add" or "delete" of the records?
should it support those features directly?
0
 

Author Comment

by:rogermccoy
ID: 24153427
The intent was to have these functions carried out when command buttons are pressed, but I've tried several different methods of adding/deleting records with the same problem, including:

* Via record selectors.
* Via code calling acCmdDeleteRecord
* Via code running an SQL statement
* Via code calling a stored procedure
* Updating " directly" outside the form by opening the query or original table in datasheet view.

Each method has the same issue.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24153462
side question: you don't have any clustered index on the table?
please change one of the indexes to a clustered index, best a field where you do "often" a >= <= BETWEEN style of condition in your queries...
0
 

Author Comment

by:rogermccoy
ID: 24153466
It's probably worth noting that the form has multiple nested sub-forms, most of which are operating in different ways on the same table.
0
 

Author Comment

by:rogermccoy
ID: 24153481
It's in there, but I neglected to include the primary key or constraint code above.  See below:
ALTER TABLE [dbo].[Equipment Orders] ADD 
	CONSTRAINT [DF__Equipment__Order__0E0FCABA] DEFAULT (getdate()) FOR [OrderDate],
	CONSTRAINT [DF__Equipment__Payme__0F03EEF3] DEFAULT (0) FOR [PaymentReceived],
	CONSTRAINT [DF__Equipment__Recei__0FF8132C] DEFAULT (0) FOR [Received Swap?],
	CONSTRAINT [DF__Equipment__Swap __10EC3765] DEFAULT (0) FOR [Swap Charged?],
	CONSTRAINT [DF__Equipment __Rush__11E05B9E] DEFAULT (0) FOR [Rush],
	CONSTRAINT [DF__Equipment__DoNot__12D47FD7] DEFAULT (0) FOR [DoNotCharge],
	CONSTRAINT [DF__Equipment__FreeS__13C8A410] DEFAULT (0) FOR [FreeShipping],
	CONSTRAINT [DF__Equipment__Appro__14BCC849] DEFAULT (0) FOR [Approved],
	CONSTRAINT [PK__Equipment Orders__0D1BA681] PRIMARY KEY  CLUSTERED 
	(
		[OrderID]
	)  ON [PRIMARY] 
GO

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24153503
I see. however, it shall be worth making the primary key non-clustered, and another index clustered, as the primary key is rarely a candidate for range queries...

apart from that, having sub-forms for the same table might explain the problem for the delete...
I presume you will need to get the subforms to move off the record that has to be deleted...
0
 

Author Comment

by:rogermccoy
ID: 24153525
None of them seem to have that specific record selected (in the cases I've tested), although it may be in the query results somewhere.

It happens inconsistently, so I keep wondering if the tables are being locked by the forms refreshing their data... Is there a way to reduce the amount of time they keep the tables locked while doing this?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24153532
locks are held until the cursor is released.
so, unless the form/recordset that has the record open closes/moves off the record/..., the record will remain locked. there is no timeout in those matters, except the timeout to wait to get the lock. once you have the lock, you can keep it "forever".
0
 

Author Comment

by:rogermccoy
ID: 24153547
Hm... But I wonder why the inconsistency then.  Usually the first record I delete works properly, but the second one doesn't.  Often if I try the second one again, it works.  (This pattern happens frequently, but is not absolutely consistent.)
0
 

Author Comment

by:rogermccoy
ID: 24153634
Another thought... This will be a multi-user database, so even if I just had one form visible, I'm going to need to avoid the system locking it when multiple users are trying to edit the table.  There has to be a way around this.
0
 

Author Comment

by:rogermccoy
ID: 24153646
Here's another error message.  Same basic principle:

Run-time error '3156':

ODBC--delete on a linked table 'tblOrders' failed
0
 

Author Comment

by:rogermccoy
ID: 24153706
Here's the timeout message I usually get on INSERTs:

Run-time error '3155':

ODBC--insert on a linked table 'tblOrders' failed.

[Microsoft][ODBC SQL Server Driver]Fractional truncation (#0)
[Microsoft][ODBC SQL Server Driver]Timeout expired (#0)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24153755
Can you post the code you are using to do the Insert?
0
 
LVL 59
ID: 24156580
The default ODBC operation time in Access is 60 seconds.  In some cases, you may need to make this larger depending on the operations being performed and the performance of your SQL Server.   Note that a time out setting of 0 means no timeout; Access will wait forever until it gets done.  Setting the timeout depends on how your doing the insert.
JimD.
0
 

Author Comment

by:rogermccoy
ID: 24160309
I've used different variations with the same problem each time.  Bear in mind the same problem happens if I open the query in datasheet view without any code or forms involved (other than having a form open in the background).
Private Sub cmdNewOrder_Click()
 
    RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , , acNewRec
 
End Sub

Open in new window

0
 

Author Comment

by:rogermccoy
ID: 24160319
> The default ODBC operation time in Access is 60 seconds.

Is there a default as to how long it holds a lock open on a SELECT statement?  If so, finding a way to decrease that might solve the blocking, but I'm not aware of such a setting if there is one.
0
 

Author Comment

by:rogermccoy
ID: 24161426
Don't know if this helps at all, but here's some of the sp_Lock output from before opening the database, after opening the database, and while the table is locked:
sp_Lock output before opening DB. The DB I'm using is #7
 
56	7	0	0	DB	                	S	GRANT
56	1	85575343	0	TAB	                	IS	GRANT
58	7	0	0	DB	                	S	GRANT
 
 
spLock after opening DB:
 
51	7	0	0	DB	                	S	GRANT
51	7	1588968787	0	TAB	                	Sch-S	GRANT
51	7	1572968730	0	TAB	                	Sch-S	GRANT
51	7	2100970611	1	PAG	1:42612         	IS	GRANT
51	7	1604968844	0	TAB	                	Sch-S	GRANT
51	7	91915449	0	TAB	                	IS	GRANT
51	7	1967398128	0	TAB	                	IS	GRANT
51	2	0	0	PAG	1:100           	X	GRANT
51	7	2100970611	0	TAB	                	S	GRANT
52	7	0	0	DB	                	S	GRANT
54	7	0	0	DB	                	S	GRANT
56	1	85575343	0	TAB	                	IS	GRANT
56	7	0	0	DB	                	S	GRANT
58	7	0	0	DB	                	S	GRANT
 
 
While locked:
 
51	7	0	0	DB	                	S	GRANT
51	2	0	0	PAG	1:100           	X	GRANT
51	7	2100970611	0	TAB	                	S	GRANT
51	7	1588968787	0	TAB	                	Sch-S	GRANT
51	7	1572968730	0	TAB	                	Sch-S	GRANT
51	7	2100970611	1	PAG	1:42612         	IS	GRANT
51	7	1604968844	0	TAB	                	Sch-S	GRANT
51	7	91915449	0	TAB	                	IS	GRANT
51	7	1967398128	0	TAB	                	IS	GRANT
52	7	2100970611	0	TAB	                	IX	WAIT
52	7	0	0	DB	                	S	GRANT
56	7	0	0	DB	                	S	GRANT
56	1	85575343	0	TAB	                	IS	GRANT
58	7	0	0	DB	                	S	GRANT
 
 
DBCC INPUTBUFFER(51)
 
RPC Event	0	sp_executesql;1
 
 
DBCC INPUTBUFFER(52)
 
Language Event	0	DELETE FROM "dbo"."Equipment Orders" WHERE ("OrderID" = 6771 ) 

Open in new window

0
 
LVL 59
ID: 24161486
Your carrying out the insert then through a form with no code?  Base the form on a query and set the ODBC timeout property of the query to something like 300 (which is 5 minutes).
JimD.
0
 

Author Comment

by:rogermccoy
ID: 24161683
I believe I was originally using DoCmd.RunSQL with an INSERT statement, but I've changed it around several times in an attempt to find a solution.

Tried this and haven't caught any straightforward timeout errors since, but I am getting a long delay on some DELETEs followed by good ol':

Run-time error '3156':

ODBC--delete on a linked table 'tblOrders' failed
CurrentDb.Execute "DELETE FROM tblOrders WHERE [OrderID] = " & NonRefOrder, dbFailOnError + dbSeeChanges

Open in new window

0
 

Author Comment

by:rogermccoy
ID: 24161821
> Base the form on a query and set the ODBC timeout property of the query to something like 300 (which is 5 minutes).

After some more testing, looks like this just gives me the same problem with a much, much longer wait before the error appears.

This database is being converted over from pure Access to using an SQL Server/ODBC backend.  There was never any delay on the Access edition... There has to be a way to get rid of the delay here.
0
 

Author Comment

by:rogermccoy
ID: 24162009
It sounds like using READ_COMMITTED_SNAPSHOT might fix the problem, but I'm using SQL Server 2000.  Any idea how to do a rough equivalent on 2000?  Or am I even on the right track here?
0
 

Author Comment

by:rogermccoy
ID: 24162018
(Just to clarify, READ_COMMITTED_SNAPSHOT was added in SQL Server 2005.)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24163755
Ah, I see you are still using the old "MS Access" way to view/modify your data on SQL Server.  In that case I cannot help you, other than to wish you luck.
0
 

Author Comment

by:rogermccoy
ID: 24163792
> Ah, I see you are still using the old "MS Access" way to view/modify your data on SQL Server.

This is (ideally) the first step in a transition to moving to a web-based system, but in the meantime we're stuck with Access as the pre-existing front-end.

When you say the "MS Access way", are you talking about using Access in general or the way I'm programming it specifically?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24163871
IMHO if you are going to use SQL Server then you should be prepared to re-analyze, re-write and re-test your entire app.  There is nothinkg intrinsically wrong with using MS Access, but I would not use DAO or ODBC for that matter to connect to MS SQL Server.  Instead I would only allow unbound forms and use ADO to return Forward Only cursors and to execute what you call in MS Access "action" queries.  If you are not prepared to do that, then hire a consultant or keep using MS Access as a database.  The alternative is that you will be frustrated and disappointed with the results.  I have said this a thousand times, so here it goes again:  MS SQL Server is NOT an upgrade to MS Access.  Treat it that way and you do so at your own peril.

Not everyone is as radical as I am and here is a link that proves it:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22517413.html
0
 
LVL 59

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 total points
ID: 24166873
<<> Base the form on a query and set the ODBC timeout property of the query to something like 300 (which is 5 minutes).

After some more testing, looks like this just gives me the same problem with a much, much longer wait before the error appears.

This database is being converted over from pure Access to using an SQL Server/ODBC backend.  There was never any delay on the Access edition... There has to be a way to get rid of the delay here.>>
  You need to be careful with queries out of Access using ODBC.  If your using anything that is JET specific in the query, JET will handle the transaction on the client side rather then sending it to SQL Server for execution.  For example, if you do a join between a local JET table and a SQL Server table and do a delete, JET may actually issue a separate delete for each and every record to SQL.
  Other things like using VBA functions in your query will cause JET to handle the processing as well.  One easy way to avoid this problem (or at least make you aware of it) is to use an ODBC Direct workspace.  Within that workspace, JET won't be involved and anything that is JET specific will cause an error, so it's quite obvious where problems lie.
  But the other thing you can do without going through re-coding is to turn on ODBC logging, run your insert, and then see what exactly is getting sent to SQL Server for the insert.
  And I don't agree with acperkins on re-coding with Access going the unbound route; the whole point of Access is that it gives you the features so that you don't have to do all that work.  But to work with it well, you need to understand how the product works.  Many people don't take the time to do that and get poor performance as a result.
  My suggestion is that if your going to go the unbound route, then you should use something else.  Access's main drawbacks are that:
1. You cannot do an n-tier design

2. You cannot build a standalone .EXE and as a result, are subject to environment issues (an Access database is really more like a document that is read rather then a program that is executed).

3. Broken references in VBA projects.

  There are some other minor ones, but in terms of developing applications, those are the major issues it has.  But with all that said, there are many, many, Access based apps out there that use SQL Server as a BE with ODBC connections to the tables and they work fine with DAO.  I do think ADO is a better choice in that regard, but DAO will work.
  If you plan to stick with Access and SQL server for a bit, I would wander down to the local book store and pickup either the Access Developers Handbook Vol 2, which covers "enterpise" issues (working with something other then a JET BE), or the Access to SQL Server By Martin Reid and Susan Sales Harkins.  Both of those cover a wide range of issues when using Access with SQL Server, use of ADO, etc.  Should be available on Amazon as well.
FWIW,
JimD.
0
 

Author Comment

by:rogermccoy
ID: 24170829
>> You need to be careful with queries out of Access using ODBC.  If your using anything that is JET specific in the query, JET will handle the transaction on the client side rather then sending it to SQL Server for execution.  For example, if you do a join between a local JET table and a SQL Server table and do a delete, JET may actually issue a separate delete for each and every record to SQL.

You might say I'm splitting the difference on this one.  Several of these queries are search results for different forms, and the relevant entries are identified by the computer name.  I have a view on the server that has just the search results on it and not any other entries, but Access runs a query on top of that view to filter it down to the local computer's results (using a VBA function that checks the environmental variables).  At any given time I have about eleven of these queries open simultaneously on the main form for different subforms.  It might sound a little crazy to have that many open at the same time, but it makes for a good user interface, and it worked perfectly fine when the database was pure Access.

>>  But the other thing you can do without going through re-coding is to turn on ODBC logging, run your insert, and then see what exactly is getting sent to SQL Server for the insert.

That's a good idea.  I'll play with that for a little bit.
0
 

Author Comment

by:rogermccoy
ID: 24171589
I tried running an SQL server trace while re-creating the updated error.  I noticed that the following RPC:StmtStarting
and SP:StmtStarting don't seem to have corresponding RPC:Completed and SP:StmtCompleted entries.  According to Enterprise Manager, 54 was hanging and preventing 53 from executing... So 54 runs:

SELECT "dbo"."vEquipment Search Results"."OrderID","dbo"."vEquipment Search Results"."Computer" FROM "dbo"."vEquipment Search Results" WHERE ("Computer" =  @P1 )

... leaves it open, and then 53 starts ...

UPDATE "dbo"."vEquipment Not Shipped" SET "AgentID"=@P1,"OrderDate"=@P2  WHERE "OrderID" = @P3 AND "timestamp" = @P4

(53 and 54 are both the Access application.)

Both of these tables are views drawing primarily from the [Equipment Orders] table (among others).  But why is it holding that SELECT statement open?  If I could just get it to cut that out, that should fix everything.
RPC:Starting	exec sp_executesql N'SELECT "dbo"."vEquipment Search Results"."OrderID","dbo"."vEquipment Search Results"."Computer" FROM "dbo"."vEquipment Search Results" WHERE ("Computer" =  @P1 ) ', N'@P1 varchar(510)', 'ROGERPC'	54	36:32.6
SP:StmtStarting	SELECT "dbo"."vEquipment Search Results"."OrderID","dbo"."vEquipment Search Results"."Computer" FROM "dbo"."vEquipment Search Results" WHERE ("Computer" =  @P1 ) 	54	36:32.6
SP:StmtStarting	UPDATE "dbo"."vEquipment Not Shipped" SET "AgentID"=@P1,"OrderDate"=@P2  WHERE "OrderID" = @P3 AND "timestamp" = @P4	53	36:40.3
RPC:Starting	exec sp_executesql N'UPDATE "dbo"."vEquipment Not Shipped" SET "AgentID"=@P1,"OrderDate"=@P2  WHERE "OrderID" = @P3 AND "timestamp" = @P4', N'@P1 int,@P2 smalldatetime,@P3 int,@P4 binary(8)', 0, 'Apr 17 2009 12:35PM', 6856, 0x00000000000B894D	53	38:17.2
SP:StmtStarting	UPDATE "dbo"."vEquipment Not Shipped" SET "AgentID"=@P1,"OrderDate"=@P2  WHERE "OrderID" = @P3 AND "timestamp" = @P4	53	38:17.2

Open in new window

0
 

Author Comment

by:rogermccoy
ID: 24172139
Think I've got it... One of the queries is a bit slow to execute, and it was set to update whenever a record was deleted or on a few other triggers.  Points to JDettman for reminding me of the obvious... Check the logs.  Thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

621 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