Prev: TransactionAbortedException Occurs Randomly
Next: T-SQL - How to use xp_regread to get ALL time zone information
From: Goran Djuranovic on 17 Sep 2008 15:40
Hi all,
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?

TIA
Goran


From: Erland Sommarskog on 17 Sep 2008 18:17
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 18 Sep 2008 04:22
Hello Goran

Thank you for contacting Microsoft Online Community Support. It is
Mark,again. I'm glad to assist you with the issue.

For this case, you indicated that the lind is queried by a SQL job which
runs a DTS package; and when the query runs for 45 seconds, a time out
error happaned. Is it correct? If I misunderstand anything, please tell me
directly. It
will help us to resolve this issue quickly. Once the issue resolved, I'll
appreciate your verification.

Before we move on, please help to confirm your main concern.
1) would you like us to help to reduce the time the query runs?

2) Is your main concern is to fix the time our error and make the query run
successfully even if it will run for 45 seconds.

"Timeout expired" is usually the command timeout, it is related to the
Connection Timeout, Query Timeout or some other setting.

To anrrow down the issue, please help to answer the following questions.
1) How long does the query usually run?

2) Please describ the scenario in detail. For example: Is the linked server
another SQL Server or other database server?
how many computers are involved into the issue?

3) Provide me the query you try to run when the error happan. If it is not
convenient to you to public it here, please email me. My email address is
v-fathan(a)online.microsoft.com(remove online)

4) Tell me how did you create the DTS. How many tasks are created in it and
what are those tasks?

5) Since the issue is related to a linked server, we should verify what is
the problematic server. It is best for us to use SQL Profile to confirm. If
it is not convenient, please email me. I would like to share you some
general steps and give you a tdf file.

6) give me the linked server configuration information. Please run the
following script and send the result with TXT format to me.
a) connect to SQL Server by Management Studio and "Ctrl+T"
b) run the script
SET NOCOUNT ON
GO
PRINT ''
PRINT '==== SELECT GETDATE()'
SELECT GETDATE()
PRINT ''
PRINT ''
PRINT '==== SELECT @@version'
SELECT @@VERSION
GO
PRINT ''
PRINT '==== SQL Server name'
SELECT @@SERVERNAME
GO
PRINT ''
PRINT '==== Host (client) machine name'
SELECT HOST_NAME()
GO
PRINT ''
PRINT '==== sp_configure advanced'
EXEC sp_configure 'show advanced', 1
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure
GO
PRINT ''
PRINT '==== Active Trace Flags'
DBCC TRACESTATUS(-1)
GO
PRINT ''
PRINT '==== sp_helpsort'
EXEC sp_helpsort
GO
PRINT '======== SQL commandline args'
EXEC master..xp_instance_regenumvalues 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters'
GO
PRINT '======== Default client netlib and server aliases'
EXEC master..xp_regenumvalues 'HKEY_LOCAL_MACHINE',
'Software\Microsoft\MSSQLServer\Client\ConnectTo'
EXEC master..xp_regenumvalues 'HKEY_LOCAL_MACHINE',
'Software\Microsoft\MSSQLServer\Client\SuperSocketNetLib'
GO
PRINT '======== MDAC version information'
EXEC master..xp_regenumvalues 'HKEY_LOCAL_MACHINE',
'Software\Microsoft\DataAccess'
GO
PRINT ''
PRINT '==== sp_helpserver'
EXEC master..sp_helpserver
GO
PRINT ''
PRINT '==== Linked server properties'
PRINT ''
PRINT '======== sp_helplinkedservers'
EXEC master..sp_linkedservers
PRINT ''
PRINT '======== sp_helplinkedsrvlogin'
EXEC master..sp_helplinkedsrvlogin
PRINT ''
PRINT '======== xp_enum_oledb_providers'
EXEC master..xp_enum_oledb_providers
PRINT ''
PRINT '======== OLEDB provider SQL registry properties'
DECLARE @sql70or80xp sysname
IF CHARINDEX ('7.00.', @@VERSION) = 0
SET @sql70or80xp = 'master..xp_instance_'
ELSE
SET @sql70or80xp = 'master..xp_'
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE name like '#providers%')
DROP TABLE #providers
CREATE TABLE #providers
(prov_name varchar(255), parse_name varchar(255), prov_descr text)
INSERT INTO #providers
EXEC master..xp_enum_oledb_providers
DECLARE @prov_name varchar(255)
DECLARE @regpath varchar(4000)
DECLARE curs INSENSITIVE CURSOR
FOR SELECT prov_name FROM #providers
FOR READ ONLY
OPEN curs
FETCH NEXT FROM curs INTO @prov_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT ''
PRINT '======== Registry properties for provider ' + @prov_name
SET @regpath = 'Software\Microsoft\MSSQLServer\Providers\' + @prov_name
EXEC ('EXEC ' + @sql70or80xp + 'regenumvalues ''HKEY_LOCAL_MACHINE'', '''
+ @regpath + '''')
FETCH NEXT FROM curs INTO @prov_name
END
CLOSE curs
DEALLOCATE curs
GO
PRINT '==== ODBC DSN info'
PRINT 'EXEC master.dbo.xp_cmdshell ''regedit /e %tmp%\odbc_pss.txt
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC'''
EXEC master.dbo.xp_cmdshell 'regedit /e %tmp%\odbc_pss.txt
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC'
PRINT 'EXEC master.dbo.xp_cmdshell ''dir %tmp%\odbc_pss.txt '''
EXEC master.dbo.xp_cmdshell 'dir %tmp%\odbc_pss.txt '
PRINT 'EXEC master.dbo.xp_cmdshell ''type %tmp%\odbc_pss.txt'''
EXEC master.dbo.xp_cmdshell 'type %tmp%\odbc_pss.txt'
PRINT 'EXEC master.dbo.xp_cmdshell ''del %tmp%\odbc_pss.txt'''
EXEC master.dbo.xp_cmdshell 'del %tmp%\odbc_pss.txt'
GO
PRINT ''
PRINT '==== SELECT GETDATE()'
SELECT GETDATE()

If anything is unclear, please let me know.

I look forward to your update.

Thanks.

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.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

From: Goran Djuranovic on 23 Sep 2008 09:51
Erland & Mark,
Remote Query Timeout is already 600 seconds, so no need to increase it, I
think. The funny thing is now I cannot get it to fail. I let it run
scheduled and manual 20 times (10 each), and it runs fine. I haven't changed
anything. I found that 90% of the time the process runs between 15-20
seconds (not 30), when it runs successfully. So, I honestly think it could
be a network issue. Well, I will update you with more details, if it fails
again.

Thanks for your help.
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 24 Sep 2008 03:37
Hi Goran,

I'm glad that the issue disappears now. Congratulation.

If the original issue happens again, it is welcom to post it here again.

If I can assit you anything related to the technical issue, please tell me.
It is my pleasure to assist you.

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.
=========================================================

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