Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DTS to flat file for pickup by Oracle Expand / Collapse
Author
Message
Posted Friday, April 10, 2009 8:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832, Visits: 5,316
I have a table which allows for storing notes about a client. One field is ntext(8000), where the users input the history about a particular client/claim. They have CR/LF in the field, which I can handle in SQL and the application. However, when I export the data to a text file, vertical bar delimited for each field, when our Oracle DBA tries to import it, it breaks that field into multiple rows of data, breaking on the CR/LF. Now, having said all that, I know zip about Oracle and don't know how to help the Oracle DBA. I've tried ansi and unicode as an export option and that doesn't seem to matter. Lastly, we cannot setup a linked server as our version of Oracle (7.32) needs to be at 7.33, minimum, based on my attempts to setup the linked server and there are no current plans to upgrade. Any thoughts on how/what I can try to get around this?

-- You can't be late until you show up.
Post #694889
Posted Friday, April 10, 2009 9:44 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, August 29, 2014 4:49 PM
Points: 3,139, Visits: 1,259
How about using the replace() function when selecting the data to export? Here is an example.

declare @test varchar(8000)

select @test = 'test
test
test'

select @test

select replace(replace(@Test, char(10), ''), char(13), ' - ')





Post #694940
Posted Friday, April 10, 2009 9:51 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 7:31 AM
Points: 2,832, Visits: 5,316
I started to do that. The table has 660K rows of data. After about 10 or 15 minutes, I finally canceled the query. I don't deem the number of rows to be extensive but when it's searching through an 8000 ntext field maybe the run-time isn't that extreme?? And, of course, I cannot put an index on the field in question due to the data type. Maybe I'll offload that table to a development server and just let the update run however long it takes and go from there. Thanks for the reply.

-- You can't be late until you show up.
Post #694947
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse