Exporting data

[GerdBrix]

Hi, I have a few legacy systems to which I need to export data from
SQL server 2000.
The export should be done every one or two hours, so there's much time
for little data ;-)

My first idea to do this was using a batch file. I found bcp.exe,
which could work - however, I have text data in the table, and this is
not quoted by bcp.exe. Parsing the resulting data is, to say the
least, difficult...

Can someone please suggest a way to do this task?

Thanks,
Gerd

[patrick61z]

On Nov 7, 10:04 am, [e-mail] wrote:

Hi, I have a few legacy systems to which I need to export data from
SQL server 2000.
The export should be done every one or two hours, so there's much time
for little data ;-)

My first idea to do this was using a batch file. I found bcp.exe,
which could work - however, I have text data in the table, and this is
not quoted by bcp.exe. Parsing the resulting data is, to say the
least, difficult...

Can someone please suggest a way to do this task?

Thanks,
Gerd

I use bcp, and point it at a view on the server (sql2005), and this
view on the server puts multiple fields with delimiters together into
one record / field, and this lets me choose my output delimiter.

like

create view EXPORT_VIEW as select NAME + '|' + ADDRESS + '|' + CITY +
'|' + STATE from CUSTOMER_TABLE

and then bcp that. This lets me use the vertical pipe as a delimiter.

At least on 2005, bcp has many options and some of these go into a
spec file, but I've been happy just doing the field delimiters in sql.

[Gerd Brix]

On 7 Nov., 20:22, [e-mail] wrote:

On Nov 7, 10:04 am, [e-mail] wrote:

[...]

>
like

create view EXPORT_VIEW as select NAME + '|' + ADDRESS + '|' + CITY +
'|' + STATE from CUSTOMER_TABLE

and then bcp that. This lets me use the vertical pipe as a delimiter.

[...]

Great! That's exactly what I needed... Thanks!

-gerd