Posted by klimenkor@gmail.com on 04/09/07 19:09

On Apr 9, 7:27 am, "Designing Solutions WD"
<michael.grass...@gmail.com> wrote:
> 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

[Back to original message]