February 6, 2005 at 3:59 pm
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 have above query I want the output file name to be like g_MMDDYY.txt
where MMDDYY is @cdr_date
how can I do that ?
February 6, 2005 at 6:15 pm
DECLARE @cdr_date datetime
DECLARE @filename varchar(15), @cmd varchar(4000), @sql varchar(2000)
SET @cdr_date = getdate()-1
SET @filename = 'g_' +
substring(convert(varchar, @cdr_date, 120), 6, 2) +
substring(convert(varchar, @cdr_date, 120), 9, 2) +
substring(convert(varchar, @cdr_date, 120), 3, 2) +
'.txt'
-- pub your code into sql string
set @sql = '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)'
-- output the file into c:\temp folder
set @cmd = 'OSQL -S(LOCAL) -E -Q"' + @sql + '" -oc:\temp\' + @filename
exec master.dbo.xp_cmdshell @cmd
February 7, 2005 at 8:03 pm
Thank you so much I appreciate your kind help. it works fine.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply