Assistance Requested OpenQuery to Oracle (linked server)

Copper Contributor

Good Evening Community, 

 

I am working on a project where I need to update Oracle Tables via a linked server on SQL Server.  I must use OpenQuery and I need that query to be parameterized.  Based on this, it is my understanding that I need to build my statement and execute it.  

 

In the end I will need to update 0 - N records in any given process (approximately 10 processes/tables) each done one table at a time.

 

during my initial test (using a straight OpenQuery) I came up with this update statement (it works): 

 

declare @procdate datetime ='2261-12-29 00:00:01.000'

UPDATE OPENQUERY ([<myserver>], 'SELECT FamilyName, MiddleName, GivenName, RelationshipStatus, RelationshipToOrganization, UpdatedDate

FROM HREmployee_wConversion WHERE EMPLOYEENUMBER = 00001')

SET GivenName = (select GivenName from #UpdatesNeeded where employeenumber = 00001)

, FamilyName = (select FamilyName from #UpdatesNeeded where employeenumber = 00001)

, MiddleName =  (select MiddleName from #UpdatesNeeded where employeenumber = 00001)

, RelationshipStatus = (select RelationshipStatus from #UpdatesNeeded where employeenumber = 00001 )

, RelationshipToOrganization = (select RelationshipToOrganization from #UpdatesNeeded where employeenumber = 00001)

, UpdatedDate= @ProcDate;

 

You can see that I am changing record fields for a specific employee and setting the updatedate (in oracle a timestamp data type) . 

 

Now my real purpose is to loop (while) through a set of records and update 1-N.  So I have built this code (the looping is working just as I expect): 

 

DECLARE @EmpNum_internal VARCHAR(10)

SET @EmpNum_Internal = (SELECT TOP 1 EmpNumber FROM @EmpNum) --table varible defined earlier

DECLARE @OpenQuery VARCHAR(2000),

@LinkedServer VARCHAR(10),

@TSql VARCHAR(2000)

 

SET @LinkedServer = '[SYNCDEV]'

SET @OpenQuery = 'UPDATE OPENQUERY(' + @LinkedServer + ','

 

SET @TSql = '''SELECT FamilyName, MiddleName, GivenName, RelationshipStatus, RelationshipToOrganization, UpdatedDate

FROM HREmployee_wConversion WHERE EMPLOYEENUMBER ='  + @EmpNum_Internal + ''') 

  SET GivenName = (select GivenName from #UpdatesNeeded where employeenumber = ' + @EmpNum_internal + ')

, FamilyName = (select FamilyName from #UpdatesNeeded where employeenumber = '+ @EmpNum_internal + ')

, MiddleName =  (select MiddleName from #UpdatesNeeded where employeenumber =' + @EmpNum_internal + ')

, RelationshipStatus = (select RelationshipStatus from #UpdatesNeeded where employeenumber = '+ @EmpNum_internal + ')

, RelationshipToOrganization = (select RelationshipToOrganization from #UpdatesNeeded where employeenumber = '+ @EmpNum_internal +')

    , UpdateDate = ' + @ProcDate +';'

 

 

EXEC (@OpenQuery+@TSql)

 

with this I am getting the following error:

 

Msg 242, Level 16, State 3, Line 70

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

 

(2 rows affected)

entering loop reverse iteration = 2

Msg 241, Level 16, State 1, Line 106

Conversion failed when converting date and/or time from character string.

 

 

Based on reviews of the the actual string produced, the results of this (second) code is the same as the first and I would expect it to execute without issue.  I do not have any recent experience with open query and and only some Oracle.  I am sure that I am missing something simple here but I cannot see it.  

 

Any guidance members of the community can provide is greatly appreciated.  

 

Thank you 

 

MT

1 Reply

I forgot to add that the date parameter used is (sql) defined in both instances as follows: 

 

working version:

declare @procdate datetime = '2261-12-29 00:00:01.000'

 

non-working version: 

DECLARE @procdate datetime

set @procdate = '2061-12-29 00:00:01.000'

 

Thank you again. 

 

MT

 

The crazy date is for testing purposes. 

 

Thank you. 

 

MT