?
Solved

CREATE DATABASE

Posted on 2001-07-24
3
Medium Priority
?
311 Views
Last Modified: 2008-03-17
Hi folks,

i am wanting to write a script to create a new database on any server.

i have got this:

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

declare @FN varchar(128)
declare @LN varchar(128)

SELECT @FN = LEFT(FILENAME, LEN(FILENAME)-10)+'quotaman_data.MDF', @LN = LEFT(FILENAME, LEN(FILENAME)-10)+'quotaman_data.LDF' FROM MASTER.DBO.SYSALTFILES WHERE NAME = 'tempdev'
GO

CREATE DATABASE [quotaman] ON (NAME = 'quotaman_Data', FILENAME = @FN, SIZE = 6, FILEGROWTH = 10%) LOG ON (NAME = N'quotaman_Log', FILENAME = @LN, SIZE = 1, FILEGROWTH = 10%)
GO

but i am always rewarded with

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '@FN'.

so how else can i dynamically set the os_file_name?

thanks and regards,

Mike.
0
Comment
Question by:meverest
3 Comments
 
LVL 6

Expert Comment

by:acampoma
ID: 6312475
try using this proc
IF OBJECT_ID('dbo.CreateDatabase') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.CreateDatabase
    IF OBJECT_ID('dbo.CreateDatabase') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.CreateDatabase >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.CreateDatabase >>>'
END
go
CREATE PROCEDURE CreateDatabase (
@DBName varchar (25),
@DbPath varchar (50),
@LogPath varchar (50)

)
AS
Declare @DBFile varchar (40)
Declare @LogFile  varchar (40)
Declare @exec_str varchar(1000)

   set @DBFile = @DbPath + @DBName + '.mdf'
   set @LogFile =@LogPath+ @DBName + '_log.ldf'

   --print @DBName
   --print @DBFile
   --print @LogFile
   set @exec_str = 'CREATE DATABASE ' + @DBName + '
   ON
   ( NAME = ' + @DBName + '_dat,
     FILENAME = "' + @DBFile + '",
     SIZE = 7,  
     MAXSIZE = unlimited,
     FILEGROWTH = 10% )
   LOG ON
   ( NAME = ' + @DBName + '_log,
     FILENAME = "' + @LogFile + '",
     SIZE = 4MB,  
     MAXSIZE = unlimited,
     FILEGROWTH = 2% )'
   EXEC (@exec_str)
go
IF OBJECT_ID('dbo.CreateDatabase') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.CreateDatabase >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.CreateDatabase >>>'
go
0
 
LVL 2

Accepted Solution

by:
Caseys95 earned 200 total points
ID: 6313310
acampomo had it right.

The use of the variables in create statement are not resolved at execution time.

I modified your code to get the result that I think you are looking for.

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

declare @FN varchar(128)
declare @LN varchar(128)
declare @SQL nvarchar(512)

SELECT @FN = IsNull(LEFT(FILENAME, LEN(FILENAME)-10), '')+'quotaman_data.MDF',
       @LN = IsNull(LEFT(FILENAME, LEN(FILENAME)-10), '')+'quotaman_data.LDF'
  FROM MASTER.DBO.SYSALTFILES WHERE NAME = 'tempdev'

Set @SQL = "CREATE DATABASE [quotaman] ON (NAME = 'quotaman_Data', FILENAME = '" + @FN  +
          "', SIZE = 6, FILEGROWTH = 10%) LOG ON (NAME = N'quotaman_Log', FILENAME = '" + @LN +
          "', SIZE = 1, FILEGROWTH = 10%) "

Execute sp_executesql @SQL
GO

0
 
LVL 37

Author Comment

by:meverest
ID: 6315688
acampoma - thanks for your suggestion. i couldn't get it to work, but i didn't try too hard because i wanted to avoid having to remove the stored proc afterward..

i award the points to Caseys95 because this is a most elowuent solution.

thanks all.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question