Best way to export data.

[Designing Solutions WD]

Hello,

I have some questions on my options available.

I have to export some tables to csv files to enable another department
to process the files. What I need is a way to do this in ms sql
though a stored proc with quoted identifiers and column names as
heads. I cannot figure out how to do this.

Can anybody give me some options that would be the best options.

I am using ms sql 2000.

Thank you for your time.

[klimenkor]

On Apr 9, 7:27 am, "Designing Solutions WD"
<[e-mail]:

Hello,

I have some questions on my options available.

I have to export some tables to csv files to enable another department
to process the files. What I need is a way to do this in ms sql
though a stored proc with quoted identifiers and column names as
heads. I cannot figure out how to do this.

Can anybody give me some options that would be the best options.

I am using ms sql 2000.

Thank you for your time.

The easiest solution that came to my head is to execute DTS package in
command shell. In DTS package you can define whatever format you want.
Create it. Debug it. Play with it. Then just add xp_cmdshell
'dtsrun.exe -S<server-N<dts-package-E -M<dts-password>' to your
procedure.

- Roman

[klimenkor]

On Apr 9, 7:27 am, "Designing Solutions WD"
<[e-mail]:

Hello,

I have some questions on my options available.

I have to export some tables to csv files to enable another department
to process the files. What I need is a way to do this in ms sql
though a stored proc with quoted identifiers and column names as
heads. I cannot figure out how to do this.

Can anybody give me some options that would be the best options.

I am using ms sql 2000.

Thank you for your time.

Straight forward solution is to UNION field names with data and use
BCP -

1. Create a SELECT statement that includes field names -
DECLARE @names varchar(100), @delimiter varchar(10)
SET @delimiter = ','
SELECT @names = COALESCE(@names + @delimiter, '') + '"' + name + '"'
FROM syscolumns where id = (select id from sysobjects where
name='TABLE_TO_EXPORT')

SELECT 'select ' + @names

2. Concatenate it with UNION SELECT cast(FIELD1 as char), cast(FIELD2
as char), .... From TABLE_TO_EXPORT (which is ugly but it has to be
done to create union)

3. Then using UNION create a VIEW which can be used in BCP to export
data

4. Use BCP from command shell xp_cmdshell "BCP ""select * from
VIEW_TO_EXPORT"" out c:\results.csv -c -t, -T -S<servername>

- Roman