How to avoid MSTDC

Question

I want synchronize data between two SQL servers 2008 Express Edition. I would like to avoid escalating SQL queries to MS DTC service (MS DTC service is not running). For synchronization I use stored procedures without transactions.

I have more stored procedure. The procedures which download data from linked server and then store it to local DB works fine.

I am using stored procedures to upload data to linked server. Inside stored procedure I have no transaction used.

When I run stored procedure from SQL Management Studio, data are correctly uploaded to server. But when I call this procedure from .NET 4.0 C# code, it ends up with error

8501 - MSDTC on server 'compName\SQLEXPRESS2008' is unavailable.

No transactions used within application code. Sometimes the data is uploaded even from the code. I cannot figure out when the MSDTC is necessary and when not.

Why is MSDTC needed when no transaction used?

EDIT1:

I have little progress. This code is used for calling stored procedure. First time when the procedure is called, the data is correctly uploaded to server. Next time it ends with exception 8501.

  SqlCommand sqlCmd = new SqlCommand("spUploadUnsendStockDocument");
  sqlCmd.CommandType = CommandType.StoredProcedure;
  sqlCmd.Parameters.AddWithValue("@pDebug", false);
  sqlCmd.Parameters.AddWithValue("@boxId", boxId);

  // Return value as parameter
  SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);
  returnValue.Direction = ParameterDirection.ReturnValue;
  sqlCmd.Parameters.Add(returnValue);

  try
  {

      using (SqlConnection conn = new SqlConnection(dbConnection.ConnectionString))
      {
        conn.Open();
        sqlCmd.Connection = conn;
        sqlCmd.ExecuteScalar();
        sqlCmd.Dispose();
        conn.Close();
      }

    if (Convert.ToInt16(returnValue.Value) == 1)
      LogError("error while sending");

    return Convert.ToInt16(returnValue.Value) == 0;
  }
  catch (Exception ex)
  {
    Log(ex);
    return false;
  }

score 0 · Answer 1 · answered Apr 15 '14 at 19:43

0

Why are you trying to manage the sync'ing yourself?

How come you are not using the built in replication features built-in to SQL SERVER 2008 Express?

http://technet.microsoft.com/en-us/library/ms165686(v=sql.105).aspx

This will allow you to push and pull between databases to keep them synchronized.

answered Apr 15 '14 at 19:43

ChampChris

1,565
5
26
44

I need to make replication between 2 SQL Servers 2008 Express Edition. I thought that I cannot use built-in replication between two Express Edition servers. – MartinM Apr 15 '14 at 21:33
directly from the the link i sent you ... Microsoft SQL Server 2008 Express (SQL Server Express) can serve as a Subscriber for all types of replication, providing a convenient way to distribute data to client applications that use SQL Server Express. When using SQL Server Express in a replication topology, consider the following: – ChampChris Apr 15 '14 at 23:03
Thank you for answers, but according to http://stackoverflow.com/questions/6248298/how-to-synchronize-2-databases. The replication feature cannot be used. And the table structure is slightly different in both databases. – MartinM Apr 16 '14 at 07:16

How to avoid MSTDC

1 Answers1