Generate Flat File via Stored Procedure

I have a need to do the following:

Generate a Stored Procedure and have the output written in a csv format.

I have everything I need to capture the data via stored procedure, but I am lost on a way to 'INSERT' the data values into a csv file.

This stored procedure will be triggered by another application.

Could someone please help.

thanks

there are many ways to do that

1. in sql server 200 you can use a dts to out put the result

2. in sql server 2005 you can use SSIS

3. you can use the openrowset function

4. you cann configure a linked server and output the result.

|||

This might help

create procedure usp_runbcppkg

as

declare @.as_bcp varchar(255)

declare @.as_query nvarchar(255)

declare @.as_select varchar(255)

declare @.as_appcode varchar(20)

set @.as_select = 'bcp "select appcode, servername, region, usage_ctr, st_date, ed_date from pubs..temp"'

set @.as_query = 'master.dbo.xp_cmdshell ' + @.as_bcp

begin

--cursor to get the list of state

declare cur_appcode cursor for

select distinct state from authors

order by 1

open cur_appcode

fetch next from cur_appcode into @.as_appcode

while @.@.fetch_status = 0

begin

Truncate & Insert the date for the state in Temp Table

truncate table temp

insert into temp (au_id, au_lname, phone, address, city, state, zip)

select au_id, au_lname, phone, address, city, state, zip

from pubs..authors where state = @.as_appcode

--<Servername> to be entered

--<Password> to be entered

set @.as_bcp = @.as_select + ' queryout c:\temp\' + @.as_appcode + '.xls -S<Servername> -Usa -P<Password> -w'

exec master..xp_cmdshell @.as_bcp

fetch next from cur_appcode into @.as_appcode

end

close cur_appcode

deallocate cur_appcode

end|||

An OPENROWSET example which emails the results.

@.reportxls must exist on the server (an empty xls flle with column headers only).

Openrowset will append results to the file. If this is what you want then remove the copy, rename and delete parts of the code.

CREATE PROCEDURE sp_Test
(@.selectcmd as varchar(400), @.email_list as varchar(300), @.reportxls as varchar(40))

as

declare @.result as int, @.attach as varchar(140), @.renamecmd as varchar(200), @.deletecmd as varchar(200), @.copycmd as varchar(200), @.insertcmd as varchar(200), @.sql as varchar(600)
set @.attach = 'd:\reports\' + @.reportxls
set @.copycmd = 'copy d:\reports\' + @.reportxls + ' d:\reports\' + @.reportxls + '#'
set @.renamecmd = 'ren d:\reports\' + @.reportxls + '# ' + @.reportxls
set @.deletecmd = 'del d:\reports\' + @.reportxls
set @.insertcmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;Database=d:\reports\' + @.reportxls + ';'', ''SELECT * FROM [Sheet1$]'')'
set @.sql = @.insertcmd + @.selectcmd

EXEC @.result = master.dbo.xp_cmdshell @.copycmd
IF (@.result = 0)
BEGIN
EXEC (@.sql)
EXEC master.dbo.xp_sendmail @.recipients = @.email_list,
@.message = 'Test Body Message',
@.subject = 'Report Request',
@.attachments = @.attach
EXEC master.dbo.xp_cmdshell @.deletecmd
EXEC master.dbo.xp_cmdshell @.renamecmd
END

GO