Home

Assistance Requested OpenQuery to Oracle (linked server)

%3CLINGO-SUB%20id%3D%22lingo-sub-691562%22%20slang%3D%22en-US%22%3EAssistance%20Requested%20OpenQuery%20to%20Oracle%20(linked%20server)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-691562%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20Evening%20Community%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20working%20on%20a%20project%20where%20I%20need%20to%20update%20Oracle%20Tables%20via%20a%20linked%20server%20on%20SQL%20Server.%26nbsp%3B%20I%20must%20use%20OpenQuery%20and%20I%20need%20that%20query%20to%20be%20parameterized.%26nbsp%3B%20Based%20on%20this%2C%20it%20is%20my%20understanding%20that%20I%20need%20to%20build%20my%20statement%20and%20execute%20it.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20end%20I%20will%20need%20to%20update%200%20-%20N%20records%20in%20any%20given%20process%20(approximately%2010%20processes%2Ftables)%20each%20done%20one%20table%20at%20a%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eduring%20my%20initial%20test%20(using%20a%20straight%20OpenQuery)%20I%20came%20up%20with%20this%20update%20statement%20(it%20works)%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22s2%22%3Edeclare%3C%2FSPAN%3E%3CSPAN%20class%3D%22s1%22%3E%20%40procdate%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3Edatetime%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s3%22%3E%3D%3C%2FSPAN%3E'2261-12-29%2000%3A00%3A01.000'%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22s4%22%3EUPDATE%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s2%22%3EOPENQUERY%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s3%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s1%22%3E%5B%3CMYSERVER%3E%5D%3C%2FMYSERVER%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22s3%22%3E%2C%3C%2FSPAN%3E%20'SELECT%20FamilyName%2C%20MiddleName%2C%20GivenName%2C%20RelationshipStatus%2C%20RelationshipToOrganization%2C%20UpdatedDate%3C%2FP%3E%3CP%20class%3D%22p1%22%3EFROM%20HREmployee_wConversion%20WHERE%20EMPLOYEENUMBER%20%3D%2000001'%3CSPAN%20class%3D%22s3%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%3CSPAN%20class%3D%22s2%22%3ESET%3C%2FSPAN%3E%20GivenName%20%3CSPAN%20class%3D%22s3%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s3%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3Eselect%3C%2FSPAN%3E%20GivenName%20%3CSPAN%20class%3D%22s2%22%3Efrom%3C%2FSPAN%3E%20%23UpdatesNeeded%20%3CSPAN%20class%3D%22s2%22%3Ewhere%3C%2FSPAN%3E%20employeenumber%20%3CSPAN%20class%3D%22s3%22%3E%3D%3C%2FSPAN%3E%2000001%3CSPAN%20class%3D%22s3%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%3CSPAN%20class%3D%22s3%22%3E%2C%3C%2FSPAN%3E%20FamilyName%20%3CSPAN%20class%3D%22s3%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s3%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3Eselect%3C%2FSPAN%3E%20FamilyName%20%3CSPAN%20class%3D%22s2%22%3Efrom%3C%2FSPAN%3E%20%23UpdatesNeeded%20%3CSPAN%20class%3D%22s2%22%3Ewhere%3C%2FSPAN%3E%20employeenumber%20%3CSPAN%20class%3D%22s3%22%3E%3D%3C%2FSPAN%3E%2000001%3CSPAN%20class%3D%22s3%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%3CSPAN%20class%3D%22s3%22%3E%2C%3C%2FSPAN%3E%20MiddleName%20%3CSPAN%20class%3D%22s3%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22s3%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3Eselect%3C%2FSPAN%3E%20MiddleName%20%3CSPAN%20class%3D%22s2%22%3Efrom%3C%2FSPAN%3E%20%23UpdatesNeeded%20%3CSPAN%20class%3D%22s2%22%3Ewhere%3C%2FSPAN%3E%20employeenumber%20%3CSPAN%20class%3D%22s3%22%3E%3D%3C%2FSPAN%3E%2000001%3CSPAN%20class%3D%22s3%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%3CSPAN%20class%3D%22s3%22%3E%2C%3C%2FSPAN%3E%20RelationshipStatus%20%3CSPAN%20class%3D%22s3%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s3%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3Eselect%3C%2FSPAN%3E%20RelationshipStatus%20%3CSPAN%20class%3D%22s2%22%3Efrom%3C%2FSPAN%3E%20%23UpdatesNeeded%20%3CSPAN%20class%3D%22s2%22%3Ewhere%3C%2FSPAN%3E%20employeenumber%20%3CSPAN%20class%3D%22s3%22%3E%3D%3C%2FSPAN%3E%2000001%20%3CSPAN%20class%3D%22s3%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%3CSPAN%20class%3D%22s3%22%3E%2C%3C%2FSPAN%3E%20RelationshipToOrganization%20%3CSPAN%20class%3D%22s3%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s3%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3Eselect%3C%2FSPAN%3E%20RelationshipToOrganization%20%3CSPAN%20class%3D%22s2%22%3Efrom%3C%2FSPAN%3E%20%23UpdatesNeeded%20%3CSPAN%20class%3D%22s2%22%3Ewhere%3C%2FSPAN%3E%20employeenumber%20%3CSPAN%20class%3D%22s3%22%3E%3D%3C%2FSPAN%3E%2000001%3CSPAN%20class%3D%22s3%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%3CSPAN%20class%3D%22s3%22%3E%2C%3C%2FSPAN%3E%20UpdatedDate%3CSPAN%20class%3D%22s3%22%3E%3D%3C%2FSPAN%3E%20%40ProcDate%3CSPAN%20class%3D%22s3%22%3E%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%3CSPAN%20class%3D%22s3%22%3EYou%20can%20see%20that%20I%20am%20changing%20record%20fields%20for%20a%20specific%20employee%20and%20setting%20the%20updatedate%20(in%20oracle%20a%20timestamp%20data%20type)%20.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%3CSPAN%20class%3D%22s3%22%3ENow%20my%20real%20purpose%20is%20to%20loop%20(while)%20through%20a%20set%20of%20records%20and%20update%201-N.%26nbsp%3B%20So%20I%20have%20built%20this%20code%20(the%20looping%20is%20working%20just%20as%20I%20expect)%3A%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22s1%22%3EDECLARE%3C%2FSPAN%3E%20%40EmpNum_internal%20%3CSPAN%20class%3D%22s1%22%3EVARCHAR%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E(%3C%2FSPAN%3E10%3CSPAN%20class%3D%22s2%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22s1%22%3ESET%3C%2FSPAN%3E%20%40EmpNum_Internal%20%3CSPAN%20class%3D%22s2%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s2%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22s1%22%3ESELECT%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s1%22%3ETOP%3C%2FSPAN%3E%201%20EmpNumber%20%3CSPAN%20class%3D%22s1%22%3EFROM%3C%2FSPAN%3E%20%40EmpNum%3CSPAN%20class%3D%22s2%22%3E)%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s3%22%3E--table%20varible%20defined%20earlier%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22s1%22%3EDECLARE%3C%2FSPAN%3E%20%40OpenQuery%20%3CSPAN%20class%3D%22s1%22%3EVARCHAR%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E(%3C%2FSPAN%3E2000%3CSPAN%20class%3D%22s2%22%3E)%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%40LinkedServer%20%3CSPAN%20class%3D%22s1%22%3EVARCHAR%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E(%3C%2FSPAN%3E10%3CSPAN%20class%3D%22s2%22%3E)%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%40TSql%20%3CSPAN%20class%3D%22s1%22%3EVARCHAR%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E(%3C%2FSPAN%3E2000%3CSPAN%20class%3D%22s2%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22s1%22%3ESET%3C%2FSPAN%3E%20%40LinkedServer%20%3CSPAN%20class%3D%22s2%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s4%22%3E'%5BSYNCDEV%5D'%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22s1%22%3ESET%3C%2FSPAN%3E%20%40OpenQuery%20%3CSPAN%20class%3D%22s2%22%3E%3D%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s4%22%3E'UPDATE%20OPENQUERY('%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E%20%40LinkedServer%20%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22s4%22%3E'%2C'%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p3%22%3E%3CSPAN%20class%3D%22s1%22%3ESET%3C%2FSPAN%3E%3CSPAN%20class%3D%22s5%22%3E%20%40TSql%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%3D%3C%2FSPAN%3E%20'''SELECT%20FamilyName%2C%20MiddleName%2C%20GivenName%2C%20RelationshipStatus%2C%20RelationshipToOrganization%2C%20UpdatedDate%3C%2FP%3E%3CP%20class%3D%22p3%22%3EFROM%20HREmployee_wConversion%20WHERE%20EMPLOYEENUMBER%20%3D'%3CSPAN%20class%3D%22s5%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E%3CSPAN%20class%3D%22s5%22%3E%20%40EmpNum_Internal%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E%20''')%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p3%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%3C%2FSPAN%3ESET%20GivenName%20%3D%20(select%20GivenName%20from%20%23UpdatesNeeded%20where%20employeenumber%20%3D%20'%20%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E%3CSPAN%20class%3D%22s5%22%3E%20%40EmpNum_internal%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E%20')%3C%2FP%3E%3CP%20class%3D%22p3%22%3E%2C%20FamilyName%20%3D%20(select%20FamilyName%20from%20%23UpdatesNeeded%20where%20employeenumber%20%3D%20'%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E%3CSPAN%20class%3D%22s5%22%3E%20%40EmpNum_internal%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E%20')%3C%2FP%3E%3CP%20class%3D%22p3%22%3E%2C%20MiddleName%20%3D%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%3C%2FSPAN%3E(select%20MiddleName%20from%20%23UpdatesNeeded%20where%20employeenumber%20%3D'%20%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E%3CSPAN%20class%3D%22s5%22%3E%20%40EmpNum_internal%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E%20')%3C%2FP%3E%3CP%20class%3D%22p3%22%3E%2C%20RelationshipStatus%20%3D%20(select%20RelationshipStatus%20from%20%23UpdatesNeeded%20where%20employeenumber%20%3D%20'%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E%3CSPAN%20class%3D%22s5%22%3E%20%40EmpNum_internal%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E%20')%3C%2FP%3E%3CP%20class%3D%22p3%22%3E%2C%20RelationshipToOrganization%20%3D%20(select%20RelationshipToOrganization%20from%20%23UpdatesNeeded%20where%20employeenumber%20%3D%20'%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E%3CSPAN%20class%3D%22s5%22%3E%20%40EmpNum_internal%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E')%3C%2FP%3E%3CP%20class%3D%22p3%22%3E%3CSPAN%20class%3D%22Apple-converted-space%22%3E%26nbsp%3B%20%26nbsp%3B%20%3C%2FSPAN%3E%2C%20UpdateDate%20%3D%20'%20%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E%3CSPAN%20class%3D%22s5%22%3E%20%40ProcDate%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E'%3B'%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22s1%22%3EEXEC%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s2%22%3E(%3C%2FSPAN%3E%40OpenQuery%3CSPAN%20class%3D%22s2%22%3E%2B%3C%2FSPAN%3E%40TSql%3CSPAN%20class%3D%22s2%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%3CSPAN%20class%3D%22s2%22%3Ewith%20this%20I%20am%20getting%20the%20following%20error%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3EMsg%20242%2C%20Level%2016%2C%20State%203%2C%20Line%2070%3C%2FP%3E%3CP%20class%3D%22p1%22%3EThe%20conversion%20of%20a%20varchar%20data%20type%20to%20a%20datetime%20data%20type%20resulted%20in%20an%20out-of-range%20value.%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E(2%20rows%20affected)%3C%2FP%3E%3CP%20class%3D%22p1%22%3Eentering%20loop%20reverse%20iteration%20%3D%202%3C%2FP%3E%3CP%20class%3D%22p1%22%3EMsg%20241%2C%20Level%2016%2C%20State%201%2C%20Line%20106%3C%2FP%3E%3CP%20class%3D%22p1%22%3EConversion%20failed%20when%20converting%20date%20and%2For%20time%20from%20character%20string.%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3EBased%20on%20reviews%20of%20the%20the%20actual%20string%20produced%2C%20the%20results%20of%20this%20(second)%20code%20is%20the%20same%20as%20the%20first%20and%20I%20would%20expect%20it%20to%20execute%20without%20issue.%26nbsp%3B%20I%20do%20not%20have%20any%20recent%20experience%20with%20open%20query%20and%20and%20only%20some%20Oracle.%26nbsp%3B%20I%20am%20sure%20that%20I%20am%20missing%20something%20simple%20here%20but%20I%20cannot%20see%20it.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3EAny%20guidance%20members%20of%20the%20community%20can%20provide%20is%20greatly%20appreciated.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3EThank%20you%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3EMT%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-691571%22%20slang%3D%22en-US%22%3ERe%3A%20Assistance%20Requested%20OpenQuery%20to%20Oracle%20(linked%20server)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-691571%22%20slang%3D%22en-US%22%3E%3CP%3EI%20forgot%20to%20add%20that%20the%20date%20parameter%20used%20is%20(sql)%20defined%20in%20both%20instances%20as%20follows%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3Eworking%20version%3A%3C%2FP%3E%3CP%20class%3D%22p1%22%3Edeclare%20%40procdate%20datetime%20%3D%20'2261-12-29%2000%3A00%3A01.000'%3C%2FP%3E%3CP%20class%3D%22p1%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3Enon-working%20version%3A%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p1%22%3EDECLARE%3CSPAN%20class%3D%22s1%22%3E%20%40procdate%20%3C%2FSPAN%3Edatetime%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%3CSPAN%20class%3D%22s2%22%3Eset%3C%2FSPAN%3E%3CSPAN%20class%3D%22s1%22%3E%20%40procdate%20%3C%2FSPAN%3E%3CSPAN%20class%3D%22s3%22%3E%3D%3C%2FSPAN%3E%20'2061-12-29%2000%3A00%3A01.000'%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p2%22%3EThank%20you%20again.%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p2%22%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22p2%22%3EMT%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThe%20crazy%20date%20is%20for%20testing%20purposes.%26nbsp%3B%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThank%20you.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EMT%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
MTognetti
New 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

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies