Re: Timeout Expired for no apparent reason

• Previous message: Fred Zolar: "How to use SQL stored proc as datasource for MS Word mail merge?"
• In reply to: Andrew J. Kelly: "Re: Timeout Expired for no apparent reason"
• Next in thread: Andrew J. Kelly: "Re: Timeout Expired for no apparent reason"
• Reply: Andrew J. Kelly: "Re: Timeout Expired for no apparent reason"
• Messages sorted by: [ date ] [ thread ]

Date: Fri, 6 Aug 2004 14:05:02 -0700

I'll try to describe in more details what I'm doing. I have 2 tables: Table
A and Table B. Table A contains around 17 million records. Table B contains
updates for table A and contains around 100 thousand records. The DLL simply
opens a recordset on table B (Select * From B Where Status = 0). For each
record, I read some values and use them to update table A. On success of this
update I update the status flag to 1 with another update statement (not using
the recorset update method).

Sometimes the method updated 1000 records, sometimes up to 35000 records.
But it never managed to end the recordset.

I hope this helps.

Thanks,
Sami Samir

"Andrew J. Kelly" wrote:

> Just because you don't explicitly issue a BEGIN TRAN does not mean you don't
> issue locks. By default every sql statement (Data access or modification)
> will take some type of lock which can potentially block others. That is the
> whole point of locks. Executing updates from a DLL does not exclude them
> from taking locks or blocking others. As a matter of fact it most likely
> increases the chance of blocking simply due to the fact it will most likely
> take longer than the same actions done in a stored procedure. If you give
> some details on exactly what your trying to do in the DLL maybe we can
> suggest a better alternative.
>
> --
> Andrew J. Kelly SQL MVP
>
>
> "Sami Samir" <SamiSamir@discussions.microsoft.com> wrote in message
> news:DC3EE824-11F2-4463-A25F-7E2982EE5874@microsoft.com...
> >
> > I checked for locks during the execution. I did not define any
> transactions
> > in my code. Maybe it's defined explicitly?
> > The reason why I'm doing it in a DLL is that when I do it in a stored
> > procedure, it causes other commands to be blocked. This was an attempt to
> > avoid this by doing updates one at a time.
> >
> > Thanks,
> > Sami Samir
> >
> > "Andrew J. Kelly" wrote:
> >
> > > Did you check for locks during the execution of the update or after?
> Are
> > > all of these updates wrapped in a single transaction? It sounds likely
> that
> > > you were blocked for a short enough period of time to cause the timeout.
> By
> > > default I believe it is only 30 seconds. What is the reason for doing
> this
> > > in a DLL though? Why not just use a stored procedure?
> > >
> > > --
> > > Andrew J. Kelly SQL MVP
> > >
> > >
> > > "Sami Samir" <SamiSamir@discussions.microsoft.com> wrote in message
> > > news:4BE61091-F2FD-48AC-AF20-3E646A58E50F@microsoft.com...
> > > > I have a VB 6 Active X DLL that contains a method that opens a
> recordset
> > > and
> > > > for each row updates a row in another table and then updates the
> original
> > > > table. The database is a SQL Server 2000 cluster (SP3). My problem is
> that
> > > > when I run this method from an ActiveX script from within a DTS
> package, I
> > > > notice that everything is fine and hundreds of rows are updated per
> minute
> > > > and then after a certain random ammount of time and rows it gives me
> this
> > > > error:
> > > >
> > > > [Microsoft][ODBC SQL Server Driver]Timeout expired
> > > >
> > > > Each update takes less than a second and should not be the reason for
> this
> > > > error. Also, I checked if there were any locks on the table, but there
> > > > weren't. I use an ODBC connection to connect to the database using
> MDAC
> > > 2.6.
> > > > Has anybody experienced a similar case who can help me?
> > > >
> > > > Thanks and best regards,
> > > > Sami Samir
> > >
> > >
> > >
>
>
>

• Previous message: Fred Zolar: "How to use SQL stored proc as datasource for MS Word mail merge?"
• In reply to: Andrew J. Kelly: "Re: Timeout Expired for no apparent reason"
• Next in thread: Andrew J. Kelly: "Re: Timeout Expired for no apparent reason"
• Reply: Andrew J. Kelly: "Re: Timeout Expired for no apparent reason"
• Messages sorted by: [ date ] [ thread ]