Problems with Sql Server 2005 Merge Replication via WebService

We want to perform a Merge Replication between 2 databases via WebService (SSL).
The relevant Architecture for the Merge Replication is the following:



replicationviawebservice_1.jpg


Our DBA, Moran, made an attempt to configure the publisher and the subscriber according to the following links, without significant success so far:


http://msdn2.microsoft.com/en-US/library/ms345214.aspx
http://msdn2.microsoft.com/en-US/library/ms152511.aspx
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=352025&SiteID=1
http://msdn2.microsoft.com/en-US/library/ms151763.aspx
http://www.eggheadcafe.com/forumarchives/SQLServerreplication/




The error message we receive is: “The Proxy Auto-configuration URL was not found”.


We think that the problem is due to local LAN proxy settings. We got the idea from this post:
http://www.eggheadcafe.com/forumarchives/sqlserverreplication/nov2005/post24306635.asp



Do you have any suggestions on the matter ?


* It’s important to mention that Merge Replication over LAN works without a problem.


 


Any suggestion or reference will be most appreciated…

 

Invalid access to memory location. (Exception from HRESULT: 0x800703E6) in Microsoft.SqlServer.Replication

My team use the Sql Server 2005 Replication feature in order to synchronize between databases in different enviornments. One of the strange things though, is that the reference to Microsoft.SqlServer.Replication generated a weird “Invalid access to memory location. (Exception from HRESULT: 0x800703E6)” Error. After a quick google search I found this link which offers a nice workaround (this seems to be VS.NET 2005 bug) which worked for several folks out there but unfortunately it didn’t work for me. The search continues…

 

Inconsistency of SqlParemeter from SqlDbType.DateType with Sql Server 2005

This is one of the strangest things I’ve seen lately, but it turns out that sending “2006-04-25 14:15:26:421” (via SqlParameter from SqlDbType.DateTime) to my Sql Server 2005 database has been saved as “2006-04-25 14:15:26:423“. Does it happen every time you might ask? well, the sad answer is NO; Sometimes(really, just sometimes) it happen and sometimes it don’t.


Why do I need such accuracy? Well, this DateTime column is one of the Primary Keys in my table. Trying to pull out “2006-04-25 14:15:26:421” will give me… nothing !


I wanted to reproduce it on a clean sheet so I created a new table named “CreationDates” with the single column “CreationDate” which is from DateTime type of course.


Then I’ve written the following code:


class Program
{
   static void Main(string[] args)
   {
      Init();
      Test();
   }

   private static void Init()
   {
      // Default culture – hebrew (I need it this way).
      CultureInfo israel = new CultureInfo(“he-il”);
      israel.DateTimeFormat.LongTimePattern = “HH:mm:ss.fff”;

      Thread.CurrentThread.CurrentCulture = israel;
      Thread.CurrentThread.CurrentUICulture = Thread.CurrentThread.CurrentCulture;
   }

   private static void Test()
   {
      string connString = @”MyConnectionString”;
      using (SqlConnection conn = new SqlConnection(connString))
      {
         string query = “INSERT INTO CreationDates(CreationDate)VALUES(@CreationDate)”;
         SqlCommand cmd = new SqlCommand(query, conn);

         DateTime dt = DateTime.Now;

         SqlParameter p1 = new SqlParameter(“CreationDate”, SqlDbType.DateTime);
         p1.Value = dt;
         cmd.Parameters.Add(p1);

         Console.WriteLine(“parameter time: “ + ((DateTime)p1.Value).ToString());

         conn.Open();

         cmd.ExecuteNonQuery();
      }

      using (SqlConnection conn = new SqlConnection(connString))
      {
         string sQuery = “SELECT TOP(1) CreationDate FROM CreationDates ORDER BY CreationDate DESC”;
         SqlCommand sCmd = new SqlCommand(sQuery, conn);

         conn.Open();
         SqlDataReader reader = sCmd.ExecuteReader();
         if (reader.Read())
         {
            Console.WriteLine(“db time: “ + reader.GetDateTime(0).ToString());
         }
      }
   }
}


* I know, I could make it nicer, but this is a simple test for god sakes !


In one of my tests, this code produce the following output:


datetime.gif


The Sql Server 2005 Profile shows:


exec sp_executesql N’INSERT INTO CreationDates(CreationDate)VALUES(@CreationDate)’,N’@CreationDate datetime’,@CreationDate=”2006-04-25 14:15:26:423



So I’ve sent “2006-04-25 14:15:26:421” but the database received “2006-04-25 14:15:26:423” !


Any smart ideas ??


 

 

Update multiple tables in SQL Server 2005

I was required to update 40+ tables in our DB; Moran Benisty, our DBA master gave me a cool solution:


SELECT 
   ‘update ‘ + name + ‘ set SomeColumn = ”SomeValue” where OtherColumn = ”SomeOtherValueToMatchBy”’
FROM 
   sys.objects
WHERE 
   name LIKE ‘LK_%’




Now all I had to do is copy the results and run them. Sweet !


BTW – use at your own risk. :)