Link to home
Start Free TrialLog in
Avatar of amburn
amburn

asked on

Need to create database (sql server 2000) with a variable filename.

The filename switches from A to B and back and forth each time the database is newly created, so we have a parameters table in the production database that contains the filename for the next time the database is created. This way we can automate the switch without manually changing the sql code each time we want to run it.

I extract the filenames like this:
-----------------------------------------------
use encompasstest

DECLARE @newname char(9)
select @newname=FILENAME_TO_USE from webleads_parms

DECLARE @mdfFileName varchar(50)
SET @mdfFileName = 'd:\encompass\' + @newname + '.mdf'

DECLARE @ldfFileName varchar(50)
SET @ldfFileName = 'd:\encompass\' + @newname + '.ldf'
-----------------------------------------------

Then all I need to do is use the ldf and mdf filename variables inside the Create Table statement, but I can't get them to work:
-----------------------------------------------
CREATE DATABASE encompass_temp ON (  
       NAME = encompass_temp,        
       FILENAME = @mdfFileName,      
       SIZE = 500MB,                
       MAXSIZE = UNLIMITED,          
       FILEGROWTH = 100MB)          
LOG ON (                            
       NAME = log_data,              
       FILENAME = @ldfFileName,      
       SIZE = 100MB,                
       MAXSIZE = UNLIMITED,          
       FILEGROWTH = 100MB)          
GO
-----------------------------------------------

I was afraid to try to put single quotes around the @variable because it would just use that literally for the filename?

So I tried to create a big long string and use the EXEC statement on it, but it says its too long (128 byte limit) when I finally got it to accept my syntax. I also don't know how to get a single quote character inside of a string, my syntax ended up with filename = d:\encompass\webleadsA.mdf without single quotes around it, and that's when it gave me the length error. Before that I tried double quotes and parentheses around my filename, and it gave a syntax error.
-----------------------------------------------
DECLARE @create_db_command varchar(500)
SET @create_db_command = '"CREATE DATABASE encompass_temp ON (NAME = encompass_temp, FILENAME = '
SET @create_db_command = @create_db_command + @mdfFileName + ', SIZE = 500MB,MAXSIZE = UNLIMITED,FILEGROWTH = 100MB)'
SET @create_db_command = @create_db_command + 'LOG ON (NAME = log_data,FILENAME = '
SET @create_db_command = @create_db_command + @ldfFileName + ', SIZE = 100MB,MAXSIZE = UNLIMITED,FILEGROWTH = 100MB)"'
PRINT @create_db_command
EXEC(@create_db_command)
-----------------------------------------------


Can anybody help?
Thanks
ASKER CERTIFIED SOLUTION
Avatar of adathelad
adathelad
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of amburn
amburn

ASKER

This worked perfectly! My database was successfully created with the variable filename option on the first try after this answer! Now I also know how to get single quotes into a string!
Thanks a million!