Deploying .Net / SQL Server 2005 replication

Hi,

I am experiencing a problem with deployment of a VB .Net application which carries out merge replication, and would greatly appreciate any assistance. I am currently struggling to find stuff about my problem on the net, which probably means either (a) I'm missing something really simple or (b) I shouldn't be trying to do this in the first place. :)

The story so far:
1. I added the SQLMergXLib.dll COM to my Visual Studio project, allowing me access to SQL Server merge replication functionality from my code, and wrote a procedure for synchronising a given pull subscription. This worked beautifully and with minimal fuss.
2. I tried to deploy the project to a different machine. I received an error to the effect of, "Couldn't create object, it's a COM object and it's not registered correctly." I have encountered this problem a few times before when trying to use Interop, so I called myself a few names, went back to the deployment project, and specified that the object should be registered. Still no joy. I tried this a couple of different ways round but then...
3. ...found an article on MSDN suggesting that I should consider using the managed Microsoft.SqlServer.Replication, Microsoft.SqlServer.Management, Microsoft.SqlServer.RMO interfaces instead. I reasoned that this would eliminate the registry issue altogether as everything would become native to the .Net framework, and this seemed infinitely preferable anyway, so I promptly substituted the SQLMergX DLL for these, rehashing my code to match. Again, this built and ran OK on my development machine.
4. I tried to deploy the project to a different machine. I received the following error: "Could not load file or assembly 'Microsoft.SqlServer.Replication, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. This application has failed to start because the application configuration is incorrect."

Now it's worth pointing out that I think I know what the problem might be here: No SQL Server of any kind is installed on the target machine. However, SQL Server is not required to deploy standard database solutions in .Net, and I am hoping this is also true with the Microsoft.SqlServer... namespaces, as it is a requirement that the subscription database can be located anywhere (not necessarily the target machine), and therefore that SQL Server need not be present on the target machine. However, if this is simply impossible, please let me know so that I can look at other alternatives.

Other than that, I don't mind what method I use to get the synchronisation working as long as it works (I've been at this a good few days now...), so if anyone has done this before and can offer any assistance (any registering/files I need to include etc, I am hoping it is something that simple), it would be appreciated.

Thanks,

Steve.

Hi, Steve,

You may find following SQL Server 2005 Books online topic helpful to your case:

Requirements for Deploying Replication ActiveX Controls

When deploying the Microsoft ActiveX replication controls independently of Microsoft SQL Server 2005, you must include additional files in the installation kit that you use to distribute your application. If you deploy your application using these ActiveX replication controls on a computer where Microsoft SQL Server 2005 Express Edition or SQL Server 2005 client software is also installed, these files will already be present.

This topic describes how to deploy native code applications that use the replication ActiveX controls to access SQL Server 2005 functionalities. To obtain all of the benefits provided by the Microsoft .NET Framework, you should consider updating native code applications to managed code using Microsoft Visual Studio 2005. From managed code applications, you can access the same replication ActiveX functionality using classes in the Microsoft.SqlServer.Replication namespace.

If you use Microsoft Visual Studio and the Deployment Wizard to prepare your installation kit, the wizard will recognize that the files from the Referenced Libraries column in the following table are needed, and will include them in the installation kit. The wizard will also indicate that dependency information for these files is not available. Include the files from the Dependent Libraries column of the table in your installation kit (click the Add button on the Included Files page of the wizard). The paths shown in this table assume SQL Server 2005 was installed to the default location.

Replication Control Referenced Libraries Dependent Libraries

Microsoft SQL Distribution Control 9.0

Sqldistx.dll

Replerrx.dll

C:\Program Files\Microsoft SQL Server\90\COM\Rdistcom.dll

C:\Program Files\Microsoft SQL Server\MSSQL\MSSQL.1\Binn\Sqlrepss.dll

$(WinSysPath)\Sqlwoa.dll

|||

Thank-you for your response.

I had followed this article with regard to the ActiveX deployment and experienced the problem discussed above. Does anyone know if there are any articles describing the requirements of this further?

The article also implies that moving over to managed code provides the same functionality but with less fuss, so I would rather do it this way if at all possible. The files Visual Studio included when deploying my implementation (which required access to the SynchronizationAgent, MergePullSubscription and ConnectionInfo classes, I think) were:

Microsoft.SqlServer.BatchParser.dll
Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.RegSvrEnum.dll
Microsoft.SqlServer.Replication.dll
Microsoft.SqlServer.Rmo.dll
Microsoft.SqlServer.ServiceBrokerEnum.dll
Microsoft.SqlServer.Smo.dll
Microsoft.SqlServer.SmoEnum.dll
Microsoft.SqlServer.SqlEnum.dll
Microsoft.SqlServer.WmiEnum.dll

