asked on

Help needed on SQL query

Hello,

I want to update Oracle DB table from SQL server SSMS using Linked Server. Can you please tell me how to resolve below error

Error: "Invalid object [GAIAPRE_LS].CLIent"

DB link name: GAIAPRE_LS
Oracle Table name: CLIent

update T1
set T1.REF_CLIENT_EXT=T2.ojo_accountnumber
from [GAIAPRE_LS].CLIent T1 inner join CRMDB.dbo.etel_billingaccountBase T2
on T1.NCLI collate SQL_Latin1_General_CP1_CI_AS= T2.etel_externalid collate SQL_Latin1_General_CP1_CI_AS
where T2.ojo_sourceId=2 and T2.statecode=1 --and  T2.ModifiedOn  >= cast(dateadd(day, -2, getdate()) as date)

Open in new window

I am able to execute below query successfully from SQL Server without any error.
SELECT * FROM OPENQUERY([GAIAPRE_LS] , 'select * from client') ;

I am not able to execute below query due to below error.
select *
from [GAIAPRE_LS].client

Please advise how to use OPENQUERY in above update statement.

HainKurt

put a schema name before your table name, like

[GAIAPRE_LS].schema.client

or

[GAIAPRE_LS]..schema.client

srini G

ASKER

Thanks. Now I am getting another error

update T1
set T1.REF_CLIENT_EXT=T2.ojo_accountnumber
from [GAIAPRE_LS]..[EXALEAD].[CLIENT] T1 inner join CRMPROD_MSCRM.dbo.etel_billingaccountBase T2
on T1.NCLI collate SQL_Latin1_General_CP1_CI_AS= T2.etel_externalid collate SQL_Latin1_General_CP1_CI_AS
where T2.ojo_sourceId=2 and T2.statecode=1

Error:

Msg 447, Level 16, State 0, Line 8
Expression type numeric is invalid for COLLATE clause.

Please help me on above error

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial