I had used bcp to insert records in the csv file from the database table which is on the server. once the csv file is created bcp puts the file on the path specified, provided the server and the local machine are on the network. the folder in which the file is to be saved on the local machine should be shared.
below is the Stored procedure i have used with northwind db:
CREATE Procedure aa
As
Begin
Declare @cmd varchar(300)
Declare @outfile varchar(300)
Declare @file varchar(300)
set @file = 'SMATMMYY'
set @outfile = ('\\192.168.241.217\Share\'+@file+'.csv')
Print @outfile
set @cmd = 'BCP "exec '+ db_name() +'..spsp " QUERYOUT ' + @outfile + ' -c -t "," '
Print @cmd
Exec master..xp_cmdshell @cmd
end
GO
CREATE Procedure spsp
As
Begin
select * from northwind..orders
end
GO