Are there other files I need to include with any of these for their classes to work correctly?

Thanks in advance for any help, still struggling with this a bit. :)

Steve.

|||

Hi, Steve,

I got more information from our developer where it seems the best way to deploy RMO based application is to install a SQL Server instance on the client machine (like SQL Server Express which is freely available).

Here is the quote from our developer:

We don’t really have a good story for developers who want to deploy an application based on RMO as the synchronization agents in RMO are just wrappers of the existing controls which are in turn wrappers of the agent components. As a result, anyone who wants to deploy an RMO application would need to include most of everything in 90\COM + resources\1033\replres.rll. To make matters worse, all of our components depend on sqlncli.dll and the 80 CRT which is deployed as a WinSxS component. The snapshot agent itself also depends on the SMO assemblies which are deployed in the GAC. Given the external dependencies, the simplest way to deploy an RMO application is probably to install some version of SQL Server (Express should be sufficient for most types of applications).

Hope that help,

Zhiqiang Feng

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi,

Yeah, I spent a lot of yesterday introducing myself to the delights of DLL Hell (having begun my career as a .Net programmer, it's my first time... :)), with regards to both scenarios I described, and ultimately plumped for doing exactly what you suggest: Deploying SQL Server Express on the target machine.

I thought that it seemed excessive but it seems to be the best way forward.

Thanks for all your help,

Steve.

Dependencies not correct with temporary tables --> replication is failing

Hello all,

here is a stored procedure I have:

CREATE PROCEDURE spU_GUI_AppliqueConditionFinancementPourGuichet
(
@.GuichetId int,
@.Validateur nvarchar(40)
)
AS
CREATE TABLE #tReservations (ReservationId int)

IF (dbo.GetSiGuichetEnRegle(@.GuichetId) = 0)
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteFinancement @.GuichetID, @.Validateur
ELSE
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteAgrement @.GuichetID, @.Validateur

SELECT GR.Id,
dbo.FormateNoms(GR.Name) AS Names
FROM #tReservations
LEFT JOIN AnotherTable GR ON GR.Id = AnotherTable.id
DROP TABLE #tReservations
GO

The creation is ok but when I look to the dependencies, I see that it depends on GetSiGuichetEnRegle only.

For me, it shall also depend on

AnotherTable

spU_GUI_AppliquePerteFinancement

spU_GUI_AppliquePerteAgrement

FormateNoms

Apparently the dependencies are not calculated correctly because I'm using a temporary table.

My problem is that I have updated this stored procedures (and the two other that I call) to add a new parameter. As a consequence, when I do a replication, this is failing saying that I have an extra parameter. I imagine that because my dependencies are not correct, the replication is not occuring in the correct order and so it's still using the old definition of the stored procedure.

Do you have any idea on how I can force the dependencies to be calculated correctly ?

Thanks

Did the error happen when you are applying a snapshot or when the distribution agent is applying a DDL change for the stored procedure update? If this happened while the snapshot was being delivered to the subscriber, I have the following questions for you:

1) Are you using 'drop' as the pre-creation command for the stored procedure articles? If so, you should theoretically not be seeing the error that you saw when the snapshot was delivered to the subscriber since the referenced procedures would either have been drop (or recreated with the new definition).

2) Are the referenced stored procedures (spU_GUI_AppliquePerteFinancement and spU_GUI_AppliquePerteAgrement) included in the publication as well? (It never hurts to ask the obvious.)

3) It would be great if you can post history messages of the distribution agent when the snapshot was applied, I just want to see the relative order of how the objects that you mentioned are applied. I will also be interested to see the history messages from the snapshot agent.

There is an undocumented -EnableStoredProcedureDependenciesReevaluation 1 switch in the snapshot agent which forces the snapshot agent to compute dependency ordering for stored procedure articles in a more accurate manner but I have a feeling that the real problem is something else.

-Raymond

|||

Hello Raymond,

Indeed there seems to be problems with the dependencies between stored procedures and tables, but the main issue here seems to be to be related to the latest problem we spoke together

After isolating the problem, I've found that the stored procedure that could not be replicated contains a "INSERT INTO" statement.

I will try a work-around by playing with post-replication scripts, while waiting for SP2 ;-)

Anyway, I will investigate the solutions you give for the dependencies

Pierre-Emmanuel