Using BCP utility in SQL Server 2005 for generating reports

I am currently working with SQL Server 2005 and I want to use the BCP Utility to generate reporting information which is processed by another application. The output of the file takes the form of a pipe delimited csv file. The question that I have is how do I include the table columns as part of the report output? Is there a way to do this so that both the columns and the associated data are displayed in the .csv file?

i.e column1 | column2 | column3 | ....
     data1     | data2     | data3 | ...
Khopkins32Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Sure is, can be a bit of a pain, but a couple of techniques....

First create a simple view that concatenates the columns - both headings from information_schema then data from the table itself, then, using bcp just do a query out of that view...

Or, if more columns and in need of inspection, might need to go to a user-defined procedure or function whereby you can do some coding around the column data before outputing. But let's try the simple things first, two methods, first a simple view over the data, second a procedure over the data. First up we create test data by way of example only...


--
-- step 1 create a test table
--
create table my_test_data (id int identity, column1 varchar(60), column2 varchar(60), date1 datetime)
GO
--
-- step 2 create some test data - about 10000 rows should work ok
--
declare @i int
set @i = 1
while isnull(@i,0) < 10000
begin
    set @i = isnull(@i,0) + 1
	insert my_test_data (column1, column2, date1)
	select 'Row_'+convert(varchar,@i)+'_Column_1','Row_'+convert(varchar,@i)+'_Column_2',dateadd(dd,@i,'20060101')
end
GO
--
-- step3 - method 1 create a view over the data - no good for choosing which rows though...
--
create view view_my_test_data
as
select top 100 percent line from
(
select 0 as seq,convert(varchar(max),'id|column1|column2|date1|') as line 
union
select 1,replace(line,'#char13#char10#',char(13)+char(10)) from
(select convert(varchar(max),(select convert(varchar,id)+'|'+convert(varchar,column1)+'|'+convert(varchar,column2)+'|'+convert(varchar,date1)+'|'+'#char13#char10#' from my_test_data for xml path(''), type)) as line) a
) data order by seq
GO
--
-- step4 - check out the view - only 2 rows !!
--
select * from view_my_test_data
GO
--
-- step5 - now BCP that view for output - quite different as a text file !
--
exec master..xp_cmdshell 'bcp "select * from mrwtemp..view_my_test_data" queryout "c:\my_test_data.csv" -T -c -CACP'
GO
--
-- step6 - method 2 using a procedure (or a function) where parameters can be passed
--
create procedure usp_my_test_data(@column1 varchar(60))
as
select top 100 percent line from
(
select 0 as seq,convert(varchar(max),'id|column1|column2|date1|') as line 
union
select 1,replace(line,'#char13#char10#',char(13)+char(10)) from
(select convert(varchar(max),(select convert(varchar,id)+'|'+convert(varchar,column1)+'|'+convert(varchar,column2)+'|'+convert(varchar,date1)+'|'+'#char13#char10#' from my_test_data where column1 like isnull(@column1,'%') for xml path(''), type)) as line) a
) data order by seq
GO
--
-- step7 - have a look at the procedure results passing an argument which will get every column1 with values like "row" and has a "2" in it 
--
exec usp_my_test_data 'Row%2%'
GO
--
-- step8 - use that procedure in the BCP command...
--
exec master..xp_cmdshell 'bcp "exec mrwtemp..usp_my_test_data ''Row%2%''" queryout "c:\my_test_data.csv" -T -c -CACP'
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Khopkins32Author Commented:
thanks for your help. It definitely led me to the right solution
0
Mark WillsTopic AdvisorCommented:
Thank you, and trust it will all work well for you...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.