Direct update into SQL and MaxEchange

Apply your updates to the the SQL views, not to the underlying SQL tables.

Hi,

I am not familiar with view, but when you open a view in the Maximizer database you don't see any data. As oppose to other database, Like Dynamics GP where you see the data when opening their view.

Would updating the "Last_modified_Date" field do the trick for MaxExchange?

Andre,

Changing the last modified date won't make any difference I'm afraid. Max Exchange operates on the basis of Transaction Logging. Each time Maximizer updates the database it logs the transaction in the Max Exchange tables, when Max Exchange runs it goes through the tables, packages up the transactions and sends them out as compressed binary packets to the remotes. The remotes decompress them and read in the transactions.

Writing data directly to the tables bypasses this transaction logging mechanic and effectively breaks the synchronisation.

You must update data using the views (assuming this is Maximzer 9, 9.5 or 10).

The reason you are not seeing anything when you open the view is down to security. The view respects Maximizers security, so unless you connect to the SQL Server as a valid Maximizer user you will see nothing. I would suggest you connect to the SQL Server under SQL Authentication and log in as the MASTER user (make sure you enter the password all in capital letters). This will enable you to see data in the views.

Hi,

Thanks logging in using MASTER did allowed me to see in the view.

I am on Maximizer 10.0 and SQL 2005.

Now when I try to update the view running the following statement

update amgr_user_fields
set numericCol=DynamicsSalesStat.[2008]
from amgr_user_fields, amgr_client_tbl, amgr_user_field_defs_tbl, DynamicsSalesStat
where amgr_user_fields.client_id=amgr_client_tbl.client_id and (amgr_user_field_defs_tbl.type_id=amgr_user_fields.type_id and amgr_user_fields.code_id='0') and amgr_client_tbl.contact_number='0' and DynamicsSalesStat.customer=amgr_client_tbl.department and amgr_user_fields.type_id='10'

I am getting the following error message

UPDATE is not allowed because the statement updates view "amgr_user_fields" which participates in a join and has an INSTEAD OF UPDATE trigger


Thanks for your help!

Andre,

I can offer you one further tip before we go beyond what we offer for free and straight into the realms of paid consultancy.

If you are trying to update a UDF (User Defined Field) in Maximizer then you need to use the view specific to that UDF. Each UDF has its own view and its through that view they can be updated.

I would suggest you log into the Maximizer Administrator, make sure you are logged into the appropriate address book and then run the Address Book Verification (choose the validate and recover option) as this will ensure that a view exists for each UDF.

Hi,

The view did exist (much easier to work with) but I am still getting the same error message.

update A_2008
set A_2008=DynamicsSalesStat.[2008]
from A_2008, amgr_client_tbl, amgr_user_field_defs_tbl, DynamicsSalesStat
where A_2008.client_id=amgr_client_tbl.client_id and DynamicsSalesStat.customer=amgr_client_tbl.department


UPDATE is not allowed because the statement updates view "A_2008" which participates in a join and has an INSTEAD OF UPDATE trigger.

Thanks

What happens when you try missing out the A_2008 in the from clause as you have already specified it?


update A_2008
set A_2008=DynamicsSalesStat.[2008]
from
update A_2008
set A_2008=DynamicsSalesStat.[2008]
from amgr_client_tbl, amgr_user_field_defs_tbl, DynamicsSalesStat
where A_2008.client_id=amgr_client_tbl.client_id and DynamicsSalesStat.customer=amgr_client_tbl.department

Same thing

Thanks

The error message is telling you what the problem is. The A_2008 view has an UPDATE trigger (which updates the MaxExchange transaction log) and that prevents it from being UPDATEd when it is involved in a join. You'll probably need to use some looping code similar to the following

DECLARE @Client_Id varchar(24)
DECLARE @Contact_Number int
DECLARE @sales int
DECLARE UpdateList CURSOR FORWARD_ONLY FOR 
    SELECT c.Client_Id, c.Contact_Number, s.[2008] AS sales
      FROM AMGR_Client c INNER JOIN DynamicsSalesStat s
        ON c.Department=s.customer
OPEN UpdateList
FETCH UpdateList INTO @Client_Id, @Contact_Number, @sales
WHILE @@Fetch_Status = 0 BEGIN
  UPDATE A_2008 SET A_2008=@sales
    WHERE Client_Id=@Client_Id 
      AND Contact_Number=@Contact_Number
  FETCH UpdateList INTO @Client_Id, @Contact_Number, @sales
END
CLOSE UpdateList
DEALLOCATE UpdateList

Hi,

I am going on vacation for the next two weeks. I will try that when I get back.

Thanks for your time and help!

It work!

Thanks a million!