I have a DTS job step with the SQL below (abridged). As an example, I want
to take the file name 'mms_datamodules_Oct_Data' and dynamically assign it to
the current month instead of hard coding it each month. How can variables be
used to accomplish this?

CREATE DATABASE [mms_datamodules_new] ON (NAME =
N'mms_datamodules_Oct_Data',
FILENAME = N'D:\Program Files\Microsoft SQL
Server\MSSQL\data\mms_datamodules_Oct_Data.MDF' ,
SIZE = 2500, FILEGROWTH = 10%) LOG ON (NAME = N'mms_datamodules_Oct_Log',
FILENAME = N'D:\Program Files\Microsoft SQL
Server\MSSQL\data\mms_datamodules_Oct_Log.LDF' ,
SIZE = 13, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
GO

You would need to use dynamic sql to create a string with your sql statement
in it. You can then use sp_executesql to fire off the query. Of course you
would need to also setup a variable and set it via some sql to get the month
you want to create.
--
Keith Mescha


"SQLCat" wrote:

I ended up using monthname as a variable and assisning the sql datename(m,
getdate()) to it as displayed below: (I could also use the sp_executesql proc
as well)

IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name =
N'mms_datamodules_new')
DROP DATABASE [mms_datamodules_new]
GO

Declare @monthname varchar (15)
Set @monthname = datename(m, getdate())
Declare @strSQL varchar(8000)

SET @strSQL = 'CREATE DATABASE mms_datamodules_new
ON (NAME = mms_datamodules_'+@monthname+'_Data,
FILENAME = [D:\Program Files\Microsoft Sql
Server\MSSQL\Data\mms_datamodules_'+@monthname+'_D ata.MDF] ,
SIZE = 2500, FILEGROWTH = 10%) LOG ON (NAME =
mms_datamodules_'+@monthname+'_Log,
FILENAME = [D:\Program Files\Microsoft Sql
Server\MSSQL\Data\mms_datamodules_'+@monthname+'_L og.LDF] ,
SIZE = 13,
FILEGROWTH = 10%)'

EXEC (@strSQL)

"Mescha" wrote: