December 20, 2011 at 9:23 am
I'm trying to update a linked MySQL database with records from a MSSQL 05 database.
Here is my code:
DBCC TRACEON(8765)
USE CommerceCenter
DECLARE @cstmr_id INT
DECLARE cstmr_id_cursor CURSOR FOR (SELECT cc_shipto_id_c FROM OPENQUERY(SUGAR, 'SELECT * FROM bitnami_sugarcrm.accounts_cstm'))
OPEN cstmr_id_cursor
FETCH NEXT FROM cstmr_id_cursor INTO @cstmr_id
WHILE @@FETCH_STATUS = 0
BEGIN
-- Selects the table from linked server to update
INSERT INTO OPENQUERY(SUGAR, 'SELECT * FROM bitnami_sugarcrm.accounts_cstm') (cc_class1_c, cc_class2_c, cc_class3_c, cc_sales_pot_c)
-- Updates linked server table with data from _PJ_SUGARCRM_LINK view where customer id's match
SELECT class1, class2, class3, class_1id FROM _PJ_SUGARCRM_LINK WHERE customer_id=@cstmr_id
-- Sets the prospect_c field to 1 if customer has not ordered in last two years
UPDATE OPENQUERY(SUGAR, 'SELECT * FROM bitnami_sugarcrm.accounts_cstm') SET prospect_c=1 WHERE _PJ_SUGARCRM_LINK.last_order_months>=24
FETCH NEXT FROM cstmr_id_cursor INTO @cstmr_id
END
CLOSE cstmr_id_cursor
DEALLOCATE cstmr_id_cursor
I've made bold the area that is giving me issues. When I run that query I get "The multi-part identifier "_PJ_SUGARCRM_LINK.last_order_months" could not be bound."
If I take out the reference to the view leaving just "last_order_months>=24" I get invalid column. If I add "dbo." I get the same "could not be bound" error.
Could this be an issue with how I'm using OPENQUERY to retrieve results from the linked database? Any help would be great. If I left any important info out just ask!
December 21, 2011 at 10:40 am
Sorry, but this update statement:
UPDATE OPENQUERY(SUGAR, 'SELECT * FROM bitnami_sugarcrm.accounts_cstm')
SET prospect_c=1 WHERE _PJ_SUGARCRM_LINK.last_order_months>=24
IMO looks a bit confisung, I mean what this _PJ_SUGARCRM_LINK.last_order_months is about?
December 21, 2011 at 10:49 am
Yuri55 (12/21/2011)
Sorry, but this update statement:UPDATE OPENQUERY(SUGAR, 'SELECT * FROM bitnami_sugarcrm.accounts_cstm')
SET prospect_c=1 WHERE _PJ_SUGARCRM_LINK.last_order_months>=24
IMO looks a bit confisung, I mean what this _PJ_SUGARCRM_LINK.last_order_months is about?
I think this is actually the proper way to accomplish that:
UPDATE remtable
SET prospect_c = 1
FROM OPENQUERY(SUGAR, 'SELECT * FROM bitnami_sugarcrm.accounts_cstm') remtable
WHERE EXISTS
(SELECT *
FROM _PJ_SUGARCRM_LINK PJ
WHERE PJ.customer_id=@cstmr_id
AND PJ.last_order_months>=24)
_PJ_SUGARCRM_LINK is a view on the local database.
last_order_months is a column in that view that holds an INT (months since last order).
I'm trying to say if months since last order is over 24 then set prospect_c (a column in the linked database table) to 1.
I'm using the "WHERE PJ.customer_id=@cstmr_id" to match the customer ID on the local table to the customer ID on the linked table.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply