Re: Changing file names using xp_cmdshell

From: Gregory A. Larsen (greg.larsen_at_netzero.com)
Date: 07/19/04


Date: Mon, 19 Jul 2004 15:21:16 -0700

You can do that with dynamic sql, something like this:

declare @firstyr int,
@var varchar(1000),
@cmd varchar(1000),
@dcmd varchar(1000)

select @firstyr = YEAR ( getdate() )
SET @var = 'c:\temp\' + convert (varchar(4), @firstyr)
select @var
SET @cmd = @var + '.txt'
select @cmd

--set @dcmd = 'exec master.dbo.xp_cmdshell ''osql -Sserver -Usa -Ppass -o '
+ rtrim(@cmd) + ' -Q" set nocount on;select top 20 OrderId, CompanyName,
OrderDate from Northwind.dbo.Orders O join Northwind.dbo.Customers C on
O.CustomerId = C.CustomerId order by OrderDate desc"'' , no_output'
set @dcmd = 'exec master.dbo.xp_cmdshell ''osql -S. -E -o ' + rtrim(@cmd) +
' -Q" set nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"'' , no_output'

print @dcmd
exec (@dcmd)

-- 
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"D" <D@discussions.microsoft.com> wrote in message
news:E7C149D3-BA9D-4CC0-A669-EA0C3EAA0633@microsoft.com...
> My goal is to run a script like the one below but for every time I run it
I get a new year.  For instance if I run it this year I'll get 2004.txt for
my file name and next year I'll get 2005.txt for my file name.  Is this
possible?  If so any suggestions.
>
> declare @firstyr int,
> @var sysname,
> @cmd sysname
>
> select @firstyr = YEAR ( getdate() )
> SET @var = 'c:\temp\' + convert (varchar(4), @firstyr)
> select @var
> SET @cmd = @var + '.txt'
> select @cmd
>
> exec master.dbo.xp_cmdshell  'osql -Sserver -Usa -Ppass -o @cmd -Q" set
nocount on;select top 20 OrderId, CompanyName, OrderDate from
Northwind.dbo.Orders O join Northwind.dbo.Customers C on O.CustomerId =
C.CustomerId order by OrderDate desc"' , no_output
>
>
>
>