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.)"

    Question

  •  

    We are using WCF 3.5 and SQL Server 2000 database instance.
    We have MS DTC running and configured.

    I am using the TransactionScope functionality in my service call. My service call calls a SP in a SQL Server 2000 database. 

    I have created a new SQL connection that does not enlist the TransactionScopes transaction object, but instead creates its own transaction object. I then call the SQL execute command to run the SP. I get a SQL Exception back, capture the error that was raised by the RAISERROR function in the SP that I called. This passes back the error message to the consumer of the service and everything looks fine. But then I do another call on this same method that uses the same code and I get the "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.)"

    I ran a SQL Profiler trace on these calls and noticed that after most of the SP are execute the SP sp_reset_connection is ran, but after I execute my SP that calls the Rollback, the sp_reset_connection SP never gets called until I try and call the SP a second time. Then it seems the sp_reset_connection SP get called and resets my connection.

    This seems to be a connection issue now and I am leaning towards it being a MS DTC issue because if I run the same SP without a transaction at all I don't have any issues.

    Any suggestions or help would be much appreciated.

    Thanks


    Here is the code I am using for my connection.

    using (TransactionScope txScope = new TransactionScope(TransactionScopeOption.Required))

    {

    using (SqlConnection conn = new SqlConnection(Data Source=ServerName\SQL2000;Initial Catalog=DatabaseName;Integrated Security=True;Enlist=false))

    {

    SqlCommand cmd = new SqlCommand("spname", conn);

    conn.Open();

    cmd.Transaction = conn.BeginTransaction();

    try

    {

    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(new SqlParameter("@Parameter1", P1));

    cmd.Parameters.Add(new SqlParameter("@Parameter2", P2));

    cmd.Parameters.Add(new SqlParameter("@Parameter3", P3));

    cmd.Parameters.Add(new SqlParameter("@Parameter4", P4));

    cmd.Parameters.Add(new SqlParameter("@Parameter5", P5));

    cmd.Parameters.Add(new SqlParameter("@Parameter6", P6));

    cmd.Parameters.Add(new SqlParameter("@Parameter7", P7));

    cmd.Parameters.Add(new SqlParameter("@Parameter8", P8));

    cmd.Parameters.Add(new SqlParameter("@Parameter9", P9));

    cmd.Parameters.Add(new SqlParameter("@Parameter10", P10));

    cmd.Parameters.Add(new SqlParameter("@Parameter11", P11));

    SqlDataAdapter adapter = new SqlDataAdapter();

    adapter.SelectCommand = cmd;

    adapter.Fill(result);

    cmd.Transaction.Commit();

    txScope.Complete();

    }

    catch (SqlException sqlEx)

    {

    cmd.Transaction.Rollback();

    conn.Close();

    conn.Dispose();

    //AMS ERROR, SQL Exception

    Response.ServiceResponseMessageList.Add

    (

    new ServiceResponseMessage(MessageBase.MessageTypeEnum.Error, sqlEx.Message)

    );

    txScope.Dispose();

    }

    }


    • Edited by Jason22 Wednesday, February 04, 2009 1:31 PM Code used for my connection
    Wednesday, February 04, 2009 1:21 AM

Answers

  • Hi Jason,
      From the exception, this looks like a network firewall configuration issue. If your SQL server is configured on a remote machine, you should make sure that MSDTC is enabled in the firewall and check that all your networking settings are configured correctly. 

      This link might be of some assistance to you for troubleshoot firewall issues with MS DTC.

      How to troubleshoot MS DTC firewall issues

    Thanks,
    Lin.
    Monday, February 16, 2009 9:00 PM