March 10, 2008 at 4:17 pm
Hello,
I'm trying to export some data from SQL Server 2005 to be used in Linux, specifically Redhat 7.3 and Informix database. I've been trying to use bcp to do this, but am having some problems with the way it looks in Linux (Every row is terminated by ^@ and I can't seem to figure out how to remove/change it). I've ran the query and saved the results using the "Save as, with encoding" option and selecting ANSI. This worked pretty well, but I don't know how to automate it.
I have a view I created in the SQL 05 db that I want to return all rows, but am just having the problem with the filetype/character set/code page/whatever it's called.
What are my options for automating SQL Server 2005 to export data to an ANSI encoded file?
Any help is greatly appreciated.
Thanks,
Jared
March 10, 2008 at 4:46 pm
I've never moved data to this platform...but I would imagine that SSIS would offer at least some of the functionality that you need.
Gethyn Elliswww.gethynellis.com
March 10, 2008 at 7:09 pm
The bigger question is - what kind of data do you need to get into Informix? What types of files does Informix prefers?
You can export (using SSIS, BCP, or any number of options) to a fairly wide variety of file formats. The big question is - which one is best for the data I'm sending over? Between delimiter (CSV, tab-, pipe- or any customer delimiter-delimited), fixed-width, and XML, you've got a lot of bases covered.
If ANSI is your issue - take a look at assigning a specific CodePage that works.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 10, 2008 at 7:19 pm
Thanks for the replies.
Informix will let me load using any number of delimiters. I prefer to use a 'pipe' (|) for the delimiter since Informix defaults to the 'pipe' sign.
I'm currently running the following to extract my data (I have it running in Windows Scheduler once a week):
bcp [Database].[View] out \\server\file.csv -c -t | -T
This works, but I get a ^@ at the end of every file. Usually this isn't bad since I can usually use sed in Linux to remove the end of a file, but it's not working this time.
If I were to run a "select * from [Database].[View]" and save it to a file with ANSI encoding I don't get the ^@ characters, but I don't know how to automate it - which is really what I need to do.
I've tried to use bcp and the -r \r switch, and then use dos2unix in Linux to remove the ^M (carriage return) values, but I can't seem to figure out how to get rid of the ^@ characters.
March 10, 2008 at 7:35 pm
I'd try giving SSIS a go
March 10, 2008 at 7:45 pm
if you don't like the row terminator, then add the -r (row terminator) flag to your BCP. You will need to try various ones to find which one works for you.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply