Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
pixelwiz
Starting Member
25 Posts |
 Posted - 2009-04-06 : 14:28:24
|
| We've ran into a little problem with MS SQL Server 2005 and XML, and spent days looking for a solution on the web.I'll try to make this as brief as possible. We have to move about 10000 records from Oracle DB into MS SQL (nightly). Our Oracle guys wrote a web service that we can access through ColdFusion and it sends back an XML file. That part works really fast (seconds). The file is about 4MB. The ColdFusion then feeds the file to a SQL Server Stored Procedure (I'll put code below) which is pretty simple and works great on small record sets. I've loaded 500 records with it easily. But if we try the full 10k, the SQL Server CPU goes to 100% and never stops. And that's a powerful server with 4GB of memory.We've even eliminated the first step and just had the file read by coldfusion from disk and passed to MS SQL. The thing is we can't save it locally to the SQL Server (I guess for security reasons).So after much searching now I'm trying to figure out if it's possible to get ColdFusion to work with the SQLXMLBulkLoad object.Below is the CF code and the original Stored Procedure code (some parts are commented out as we've been experimenting with pieces of it.Thanks for any help,-Roman============MS SQL PARTALTER PROCEDURE [dbo].[sp_insertOracleFacs] (@xml XML)BEGINDELETE FROM temp_Oracle_Facility_InfoINSERT INTO temp_Oracle_Facility_Info (facility_id,company_nm,section_id,section_nm,addr1,addr2,addr3,addr4,city,[state],postal)SELECTdoc.col.value('facility_id[1]', 'varchar(10)'),doc.col.value('company_nm[1]', 'varchar(200)'),doc.col.value('section_id[1]', 'varchar(3)'),doc.col.value('section_nm[1]', 'varchar(100)'),doc.col.value('addr1[1]', 'varchar(100)'),doc.col.value('addr2[1]', 'varchar(100)'),doc.col.value('addr3[1]', 'varchar(100)'),doc.col.value('addr4[1]', 'varchar(100)'),doc.col.value('city[1]', 'varchar(100)'),doc.col.value('state[1]', 'varchar(3)'),doc.col.value('postal[1]', 'varchar(5)')FROM @xml.nodes('//FacilityInfo') AS doc(col)END===========COLDFUSION PART<!--- Make HTTP Request ---><!---Send HTTP Request<br /><cfflush><cfhttp url="#XMLURL#" username="#myUsername#" password="#myPassword#"></cfhttp> ---><!--- <cfdump var="#cfhttp.FileContent#"><cfabort> ---><!--- Create the XML Object ---><!--- Create XML<br /><cfflush><cfset xmlData = XMLParse(cfhttp.FileContent)> ---><!--- <cffile action="write" file="#getDirectoryFromPath(getTemplatePath())#Ora cleFacilities.xml" output="#xmlData#">Done writing file<cfabort> ---><cffile action="read" file="#getDirectoryFromPath(getTemplatePath())#Ora cleFacilities.xml" variable="xmlData">Done Reading File<br /><!--- <cfdump var="#xmlData#"><cfabort> ---><!--- Run Stored Procedure<br /><cfflush><cfstoredproc datasource="#SQL_DB#" procedure="sp_insertOracleFacs" result="return"><cfprocparam type="in" cfsqltype="cf_sql_xml" value="#xmlData#"></cfstoredproc> ---> |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-04-06 : 17:28:43
|
| why xml? why all the hoop and passing xml. can you not from oracles insert directly into a sql table, a sort of staging table and then work with it in there. staging is just in case something fails it fails on the staging table.<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
 |
|
|
pixelwiz
Starting Member
25 Posts |
Posted - 2009-04-08 : 09:05:38
|
| We currently do not have a way for MS SQL to directly connect to Oracle DB. So we are going the XML route as a work-around.We have actually found a solution. Doing it as below did work for 10k rows in just a few seconds. <cfquery name="insertFacilities" datasource="#mydb#"> DECLARE @hDoc int EXEC sp_xml_preparedocument @hDoc output, '#cfhttp.FileContent#' DELETE FROM temp_Oracle_Facility_Info INSERT temp_Oracle_Facility_Info ( facility_id, company_nm, section_id, section_nm, addr1, addr2, addr3, addr4, city, [state], postal) SELECT facility_id, company_nm, section_id, section_nm, addr1, addr2, addr3, addr4, city, [state], postal FROM OPENXML(@hDoc, '/OracleFacilities/FacilityInfo', 1) WITH ( facility_id varchar(10) 'facility_id', company_nm varchar(200) 'company_nm', section_id varchar(3) 'section_id', section_nm varchar(100) 'section_nm', addr1 varchar(100) 'addr1', addr2 varchar(100) 'addr2', addr3 varchar(100) 'addr3', addr4 varchar(100) 'addr4', city varchar(100) 'city', state varchar(3) 'state', postal varchar(5) 'postal' ) EXEC sp_xml_removedocument @hDoc </cfquery> |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2009-04-08 : 10:43:54
|
| glad it worked . But can you not connect to oracle database via linked server? I ahev seen many examples here, if it is worth the try.<><><><><><><><><><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
|
|
|
|
|
Load Large XML file into SQL 2005 SQLXMLBulkLoad?