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+'_Data.MDF] ,
SIZE = 2500, FILEGROWTH = 10%) LOG ON (NAME =
mms_datamodules_'+@monthname+'_Log,
FILENAME = [D:\Program Files\Microsoft Sql
Server\MSSQL\Data\mms_datamodules_'+@monthname+'_Log.LDF] ,
SIZE = 13,
FILEGROWTH = 10%)'
EXEC (@strSQL)
[quoted text, click to view] "Mescha" wrote:
> 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 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
