Prev: TransactionAbortedException Occurs Randomly
Next: T-SQL - How to use xp_regread to get ALL time zone information
From: Goran Djuranovic on 25 Sep 2008 15:37
Ok guys,
It looks like the following could be the problem:
0x80040E31L ----- DB_E_ABORTLIMITREACHED ----- Execution aborted because a
resource limit has been reached; no results have been returned.

What resources is it talking about? Server, TempDB, ...?

Thanks
Goran


"Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
news:Xns9B1D2F0C16FEYazorman(a)127.0.0.1...
> Goran Djuranovic (goran.djuranovic(a)newsgroups.nospam) writes:
>> I am working with SS 2000 and get the following error when querying
>> linked
>> server:
>><?MSSQLError HResult="0x80040e31" Source="Microsoft OLE DB Provider for
>>SQL
>> Server" Description="Timeout expired"?>
>> The strange thing is that 1 out fo 5 times runs OK. Remote Connection
>> Timeout on linked server is set to 60 seconds and Remote Query Timeout
>> is set to 600 seconds. When fails, the query runs for 45 seconds. When
>> successful, the query runs for 30 second. Linked server is queried by a
>> SQL job which runs a DTS package. SQL job and DTS are not on linked
>> server.
>>
>> Any suggestions?
>
> Hm, what about increasing the query timeout for the linked server? It's
> a little funny that it dies after 45 seconds, when you asked for 60, but
> it's not exact science.
>
> A more radical idea would be to look into improve the performance for the
> query, so that it runs faster.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
> SQL 2000:
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>


From: Erland Sommarskog on 25 Sep 2008 18:33
Goran Djuranovic (goran.djuranovic(a)newsgroups.nospam) writes:
> Ok guys,
> It looks like the following could be the problem:
> 0x80040E31L ----- DB_E_ABORTLIMITREACHED ----- Execution aborted because a
> resource limit has been reached; no results have been returned.
>
> What resources is it talking about? Server, TempDB, ...?

I'm afraid that error code does not give that much more information. In
the MDAC Books Online, I find this text for DB_E_ABORTLIMITREACHED for
several functions:

Execution has been aborted because a resource limit has been reached. For
example, a query timed out. No results have been returned.

The resource limit is something in the OLE DB provider, but it's surely
the query timeout.

Did you look into trying to improve the performance on the other end?



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Goran Djuranovic on 26 Sep 2008 08:57
Well guys, I think I found what it is. I had to explicitly set CommandTimeout in my ActiveX scipt in DTS package like this: CommandObj.CommandTimeout = 600. I don't know if this is a bug or something, but it didn't care that I had it set on the server (Remote Query Timeout = 600), nor in the ConnectionStirng (CommandTimeout = 600). One of those things you would never think of. :)

Thanks for your help again.

Goran Djuranovic


"Goran Djuranovic" <goran.djuranovic(a)newsgroups.nospam> wrote in message news:OxlcgY0HJHA.1160(a)TK2MSFTNGP04.phx.gbl...
> Ok guys,
> It looks like the following could be the problem:
> 0x80040E31L ----- DB_E_ABORTLIMITREACHED ----- Execution aborted because a
> resource limit has been reached; no results have been returned.
>
> What resources is it talking about? Server, TempDB, ...?
>
> Thanks
> Goran
>
>
> "Erland Sommarskog" <esquel(a)sommarskog.se> wrote in message
> news:Xns9B1D2F0C16FEYazorman(a)127.0.0.1...
>> Goran Djuranovic (goran.djuranovic(a)newsgroups.nospam) writes:
>>> I am working with SS 2000 and get the following error when querying
>>> linked
>>> server:
>>><?MSSQLError HResult="0x80040e31" Source="Microsoft OLE DB Provider for
>>>SQL
>>> Server" Description="Timeout expired"?>
>>> The strange thing is that 1 out fo 5 times runs OK. Remote Connection
>>> Timeout on linked server is set to 60 seconds and Remote Query Timeout
>>> is set to 600 seconds. When fails, the query runs for 45 seconds. When
>>> successful, the query runs for 30 second. Linked server is queried by a
>>> SQL job which runs a DTS package. SQL job and DTS are not on linked
>>> server.
>>>
>>> Any suggestions?
>>
>> Hm, what about increasing the query timeout for the linked server? It's
>> a little funny that it dies after 45 seconds, when you asked for 60, but
>> it's not exact science.
>>
>> A more radical idea would be to look into improve the performance for the
>> query, so that it runs faster.
>>
>> --
>> Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se
>>
>> Links for SQL Server Books Online:
>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>> SQL 2000:
>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>>
>
>
From: Mark Han[MSFT] on 30 Sep 2008 03:01
Hi Goran,

Thank you for the sharing the resolution.

To address your concern, I would like to explain the following
" remote query timeout option to specify how long, in seconds, a remote
operation can take before Microsoft SQL Server times out. The default is
600, which allows a 10-minute
wait.

" CommandObj.CommandTimeout is : Gets or sets the wait time before
terminating the attempt to execute a command and generating an error. There
is an article to share with you:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.com
mandtimeout(VS.71).aspx

" in the connection string, connection time out is : The length of time (in
seconds) to wait for a connection to the server before terminating the
attempt and generating an error. There is a link to share with you:
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.
connectionstring.aspx

If anything I can assist you, please post it here.

Best regards,
Mark Han
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg(a)microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

From: Goran Djuranovic on 30 Sep 2008 09:26
Just a little correction, the command object I used was
Server.CreateObject("ADODB.command"), but the CommandTimeout property has
the same description as the one of SqlCommand.

Thanks
Goran

"Mark Han[MSFT]" <v-fathan(a)online.microsoft.com> wrote in message
news:$quG0psIJHA.5824(a)TK2MSFTNGHUB02.phx.gbl...
> Hi Goran,
>
> Thank you for the sharing the resolution.
>
> To address your concern, I would like to explain the following
> " remote query timeout option to specify how long, in seconds, a remote
> operation can take before Microsoft SQL Server times out. The default is
> 600, which allows a 10-minute
> wait.
>
> " CommandObj.CommandTimeout is : Gets or sets the wait time before
> terminating the attempt to execute a command and generating an error.
> There
> is an article to share with you:
> http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.com
> mandtimeout(VS.71).aspx
>
> " in the connection string, connection time out is : The length of time
> (in
> seconds) to wait for a connection to the server before terminating the
> attempt and generating an error. There is a link to share with you:
> http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.
> connectionstring.aspx
>
> If anything I can assist you, please post it here.
>
> Best regards,
> Mark Han
> Microsoft Online Community Support
> =========================================================
> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg(a)microsoft.com.
> =========================================================
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> =========================================================
>


First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: TransactionAbortedException Occurs Randomly
Next: T-SQL - How to use xp_regread to get ALL time zone information