July 13, 2009 at 9:33 am
So I am extracting about 5 Million rows of data for sales and am using the "Results to File" option to dump to a .rpt file that will be used to load into an Oracle DB. The odd thing that is happening is that there is a special character at the end of each row that can only be seen if I use Unix to look at the data. It's the same type of character if I were to use a CRLF command, but I don't have one in my SQL statement. So what I have to end up doing is stripping the character using unix commands and then re-saving the file. This is obviously not the most efficient process so I was wondering if there is something in SQL2005 that I can do so the character does not show up in my results. Thanks!
Tim
July 13, 2009 at 9:43 am
Do you mean you are using SSMS to save the data?
This produces a text file and the CR/LF should be there.
July 13, 2009 at 9:59 am
Yes, I am using SSMS to get the results. I thought about doing it with SSIS once I get it working. I used the following code to put in the CR/LF:
DECLARE @CRLF char(2)
SET @CRLF = CHAR(13) + char(10)
So teh results are similar in nature but for whatever reason, the files cannot be loaded into the Oracle DB. I figured you would want the character there as well in order to identify the end of each row, but Oracle is bombing out for some reason. When I strip the character, all is well with the world. For those wondering why we have SQL Server AND Oracle, basically we use SQL 2005 here but are implementing a software package by Oracle.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply