Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using BCP utility in SQL Server 2005 for generating reports

Posted on 2008-11-09
3
Medium Priority
?
1,028 Views
Last Modified: 2012-08-13
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 | ...
0
Comment
Question by:Khopkins32
  • 2
3 Comments
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 22920578
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
 

Author Closing Comment

by:Khopkins32
ID: 31514896
thanks for your help. It definitely led me to the right solution
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22946202
Thank you, and trust it will all work well for you...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question