bcp Export to CSV with headers

I’m trying to find a way to export all data from a table in to a csv file with a header. I’m not concerned at this point with dynamic headers (that would be nice but at this point I’m not picky). I would just like to find a way to programmatically drop a table to a csv file.

use tempdb

go

create view vw_csvexport

as

select

'"' + convert(varchar(50), Name) + '"',

'"' + convert(varchar(50), ID) + '"'

from TestHeaderTable

union all

SELECT top 100 percent

Name = '"' + name + '"',

ID = '"' + convert(varchar(50), ID) + '"'

FROM TestDataTable

go

DECLARE @.bcpCommand varchar(2000)

SET @.bcpCommand = 'bcp tempdb..vw_export out

c:\test002.txt -c -T -S SERVER'

EXEC master..xp_cmdshell @.bcpCommand

I get this error when I run this query

Create View or Function failed because no column name was specified for column 1.

If I’m going about this the completely wrong direction let me know so I can get heading the right direction to get this done.

Try the following...

xp_cmdshell 'osql /S<ServerName> /E /Q"set nocount on select top 10 Convert(Varchar(50),name) as name, id from sysobjects" /oc:\test.txt'

|||Hi,

For your view try this and you will be alright:

create view vw_csvexport

as

select

'"' + convert(varchar(50), Name) + '"' as vwNAME,

'"' + convert(varchar(50), ID) + '"' as vwID

from TestHeaderTable

union all

SELECT top 100 percent

Name = '"' + name + '"',

ID = '"' + convert(varchar(50), ID) + '"'

FROM TestDataTable

go

Actually in the view your returning table should always have specific column name, and since you had not specify any, you had the error.

Cheers

BCN service questions

Hi

some questions about BCN service :

- WE have several remote clients and we don’t know their addresses.

- We create a BCN Service for dynamic routes configuration.

- Each time we send a message to a specific user message is sent to BCN asking for a route

- Messages for a specific user can have specific conversation group id

( Question : if we send several message to the client with no route does it mean that there will be several messages to BCN ?.

My guess that “missing route” message is sent to BCN for every new conversation but not for several messages inside one conversation. )

- BCN service doesn’t answer immediately because it doesn’t know the address.

- There will be an event in the system ( client is connected and provide an address ) when we know his address and only then BCN will answer to the message asking for the route for this client.

( Question : if we have several messages ( probably several conversations ) asking route for a specific client does it mean we need to answer to all these messages/conversations? ).

Will this work?

Leonid.

When the service broker service begins a dialog with the BCN service and sends a missing route message, it will not send any more messages until the BCN service ends the dialog. When the dialog is ended, broker will try to classify dialogs targetting the missing service again. If no routes are found, the messages remain in DELAYED state for 10 minutes. Then service broker service will begin a new conversation with the BCN service to re-request the missing route.

|||

So if I have several clients with no routes information then BCN is not a solution ...

I mean the scenario I have presented in my question won;t work?

|||

What do you mean by several client? If you mean target services, Service Broker will send a request to BCN for each service with a missing route. So I don't see why this shouldn't work.