out put to text file issue

DECLARE @.cdr_date datetime
SET @.cdr_date = getdate()-1
SET NOCOUNT ON
select *
from call
where year(starttime)=year(@.cdr_date) and month(starttime)=month(@.cdr_date) and day(starttime)=day(@.cdr_date)

I want to run this query with output file like g_YYMMDD ( where YYMMDD will come from @.cdr_date

How can I do thatFor this to work reliably you need to create a couple of procedures:

create proc dbo.sp_test
@.cdr_date datetime = current_timestamp
as
SET NOCOUNT ON
select *
from call
where year(starttime)=year(@.cdr_date) and month(starttime)=month(@.cdr_date) and day(starttime)=day(@.cdr_date)
return (0)
go

Then create another procedure that will create the output file:

create proc dbo.sp_bcp_queryout as
@.cdr_date datetime = current_timestamp
as
declare @.cmd varchar(8000)
--assuming the db name is MYDB, server name - MYSERVER
set @.cmd = 'bcp "exec MYDB.dbo.sp_test ' + char(39) +
convert(char(10), @.cdr_date, 101) + char(39) + '" queryout D:\g_' +
convert(char(6), @.cdr_date, 12) + ' -S MYSERVER -T -c'

exec master.dbo.xp_cmdshell @.cmd
return (0)
go|||Thank you very much|||now I get following error

SQLState = 37000, NativeError = 8146
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure sp_call_get_cdr has no parameters and arguments were supplied.
NULL|||At what point are you getting the error? I also recommend to use "-e Error-ExceptionFile.ERR" on BCP so that you can check what record(-s) caused the error.|||Thanks for the quick reply

I got it. it works now. the only quesitons remains is that if I want to create csv file can I do that with column name as headers.

please advice.

thanks for your help in advance|||Yes you can, but you'll need to switch from BCP to OSQL.|||so can you please be specific. which command I need to put|||You should be able to create a CSV with BCP by using Column terminators:
8.0
4
1 SQLSMALLINT 0 2 "" 1 smallintField ","
2 SQLNCHAR 2 100 "'" 2 charField1 "',"
3 SQLNCHAR 2 100 "'" 3 charField2 "',"
4 SQLDATETIME 0 8 "'" 4 datetimeField "',"