Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 BCP output w/column headers

Author  Topic 

fralo
Posting Yak Master

161 Posts

Posted - 2011-01-04 : 08:48:07
Hi,

I need to output a tab-delimited file with column headers at the top of the file. By googling I found that there is no straight forward way of doing this. It would be nice if there was a simple parameter to add to the bcp command to accomplish this. Anyway, here's a block of code for what I have thus far. Your help is greatly appreciated.

CREATE VIEW ConcentraOut As
SELECT 'Employee','LastName','FirstName','MiddleName','Address','[Union Name]','[Emergency Contact]',0 As SeqNo
UNION
SELECT [Employee],LastName,FirstName,MiddleName,Address,[Union Name],[Emergency Contact],1 As SeqNo From Concentra_Out

exec master..xp_cmdshell
'bcp "select [Employee],LastName,FirstName,MiddleName,Address,[Union Name],[Emergency Contact]
from ConcentraOut" out c:\bcp\concentra.txt -c -t "|" -T'

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 09:08:19
Think you were planning to have an order by SeqNo in the bcp query.
Might also what to change the union to a union all.
Is Employee a character column? Otherwise you will have to convert it.

I always name views with something to distinguish them - like v_ConcentraOut. Just to make it clear to people that it's not a table and may cause issues.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-04 : 09:11:57
Maybe this is interesting:
http://weblogs.sqlteam.com/brettk/archive/2005/04/13/4395.aspx


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

fralo
Posting Yak Master

161 Posts

Posted - 2011-01-04 : 09:14:56
Yes I did mean to place an order by clause. And Employee is a character column.

The error I'm getting is 'Incorrect syntax near the keyword 'VIEW'.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 09:33:36
create view needs to be the first statement in a batch - are you including something before it in the execution?

I take it the exec is being executed separately.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

fralo
Posting Yak Master

161 Posts

Posted - 2011-01-04 : 09:39:21
Yes, my code is part of a stored procedure. I have code which populates a table. After that, I need to extract the data from the table and generate an output file. Any ideas?

Thanks for your help thus far.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 09:45:46
The create view shouldn't be part of the SP.
The SP just populates Concentra_Out then uses the view for the bcp statement.



==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

fralo
Posting Yak Master

161 Posts

Posted - 2011-01-04 : 10:03:59
So I should simply create a view against the database and then query from it within the SP?
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 10:10:37
Yep.
A view is a permanent object.

You could also dispense with the view altogether and include the code in the bcp statement.
Also could make the view (you need to put colname in the first select statement in the view)

CREATE VIEW ConcentraOut As
select top 100 percent [Employee],LastName,FirstName,MiddleName,Address,[Union Name],[Emergency Contact]
from
(
SELECT [Employee]='Employee',LastName='LastName','FirstName','MiddleName','Address','[Union Name]','[Emergency Contact]',0 As SeqNo
UNION ALL
SELECT [Employee],LastName,FirstName,MiddleName,Address,[Union Name],[Emergency Contact],1 As SeqNo From Concentra_Out
) a
order by SeqNo
go

then you can treat it like a table in the bcp rather than using a query.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

fralo
Posting Yak Master

161 Posts

Posted - 2011-01-04 : 10:44:37
My file is now not being generated. Any reason you can think of?

exec Test..xp_cmdshell
'bcp "SELECT [Employee Number],LastName,FirstName,MiddleName,Address,[Union Name],[Emergency Contact],From v_ConcentraOut
order by SeqNo, [Employee Number]" out c:\bcp\concentra.txt -c -t "|" -T'
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 10:46:39
"out" should be "queryout"
That should give you an error which you need to trap and alert.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

fralo
Posting Yak Master

161 Posts

Posted - 2011-01-04 : 11:03:53
It still doesn't work. I tried running a variation of it from the dos prompt to see if that would work but I get an error there as well. Does this look right from dos prompt?

bcp "Select * from test..v_concentraout" queryout c:\bcp\concentra.txt -c -t "|" -T
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 11:11:54
If it's run on the server against a default instance then I think it should be ok. I include the -S parameter to explicitely specify the server.
What is the error you get.

It's always best to try the simplest possible if you can't get it to work
bcp master..sysobjects out c:\bcp\concentra.txt -c -T -Smyserver


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

fralo
Posting Yak Master

161 Posts

Posted - 2011-01-04 : 11:15:12
The error is:

SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unable to open BCP host data-file
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-04 : 11:20:41
That's talking about the file you are tryong to create.
Does the folder c:\bcp\ exist on the m/c you are runnin the bcp?


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

fralo
Posting Yak Master

161 Posts

Posted - 2011-01-04 : 11:23:24
Yep it does exist.
Go to Top of Page

lonely cowboy
Starting Member

1 Post

Posted - 2014-03-24 : 18:20:19
EXEC DATABASE_NAME..xp_cmdshell 'bcp "select ''id'' as id, ''detail'' as detail, ''creation_date'' as creation_date UNION ALL select convert(varchar,id), detail, convert(varchar,creation_date) from DATABASE_NAME.dbo.TABLE" queryout "C:\file.txt" -c -t, -r \n -T -S "ServerName"'


Try this
Go to Top of Page
   

- Advertisement -