October 6, 2009 at 2:17 am
hi i want to generate a text file report for each row of a table.
create table name (id int identity,name varchar(50),surname varchar(50),address varchar(50));
I have worked out with this.
declare @count int;
declare @path nvarchar(max);
declare @incount int;
declare @sql nvarchar(2000);
select @count=count(*) from rep_document
set @incount =5001;
while (@incount<=5010)
BEGIN
select @path='C:\' + cast(@incount as nvarchar(max)) +'.txt'
set @sql = 'EXEC master..xp_cmdshell''bcp "select name, surname,address from dbname..tblname where id='+cast(@incount as nvarchar(max))+'" queryout "'+ @path +'" -c -T -x'''
print @sql
exec sp_executesql @sql;
set @incount=@incount+1;
END
but all the data in text file comes in a one line.
can i format each column in new line in text file?
October 6, 2009 at 11:34 am
I guess you could output the file as an xml file with the for xml statement tagged on the end of your query.
bcp "select * from adventureworks.humanresources.department
where departmentid = 1 for xml path" queryout d:\test.xml -T -c
This isn't really what bcp is for though. It is a data bulk copy program. Any solution offered would be, at best, a hack. If you need to generate reports, you may be better off looking to the likes of SSRS etc.
October 6, 2009 at 2:28 pm
Or just write a simple C# app to hook up to the DB, write out the data you want.
I don't really 'get' people using stored procedures to write stuff to the disk on the sql server - it just makes for a process which is either messy, or manual.
Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply