Thursday, April 11, 2013

could not UPDATE table "[MSDASQL]"



--update any record
UPDATE ic SET
                        Display_Name__c =ISNULL(U.Display_Name__c ,'')
                        ,Display_order__C=ISNULL(U.Display_order__C ,0)
                        ,is_Active__c=U.IsActive__C
                        ,Name=ISNULL(U.Name ,'')
                        ,Parent__C=ISNULL(U.Parent__C,'')  
                        ,cost__C=ISNULL(U.cost__C,0)
                        
--DELETE FROM ic
--Select *
FROM
OPENQUERY(LINKEDSERVER,'Select
                        Id
                        ,Display_Name__c
                        ,Display_order__C
                        ,is_Active__C
                        ,Name
                        ,Parent__C 
                        ,cost__C
                        
             from
                  item
                  ') ic
INNER JOIN #UPDATES U
 ON ic.id=U.Id


OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" returned message "SetPos update return error".
Msg 7343, Level 16, State 4, Line 296
The OLE DB provider "MSDASQL" for linked server "LINKEDSERVER" could not UPDATE table "[MSDASQL]".


And I get following error with as is_Active__C field in PostGres is boolean and OpenQuery unable to update , once remove this column and query works fine.

other alternative is delete this record and insert the whole record again.

DELETE Query:
DELETE FROM ic
--Select *FROMOPENQUERY(LINKEDSERVER,'Select
                        Id                        ,Display_Name__c                        ,Display_order__C                        ,is_Active__C                        ,Name                        ,Parent__C                         ,cost__C                                     from                  item                  ') ic
INNER JOIN #UPDATES U
 ON ic.id=U.Id


INSERT Query:

            INSERT INTO OPENQUERY(BUYBACK_STG,'Select
                        Id
                        ,Display_Name__c
                        ,Display_order__C
                        ,is_Active__c
                        ,Name
                        ,Parent__C 
                        ,cost__C
             from
                  item
                  ')
SELECT
                        Id
                        ,ISNULL(Display_Name__c ,'')
                        ,ISNULL(Display_order__C ,0)
                        ,IsActive__C
                        ,ISNULL(Name,'')
                        ,ISNULL(Parent__C,'')  
                        ,ISNULL(cost__C,0)
                        FROM #UPDATES 


No comments: