Another T-SQL question. 1

[ccampbell]

I have the following query written
-- ==================
-- Create Database
-- ==================
CREATE DATABASE CVIEW
ON
( NAME = 'CVIEW_dat',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\CVIEW.mdf',
SIZE = 2000MB,
FILEGROWTH = 10% )
LOG ON
( NAME = 'CVIEW_log',
FILENAME = 'c:\program files\microsoft sql server\mssql\data\CVIEW.ldf',
SIZE = 2000MB,
FILEGROWTH = 10% )
GO

My question is, is there a way to not specify the FILENAME option and have it select a default? I am trying to create a standardized query that will create a Db on any machine. For instance if the SQL directory is set up on the D:\ drive, my sql file will fail. How can I prevent this?

 

[tlbroadbent]

You can read the location of the model or master database and create the file in the same directory. That should work in most cases.

Declare @dir varchar(128), @sql nvarchar(2000)
Select @dir=rtrim(filename)
From sysfiles
Where name='model'

Set @dir=left(@dir, datalength(@dir)-charindex("\",reverse(@dir)))

Set @sql='
CREATE DATABASE CVIEW
ON
( NAME = CVIEW_dat,
FILENAME = ''' + @dir + '\CVIEW.mdf'',
SIZE = 200MB,
FILEGROWTH = 10% )
LOG ON
( NAME = CVIEW_log,
FILENAME = ''' + @dir + '\CVIEW.ldf'',
SIZE = 20MB,
FILEGROWTH = 10% )'
exec (@sql)

Is there a reason to create a 2GB intial file and a 2GB initial log? That seems quite wasteful to me. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[ccampbell]

Thanks, that is exactly what I was looking for. I had to change the line that said:
Set @dir=left(@dir, datalength(@dir)-charindex("\",reverse(@dir)))
Because it didn't like the "\". I changed it to single quotes '\' and that seemed to work just fine. The reason that I was creating a 2GB file and log was because my restore is fairly large in size 1.5 GB. I take it that it is not necessary. Anyway, thanks for the advice and the help, it is greatly appreciated!!!

 

[ccampbell]

Okay, so that didn't quite work how I needed it to work. I think that when I changed the "\" to single quotes'\' than it does something funky. If leave the double quotes I get the error Server: Msg 207, Level 16, State3, Line 12 INvalid column name '\'.

If i leave it in single quotes '\' I get the error Server: Msg 5105, Level 16, State 2, Line 36
Device Activation error. The physical file name 'c:\Program files\Microsoft SQL server\MSSQ\Data \\CVIEW.mdf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 36
File 'CVIEW_dat' cannot be restored to 'c:\...\\CVIEW.mdf'. Use WITH MOVE to identify a valid location for the file.

And then it repeats the same 2 messages for the log file. A couple of things that I notice here. First of all notice the '\\' before the file name. Should that be like that? Second of all, shouldn't it be looking for the place to store the files rather than taking the default location. My files happen to be stored on the D drive. Not the C drive. My tools are on the C drive and my actual database is on the d drive. Any suggestion for this one.

 

[tlbroadbent]

Try this.

Use Master
Declare @dir varchar(128), @sql nvarchar(2000)
Select @dir=rtrim(filename)
From sysfiles
Where name='master'

Set @dir=left(@dir, datalength(@dir)-charindex('\',reverse(@dir)))

Set @sql='
CREATE DATABASE CVIEW
ON
( NAME = CVIEW_dat,
FILENAME = ''' + @dir + 'CVIEW.mdf'',
SIZE = 200MB,
FILEGROWTH = 10% )
LOG ON
( NAME = CVIEW_log,
FILENAME = ''' + @dir + 'CVIEW.ldf'',
SIZE = 20MB,
FILEGROWTH = 10% )'
exec( @sql) Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[ccampbell]

I think that will work. Thanks. I am out of free space on my dummy computer so I will have to try it out somewhere else. It seems like I had a problem before when I used the statement Use master. It would create the database, but when I would restore the dbk file, it would just have the sys tables not all of the tables that were in my original database. When I got away from the Use Master statement then it would work fine. Do you know why that is? Obviously, T-SQL is very new to me. I am trying to learn the best that I can, and I really appreciate your replies and input. Thanks.

 

[tlbroadbent]

Drop the Use Master statement and change the Select statement as follows. That should avoid any problems associated with Use.

Select @dir=rtrim(filename)
From master.dbo.sysfiles
Where name='master'

Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[ccampbell]

Thanks. I will let you know if this posses any problems for me in the future. I really appreciate all your help and input. A definite thumbs up!!

 

[aalmeida]

The following statment worked for me:

Declare @dir varchar(128), @sql nvarchar(2000)
Select @dir=rtrim(filename)
From master.dbo.sysdatabases
Where name='model' -- or any other db with better path
Select @dir
Set @dir=left(@dir, datalength(@dir)-charindex("\",reverse(@dir)))
Select @dir

Set @sql='
CREATE DATABASE CVIEW
ON
( NAME = CVIEW_dat,
FILENAME = ''' + @dir + '\CVIEW.mdf'',
SIZE = 200MB,
FILEGROWTH = 10% )
LOG ON
( NAME = CVIEW_log,
FILENAME = ''' + @dir + '\CVIEW.ldf'',
SIZE = 20MB,
FILEGROWTH = 10% )'
exec (@sql)

I hope it helps
AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[ccampbell]

I get the error same as above. Invalid column name and it refers to the section
Set @dir=left(@dir, datalength(@dir)-charindex("\",reverse(@dir)))

It doesn't like the "\". If I change it to '\' then it works fine. Is that a problem to change it to '\'. Does that change anything?

 

[tlbroadbent]

By all means, change the double quotes to single quotes. Should cause no problem. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[ccampbell]

THANKS! And Thanks for all the input. You guys are great!