Jun 13 2019 06:40 PM
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
Jun 13 2019 06:49 PM
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