Problem using Merge in a trigger

I am using NAV 5.0 SP1, and SQL Server 2005.

I have 2 databases on different servers that I am trying to keep syncronized. I am syncronizing the Item table. I have added a boolean field to the Item table, called Is Global.

If Is Global = TRUE in both databases, then it copies all information from the Source to the Target (it never copies from the Target to the Source, nor does it delete from either database).

The Procedure:

CREATE PROCEDURE [dbo].[MergeDataItem] AS
MERGE [TargetDB].[dbo].[TargetCompany$Item] AS T
USING [SourceDB].[dbo].[SourceCompany$Item] AS F
ON T.[No_] = F.[No_]
WHEN Matched
AND F.[No_] IN (SELECT [No_] FROM [dbo].[SourceCompany$Item] WHERE [Is Global] = 1)
AND F.[No_] IN (SELECT [No_] FROM [TargetDB].[dbo].[TargetCompany$Item] WHERE [Is Global] = 1)
THEN
UPDATE SET
T.[No_ 2] = F.[No_ 2]
WHEN NOT MATCHED BY TARGET
AND F.[No_] IN (SELECT [No_] FROM [dbo].[SourceCompany$Item] WHERE [Is Global] = 1)
AND F.[No_] IN (SELECT [No_] FROM [TargetDB].[dbo].[TargetCompany$Item] WHERE [Is Global] = 1)
THEN
INSERT
(
[No_]
,[No_ 2]
)
VALUES
(
F.[No_]
,F.[No_ 2]
);
GO

The Trigger (on SourceDB Item table):

CREATE TRIGGER [dbo].[RunMergeDataItem]
ON [dbo].[SourceCompany$Item]
AFTER INSERT, UPDATE
AS
BEGIN
EXECUTE [dbo].[MergeDataItem]
END;
GO

With this Stored Procedure and Trigger active, I get the following message:

Another user has modified the record for this Item after you retrieved it from the database.
Enter your changes again in the updated window, or start the interrupted activity again.
Identification fields and values: No.=‘220-110’

If I remove the line “AND F.[No_] IN (SELECT [No_] FROM [TargetDB].[dbo].[TargetCompany$Item] WHERE [Is Global] = 1)” from the stored procedure, I do not get an error. However it does not check for the IsGlobal value in the TargetDB either.

Is there something I’m missing? Or should I be doing this in a different way? Any help is appreciated. Also, is there a way that i can only update the most recent change (instead of the entire record set every time)?

You should be doing this in Navision. NOT in SQL.

Ok. I know how to change companies at the record level, but how do I change databases? The Target database is on a different server than the Source database.

Look at the property “Linked Object” in the table properties. You create a new table that links everything together.

The LinkedObject property is just a boolean. It’s not clear how to set what it is linked to or to set restrictions (filters) on what to update. Can you be more specific?

Thanks for your help.

Ben