Issues About Deploy Business Logic Handler To A Web Server For Web Replication.
Jul 23, 2007
Pocket PC 2003, SQL Compact Edition, SQL2005, IIS6.0
I implemented a business logic handler to deal with conflicts. When I deploy it on the SQL server which is also the web replication server, the logic handler seems working fine. However, if I deploy this handler to another web server. The logic handler failed to be loaded.
My enviroment settings are desribed as below.
Machine A, distributor, with database and publication. The business logic handler is deployed at C:Program FilesMicrosoft SQL Server90COMBusinessLogicHandler.dll. It's registered by using sp_registercustomresolver. The @assembly is specified as @assembly=C:Program FilesMicrosoft SQL Server90COMBusinessLogicHandler.dll';
Machine B, IIS server. The same business logic handler is deployed at C:Program FilesMicrosoft SQL Server90COMBusinessLogicHandler.dll on the Machine B itself.
When I ran the web replication, the Merge Agent reported the error as below.
Error loading custom assembly "C:Program FilesMicrosoft SQL Server90COMBusinessLogicHandler.dll", Error: "Could not load file or assembly 'C:\Program Files\Microsoft SQL Server\90\COM\BusinessLogicHandler.dll' or one of its dependencies. The given assembly name or codebase was invalid.
It seemed that the Merge Agent had trouble to find my logic handler because the path reported in the error log has two backward slashes. I have no idea where did that came from. I am not sure if that's the cause of the error. Without business logic handler. I had successfully finished web replication of Machine B to sync with Machine A. If I setup web replication directly on Machine A with business logic handler, I can successfully sync as well.
Does anyone has any idea about how to correctly deploy business logic handler on a web server?
I have got a business logic update conflict handler working, but I have had to work round what appears to be a bug.
Please can someone confirm if this is indeed a bug €“ and if it is a known bug?
My conflict handler needs to take some columns from the publisher row and some from the subscriber row in the event of conflict.
I can quite happily generate a custom dataset which contains the winning row that I want €“ I can see that because I can step through the conflict handler with debug when a conflict occurs.
However, just returning ActionOnUpdateConflict.AcceptCustomConflictData from the UpdateConflictsHandler method does not set the publisher and subscriber columns correctly. I end up with different values on the two databases.
I have found that the only way to get the correct rows on both publisher and subscriber is to create a new ADO connection to the publisher and actually perform an update €“ updating all the modified columns. This now works reliably in my testing.
Fortunately, due to business rules the frequency of update conflicts are likely to be very infrequent, but I would very much like to avoid having to do the €˜unnecessary€™ update.
Notes:
I am using column level tracking €“ but I have seen the problem with row level tracking too I have mainly been using SP1 but have repeated the test on a configuration using the SP2 CTP €“ and the problem occurs there too The problem is not due to complex logic in my code. If the method just sets customDataSet = publisherDataSet.Copy and then returns ActionOnUpdateConflict.AcceptCustomConflictData, the changed and winning publisher values are not sent to the subscriber
I'm trying to implement a custom conflict resolver by inheriting from
Microsoft.SqlServer.Replication.BusinessLogicSupport.BusinessLogicModule. The replication is between a SQL Server 2005 Express subscriber and a SQL Server 2005 publisher/distributer.
The problem is that the resolver class in the DLL will not load; although, it does appear to find the DLL. (If I rename the DLL I get a "file not found error"). The exact error message is:
Microsoft.SqlServer.Replication.ComErrorException
"Error loading custom class "MergeConflictHandler" from custom assembly "MergeConflictHandler",
Error : "Could not load type 'MergeConflictHandler' from assembly 'MergeConflictHandler, Version=1.0.2502.22393, Culture=neutral, PublicKeyToken=0403e50cc4dc27fa'."."}
I placed the DLL in the directory of the program that calls SynchronizationAgent.Synchronize and tried it with and without being registered in the GAC. There was no change. Interestingly, when I move the file out of that location, but register it in the GAC, the file is not found. Thinking it may be a security problem, I gave Everyone Read & Execute and Read privileges. I believe the class is actually instantiated by replmerge.exe, an apparantly unmanaged app, so I tried marking it ComVisible. No Luck. I registered the resolver with the following T-SQL code:
, @resolver_clsid = Null The resolver I've created is not much more than a shell at this point. It is pasted below, but I tried using the code found on MSDN character-for-character and had the same problem.
using System;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.SqlServer.Replication.BusinessLogicSupport;
i'm following steps described at: http://msdn2.microsoft.com/en-us/library/ms365150.aspx
all goes fine until step 6. of paragraph "To debug a business logic handler on a Web server using Web synchronization, or for a SQL Server Mobile Subscriber".
in my case when attaching to w3wp.exe i can only debug t-sql or native code. when i forcibly check 'managed' i get: "Unable to attach to the process. Access is denied". error picture:
I am performing the Merge Replication of Timesheet table between SQL server 2005 and SQL CE on the Pocket PC. For one of the article, I would like to perform web synch for only those rows which have certain field set to true.
I am trying to use Business Logic Handler to implement this logic. I am using following code out of sample on MSDN web site. However I am not sure if this code gets executed for each row for a table during synch or does this get executed once for each table? The answer would help me write logic on applying synch changes only for few rows.
Thanks
public override ActionOnDataChange UpdateHandler(SourceIdentifier updateSource, DataSet updatedDataSet, ref DataSet customDataSet, ref int historyLogLevel, ref string historyLogMessage) { if (updateSource == SourceIdentifier.SourceIsPublisher) { //check the approved status of the timesheet if (updatedDataSet.Tables["tbl_timesheets"].Rows[0]["ts_approved"] == true) { // Set the reference parameter to write the line to the log file. historyLogMessage = updatedDataSet.Tables["tbl_timesheets"].Rows[0]["ts_id"].ToString(); // Set the history log level to the default verbose level. historyLogLevel = 1;
// Accept the updated data in the Subscriber's data set and apply it to the Publisher. return ActionOnDataChange.AcceptData;
When running a business logic handler at the subscriber, you can override SubscriberInserts, SubscriberUpdates and SubscriberDeletes. Are these methods called when data is changed coming from the server to client, from the client to the server, or in both directions?
I have 2 related tables in SQL 2005. I am using Business Logic Handler on one of those tables during Merge Replication to reject data for few rows during web synchronization. Once a row is rejected, I would like to run the Business Logic Handler for the related table to reject changes for the row with same foreign key as the rejected row in first table.
Not sure if I need to create separate Business Logic Handlers for each table or can it be achieved in a single Handler.
Also is it possible to get a list of rejected rows back from the handler?
I am currenly have a simple merge replication topology. The publisher-distibutor is SQL Server 2005 and the subscriber is a SQL Server Express. The type of subscription is non anonymous pull.
I made a custom business logic handler class that is trying the following:
When a new record is inserted in a published table on the pubsliher, some additional records are added in a differenct table in subscriber. Then the "InsertHandler" method returns "AcceptData" in order to allow the agent to add the new record in current table also.
So I am establishing a new connection to the subscriber and I am trying to add the additional recs in the different table. The problem is that this different table is also published as read-only on the subscriber. So my insert fails saying that table is not updatable.
Is there any way to bypass this problem?
In fact I realised in general that when my custom logic handler performs some DML operations on the subscriber, these are NOT considered as part of the replication e.g. the NOT FOR REPLICATION constraints and triggers are active for these operations.
Today, I download SQL Server 2005 Business Intelligence Metadata Samples Toolkit from Microsoft download center, per readme.txt instructions, build the project, but there was an error as below:
Error 1 The project could not be deployed to the 'localhost' server because of the following connectivity problems : A connection cannot be made. Ensure that the server is running. To verify or update the name of the target server, right-click on the project in Solution Explorer, select Project Properties, click on the Deployment tab, and then enter the name of the server. 0 0
After then, I just build the project,but didn't deploy it, then I try to launch DependencyAnalyzer console application, there was also an there:
Error occurred: A connection cannot be made. Ensure that the server is running.
Or maybe it can't find something it's looking for to make my additional symetrical with everything else. This is from one of my businesslogic files. What causes Visual Studio to not allow me to add something here and have it behave like the other parameters/ I'm talking about ShortDesc. Visual studio inserted () and the Get instead of GET like all the others. It would not allow me to capitalize the "get". There has to be a reason for this. Currently my program is adding all of the fields except the ShortDesc to the table in my database. Does anyone have any thoughts as to why this is the case?
#Region "Pvt Members" Private _ArticleID As System.Int32 Private _UserID As System.Int32 Private _WebSiteID As System.Int32 Private _ArticleTitle As System.String Private _Author As System.String Private _ShortDesc As System.String Private _ArticleText As System.String Private _AnchorText As System.String Private _ShowInDirectory As System.Boolean Private _Active As System.Boolean Private _DateAdded As System.DateTime#End Region#Region "Properties" Public Property ArticleID As System.Int32 GET Return _ArticleID End Get Set(ByVal Value As System.Int32) _ArticleID= Value End Set End Property Public Property UserID As System.Int32 GET Return _UserID End Get Set(ByVal Value As System.Int32) _UserID= Value End Set End Property Public Property WebSiteID As System.Int32 GET Return _WebSiteID End Get Set(ByVal Value As System.Int32) _WebSiteID= Value End Set End Property Public Property ArticleTitle As System.String GET Return _ArticleTitle End Get Set(ByVal Value As System.String) _ArticleTitle= Value End Set End Property Public Property Author As System.String GET Return _Author End Get Set(ByVal Value As System.String) _Author= Value End Set End Property Public Property ShortDesc() As System.String Get Return _ShortDesc End Get Set(ByVal value As System.String) End Set End Property Public Property ArticleText As System.String GET Return _ArticleText End Get Set(ByVal Value As System.String) _ArticleText= Value End Set End Property Public Property AnchorText As System.String GET Return _AnchorText End Get Set(ByVal Value As System.String) _AnchorText= Value End Set End Property Public Property ShowInDirectory As System.Boolean GET Return _ShowInDirectory End Get Set(ByVal Value As System.Boolean) _ShowInDirectory= Value End Set End Property Public Property Active As System.Boolean GET Return _Active End Get Set(ByVal Value As System.Boolean) _Active= Value End Set End Property Public Property DateAdded As System.DateTime GET Return _DateAdded End Get Set(ByVal Value As System.DateTime) _DateAdded= Value End Set End Property #End Region
Hey guys,I have to import a csv file to my database and then add some values to other fields based on these values. At present I use a store procedure that does a bulk insert and then I use an update and fetch inside the sql to perform these updates. I am not sure if this is the best way. Is this bad, since some amount of busniess logic is in the store procedure?If it is, how can I do this better?Thanks for the answer.
I am working with stored procedures to provide conditional business logic for a customer based on triggers. Is it possible for a stored procedure to execute an external program ie opening or distributing a crystal report?
I guess I just don't get the reasoning behind the new SqlDataSource control. Haven't we just spent the last decade or so evangelizing and learning how and why to separate business logic from the display in VB 6 and VS.NET? In this age of programming for disparate devices (desktop, mobile, PocketPC, etc.) when this separation makes more sense than ever, why is MS pushing us to go back to putting our logic and data access rules and objects back in the display? It doesn't make sense. Why would anyone do this? And why would all the experts and MVPs at ASP.NET, DevX, 4GuysFromRolla, etc., go along with this?
ALTER TABLE [dbo].[bkrm_lendcoll] ADD CONSTRAINT    ReqdCovgAmt_constraint33 CHECK    ( case  when CovgAmt = 0 and  ReqdCovgAmt = 0 then 1  when CovgAmt = 0 and  ReqdCovgAmt = 1 then 1  when CovgAmt = 1 and  ReqdCovgAmt = 0 then 1  when CovgAmt = 1 and  ReqdCovgAmt = 1 then 0 end =1 )
This is my first attempt to add a constraint for business logic. Â The desired behavior is that the two columns together have the same behavior as a radio button. Â (one can be true, the other true, both can be false, but both cannot be true.) I get this error when I attempt to update it.
The ALTER TABLE statement conflicted with the CHECK constraint "ReqdCovgAmt_constraint33". The conflict occurred in database "Std", table "dbo.lendcoll".
So, basically my question is, when you have two bit columns and want them to have the truth table such as described, how can I set a Check constraint to enforce this?
I have a merge replication topology that allows some subscribers (clients) to retreive a number for certain columns (ie invoice number, order number, etc).
I have implemented a custom business logic handler to check for inserts from the subscriber and before updating the server, I call a stored proc on the server to generate a new number. I update the data set and call ActionOnDataChange.AcceptCustomData. This works great for the publication db but I am trying to get this new number back into the subscription db. I am using web synchronization so I really don't have a connection back to the subscriber database.
Any Ideas? I was thinking about keeping track of the updates myself and build a sql string and somehow get that back to the subscriber to run updates for those columns... I am wondering if I am pushing the limits of the business logic handler? Was it designed to do this? Of course the easy answer is to modify my client application but I am trying to avoid that at this point.
Any help/suggestions would be greatly appreciated!
Hello,I stuck in a delimma.Where to put the business logic that involves only one updatebut N number of selects from N tables........with N where conditions
What is the way we could implement a business logic from a Table bystoring it statemnnets in a colums and defining an execute sql toexecute it.Some legal requirements make it diffcult for us to createmodify stored procdures so Iwant to have a process where we create newrows in a table and execute it to execute business logic.All views are welcome.Havin g one table two tables different approaches to store ststementsand execute them....Case logic how to implement it?Flags in the tble colums in the tablesetcThanksAjay Garg
I am currently working with C and SQL Server 2012. My requirement is to Bulk fetch the records and Insert/Update the same in the other table with some business logic? How do i do this?
I am interested for tips/pointers on Remote Data replication using SQL 2005. Let's say there are 2 sites (A and B). I plan to have site A comprised of 2 SQL servers (1 active and 1 on standby). Site B will have the same configuration. If site A becomes disasterous then Site B will come up with zero loss of data or minimal loss of data. Will SQL data replication provide solution? or I need to look at other methods. By the way, both sites have servers attached to SAN storage. Thanks in advance;
Does anyone have a successful prescribed sequence for installing VS2005 and Business Intelligence Reports Projects on a Vista Business workstation to be used to create reports for a server?
I've looked through everything I can find here and I don't seem to see a clear solution without a lot of trial and error.
Fact is, I've not been successful getting just the reports to install on a plain XP box. Of course, the report creation looks fine on the server but I don't want to work directly on the server.
Can anyone take me through synchronization of contacts within Business Contacts Outlook into Microsoft Small Business Accounts?
I run a stand alone PC with NO network. When SBA came SQL was also installed. Apparently you can synchronise Contacts within Business Contacts with SBA but both SBA & Outlook should work through the same SQL server.
Am getting errors trying to deploy a dtsx created by ms (the reporting services execution log one) to which I have made zero changes, but it is not working (2 errors shown below)
error from deployment wizard: ===================================
Could not save the package "H:SSISRSlogRSExecutionLog_UpdateinDeploymentRSExecutionLog_Update.dtsx" to SQL Server "xxxxxxxxxxx". (Package Installation Wizard)
===================================
The SaveToSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed.
I am a bit of a novice to SQL Server programming. So, forgive me if my question is too silly or easy.
I have an client-server application where the server connects to an SQL Server and does simple DB operations. In a particular test case, after my SQL Server is shut down and restarted when I try to execute an SQL Statement, the SQLExecute function fails. The problem is that the connection handle used to allocate and prepare the statement is invalid. So, I need to reconnect to the server.
Now my question is, how do I determine if the connection handle is valid or not ? That is, is there any API/function which when used can detect whether the connection handle is invalid or not.
I have tried calls to SQLGetConnectAttr and SQLGetInfo passing the connection handle, but these API's still return the correct value even if the handle is invalid.
Hi, sorry for another newbie question but I was wondering if the following is possible:
I have to take some information from a table which I have already created a query for. This information then has to be inserted into a new table but needs another column (not the promary key) with another unique custom identifer for each record in the format EX01 which is incremented by 1 for each record. I was wondering how is it possible to do this?
My approach was to create a view and then insert the values form the view into the new table but I still have no idea how to do the unique identifer. Was the first part of my approach correct or have been wrong from the start?
I am using C# and ASP.NET 2.0, with SQL Server 2000. In my database I have a table that is similar to the following: WebpageId WebpageAddress Handler 1 /company/about ~/about.aspx 2 /blog ~/blog.aspx As you can guess, one of my queries will be a SELECT command where WebpageAddress = @address. The hiccup I have is with a friendly URL such as the following: /blog/2005/10/6 The friendly URLs have no extension, so I cannot immediately "pick out" the extension. (If the address were /blog.aspx/2005/10/6, then things would be simpler.) What I am doing at present is using a loop in C# where I first perform a query with that full address. If no match is found, I trim the address back to the last slash (/blog/2005/10) and perform another query. If no match is found, I trim the address (/blog/2005) and perform another query. Again, no match is found, so I trim the address again (/blog) and perform yet another query. This time, a match is found, in which case the URL rewriting looks vaguely like this: ~/blog.aspx?parameters=2005/10/6 While this works fine, these friendly URLs require hitting the database up to four or five times. My question is, can this looping logic be moved to SQL Server? My SQL knowledge is extremely basic, so I am just looking for someone to point me in the right direction. If it is not possible, I'd love to know now rather than wasting hours trying. If it is possible, I've love to know the keyword or technique involved, so that I can Google for the full answer.
It is a initialization of the two dimensional array DETAILED-VOUCHER(Temporary Variable) which consists of the account number for that account number different group of people say as (teachers, state police etc) have different 12 kinds of amounts such as (DEATH-AMT, PAYEE-AMTetc ) for every account number. SUB (Temporary Variable) is used as subscript whose value will be incremented by 1 after initializing current position by zero in order to initialize all 12 positions in array.
LOP-ZERO-2. MOVE ZEROS TO CNT-V (1 SUB). MOVE ZEROS TO AMT-V (1 SUB). ADD 1 TO SUB. IF SUB NOT GREATER THAN 12 GO TO LOP-ZERO-2.
MOVE 2 TO SUB.
LOP-ZERO-3. MOVE CNTS (1) TO CNTS (sub). ADD 1 TO SUB. IF SUB NOT > 13 GO TO LOP-ZERO-3.
MOVE '401.21' TO ACC-NO-V (1). ---------- This are the hotcode values MOVE '410.01' TO ACC-NO-V (2). MOVE '410.02' TO ACC-NO-V (3). MOVE '410.03' TO ACC-NO-V (4). MOVE '410.04' TO ACC-NO-V (5). MOVE '410.06' TO ACC-NO-V (6). MOVE '410.05' TO ACC-NO-V (7). MOVE '411.01' TO ACC-NO-V (8). MOVE '411.02' TO ACC-NO-V (9). MOVE '331.01' TO ACC-NO-V (10). MOVE '212.19' TO ACC-NO-V (11). MOVE '212.22' TO ACC-NO-V (12).
MOVE ZEROS TO XYZ-S (1). MOVE ZEROS TO MNO-B (1). MOVE ALL '9' TO A-KEY MOVE 01 TO D-X. MOVE 1 TO SYS-Y
Please give suggest me how to write logic in the sql server. I want to how to write two dimensional array loop in the sql server.
Hi all, Ther is a DSN (fileDSN, SystemDSN, UerDSN) on the client machine. User of the client machine has created a DSN which is connecting to the MS-Access database of his own machine. When user deploy that DSN to the Local Server, Server getting problem to finding the DSN (which is made on client). Please help me how to Deploy this DSN to server.
I do not have access to VS, but I hear it can do this... how can I do it without VS?
I am using CREATE ASSEMBLY to load dll form my server to SQLServer on my server (Same machine). How can I load the dll to another server? Do I have to write it on a directory on that server first? I tried to run the command from my pc connected to the other server, and I get a "cannot find file" error.
I have a below query which have a date filter like "EST_PICK_DATE between '2015-02-01' and '2015-06-01'", where the logic is EST_PICK_DATE should be 3 months from the current month and 1st date of next month. Ex for current month MAY, EST_PICK_DATE shoulc be between '2015-02-01' and '2015-06-01'. I need to write below query dynamically. In below query i have hardcoded the value ("EST_PICK_DATE between '2015-02-01' and '2015-06-01'"), but it should take dynamically. How to achieve this?
I am using this query in SSIS package, So Shall i do in SQL level or we should implement this logic in package? If yes, How?
INSERT INTO STG_Open_Orders (Div_Code, net_price, gross_price) SELECT ord.DIV_CODE AS Div_Code, ord_l.NET_PRICE AS net_price, ord_l.gross_price AS gross_price, FROM ORD ord inner join ORD_L ord_l ONord.ORD_ID=ord_l.ORD_ID WHERE ord_l.EST_PICK_DATE BETWEEN '2015-02-01' AND'2015-06-01'
I am trying to split the annual cost into monthly numbers based on the contract Period.Since the contract period varies from company to company not sure how to implement the logic.
This is the code for calculating a formula field in Crystal Reports. I want to implement the same in Sql Server Reporting Services.. But it doesn't have the feature of For Loop.... The strings started with @ are formula fields.... Can anyone tell me, how can the below code be implemented in Sql Server Reporting Services
numbervar YR; for YR := 1 to {@CalcFiscalAge} step 1 do ( IF YR = {@CalcLifeCode} +1 then locFactor1 := 1; exit for; IF (YR = 1 OR YR = {@CalcFiscalAge}) THEN HALF_YEAR := 2 ELSE HALF_YEAR := 1 ; LINEAR := ROUND(REM/ ({@CalcLifeCode}-YR+1.5)/HALF_YEAR,4); MACR := ROUND(REM / {@CalcLifeCode}*{@CalcSRate}/HALF_YEAR,4); IF MACR >= LINEAR then DEP := MACR ELSE DEP := LINEAR; locFactor1 := locFactor1 + DEP ; REM := 1 - locFactor1; locFactor := locFactor1; );
