User Profile
MTognetti
Copper Contributor
Joined Jun 13, 2019
User Widgets
Recent Discussions
Re: Assistance Requested OpenQuery to Oracle (linked server)
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. MT1.7KViews0likes0CommentsAssistance 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.7KViews0likes1Comment
Recent Blog Articles
No content to show