March 23, 2008 at 12:56 pm
I have to do data export from sqlserver2000 that is to be used by mainframe guys. They said they need unix files and gave me cmd for bcp export. I am having problems using this bcp. How can I do it using Export Wizard... What should I use for col delimeter, row delimeter, text delimeter or should it be fixed field?
Script was something like this:
EXECUTE master..xp_cmdshell 'bcp "SELECT * FROM inputs.dbo.mytable" queryout "\\ABC\MS\myexport.txt" -c -t "" -f "\\ABC\MS\Format\ff1.fmt" -T -S ABC
March 23, 2008 at 2:11 pm
Ghanta (3/23/2008)
EXECUTE master..xp_cmdshell 'bcp "SELECT * FROM inputs.dbo.mytable"
queryout "\\ABC\MS\myexport.txt" -c -t ""
-f "\\ABC\MS\Format\ff1.fmt" -T -S ABC
At first glance, this looks OK to me. What is the problem you are having with it? Also, please show us your table DDL and the contents of the format file.
Check out the following link for tips on how to get better results from your questions: http://qa.sqlservercentral.com/articles/Best+Practices/61537/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 23, 2008 at 3:52 pm
Found the problem.... the problem was with the ODBC driver. I have to move this table to dev server and do that that export. I have a table with like 25 columns and 77 million records... what is the fastest way to move this table to a dev server? These servers are not linked so cannot to select into.... thanks for the response.
March 23, 2008 at 4:56 pm
Since I cannot use the above BCP command in that server. What do you suggest? Should move the table to another server and do that or should I export using dts export... if dts, what should I use for delimeter to have it in unix format. Thanks!
March 23, 2008 at 7:20 pm
there is always the old standby
select 'insert into table values ( '+ isnull( '''' + stringscol + '''', 'null') +
', ' + isnull( cast( intcol as varchar(8)),'null') + ')'
from table
of course with 77 million records you probably need to break it up a bit...
the only other real choice would be to create some massive XML files.
March 24, 2008 at 12:00 am
Ghanta (3/23/2008)
Found the problem.... the problem was with the ODBC driver. I have to move this table to dev server and do that that export. I have a table with like 25 columns and 77 million records... what is the fastest way to move this table to a dev server? These servers are not linked so cannot to select into.... thanks for the response.
We've successfully used bcp to move millions of records between servers when we don't want to jsut do a backup and restore. Here's a sample pair of commands that we use:
bcp TableName out DriveName:\Folders\table.out -T -S FromServerName -n
bcp TableName in DriveName:\Folders\table.out -T -S ToServerName -n -E -h "TABLOCK"
March 24, 2008 at 6:42 am
Can you please elaborate the target platform - is it Unix or Mainframe? When files are created on Windows, the default record delimiter is CR-LF. On Unix it is only LF. So while If the target is Unix, a file created on Windows will show "^M" characters at the end of each line. To prevent this, the export should use Line-Feed only as the record delimiter instead of CR+LF. Alternatively, on the target Unix system the file needs to be formatted to Unix format using the dos2unix Unix command.
March 25, 2008 at 7:29 am
If you use ftp to transfer data between a windows machine and a unix machine, it normally strips out the the Carriage Return and leaves just the Line Feed that the unix world uses as a line terminator.
You have to ask the people needing the data whether they want fixed length or delimited data. We used to use pipe (|) delimited files for exports from Informix in Unix.
Steve
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply