locked
A transport-level error has occurred when receiving results from the server (provider: TCP Provider, error: 0 - The specified network name is no longer available.) RRS feed

  • Question

  • I've a simple SSIS Package to transfer data between two servers, let's say from SERVER01 (source) to SERVER02 (destination). When I test my SSIS package localy everything works great. When I deploy it on the SERVER02 it fails with an error:

    A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

    So I tested my "select query" directly in SSMS: Execute on my laptop - ok. Execute in SSMS on SERVER02 - fails with same error message. Execute on other servers - ok.

    So it means it's related to the SERVER02. It's virtual machine running Windows 2012 and SQL Server 2012. I can ping the SERVER01 without any problems. The data is about 170.000 rows and 20 columns.

    The query breaks after some time, and it's random. Sometimes I get 10.000 records before it breaks, sometimes 80.000.

    We've 4GB free RAM (from 16GB). When I run the query memory usage doesn't change (70%) .

    Other smaller queries run without any problems.

    Can someone point me how to solve this issue?

    Wednesday, December 11, 2013 8:07 AM

Answers

  • Hi toLL_net,

    This may be a network TCP timeout issue. You may find another error message “A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)” around. The issue can be caused by various factors. Here are some suggestions for your reference:

    • SQL Server priority boost issue. Raising the priority too high may drain resources from essential operating system and network functions and the priority boost feature will be removed in a future version of SQL Server. To disable the priority boost of SQL Server 2012, please see Configure the priority boost Server Configuration Option.
    • Network Interface Card (NIC) driver issue. Please update the NIC driver on SERVER02 to the latest version.
    • Network packet size of the SQL Server issue. The default value of the network packet size is 4096 bytes. For most applications, the default packet size is best. To configure the network packet size, please see Configure the network packet size Server Configuration Option.

    Hope this helps.

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Mike Yin Sunday, December 22, 2013 1:16 PM
    Thursday, December 12, 2013 1:13 PM

All replies

  • toLL_net: RAM perhaps is not an issue, but the network stability is likely.

    I am curious how the connection string looks like?

    Try lowering the packet size, but do have your network admins profile the connectivity.


    Arthur My Blog

    Wednesday, December 11, 2013 4:19 PM
  • I lowered the packet size to 6000 but get same error as before.

    My connectionstring:

    Data Source=SERVER01;Initial Catalog=XXX;Integrated Security=True;Connect Timeout=600;Packet Size=6000;Application Name=SSIS-CRM-XXX;

    Thursday, December 12, 2013 7:46 AM
  • Hi toLL_net,

    This may be a network TCP timeout issue. You may find another error message “A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)” around. The issue can be caused by various factors. Here are some suggestions for your reference:

    • SQL Server priority boost issue. Raising the priority too high may drain resources from essential operating system and network functions and the priority boost feature will be removed in a future version of SQL Server. To disable the priority boost of SQL Server 2012, please see Configure the priority boost Server Configuration Option.
    • Network Interface Card (NIC) driver issue. Please update the NIC driver on SERVER02 to the latest version.
    • Network packet size of the SQL Server issue. The default value of the network packet size is 4096 bytes. For most applications, the default packet size is best. To configure the network packet size, please see Configure the network packet size Server Configuration Option.

    Hope this helps.

    Regards,


    Mike Yin
    TechNet Community Support

    • Marked as answer by Mike Yin Sunday, December 22, 2013 1:16 PM
    Thursday, December 12, 2013 1:13 PM