User Profile
MTognetti
Copper Contributor
Joined 6 years ago
User Widgets
Recent Discussions
Assistance Requested OpenQuery to Oracle (linked server)
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 MT1.6KViews0likes1Comment
Groups
Recent Blog Articles
No content to show