Got a question for you DBAs out there!

[Mucman]

I am not a SQL guru, nor do I know much about DB administration I am trying to write a stored procedure that will create DBs on our SQL2000 machine.
I have two syntax errors which I believe won't let me accomplish what I want... I want to confirm that what I am trying to do is do able or not.

It seems like it doesn't like me using variables for the "CREATE DATABASE" command (if I remove the '@' it doesn't complain). It also doesn't like the
statement where I try to USE 'newdatabase' it complains if I use the variable or it complains the DB doesn't exist. Is what I am trying to do possible?


CREATE PROCEDURE sp_CreateDB
@dbname char(10),
@pass char(10)
AS
CREATE DATABASE @dbname
ON
( NAME = '@dbname',
FILENAME = 'e:\SQL\MSSQL\data\@dbname.mdf',
SIZE = 10,
MAXSIZE = 100MB,
FILEGROWTH = 10MB )
LOG ON
( NAME = '@dbname',
FILENAME = 'd:\SQL\MSSQL\data\@dbname.ldf',
SIZE = 5MB,
MAXSIZE = 20MB,
FILEGROWTH = 5MB )
GO
EXEC sp_addlogin '@dbname', '@pass'
GO
USE @dbname
GO
sp_changedbowner '@dbname''
EXEC sp_defaultdb '@dbname'', '@pass'
GO

 

[Mucman]

anybody?

 

[cyberia]

I see two possible problems:

1. Get rid of the first three GO statements. Add a GO statement just before EXEC sp_defaultdb '@dbname'', '@pass' line.

2. Add BEGIN ... END block. Put BEGIN just before CREATE DATABASE @dbname line. Put END just after sp_changedbowner '@dbname'' line.

I have not tried it myself, but hope it helps.

cyberia.

 

[Mucman]

Thanks for the tips... I now get an error saying I cannot use "USE" statement is not allowed in a trigger or procedure.
Ugh... I am starting this might not be able to be done.


it now looks like this :

CREATE PROCEDURE sp_CreateDB
@dbname char(10),
@pass char(10)
AS
BEGIN
CREATE DATABASE @dbname
ON
( NAME = '@dbname',
FILENAME = 'e:\SQL\MSSQL\data\@dbname.mdf',
SIZE = 10,
MAXSIZE = 100MB,
FILEGROWTH = 10MB )
LOG ON
( NAME = '@dbname',
FILENAME = 'd:\SQL\MSSQL\data\@dbname.ldf',
SIZE = 5MB,
MAXSIZE = 20MB,
FILEGROWTH = 5MB )
EXEC sp_addlogin '@dbname', '@pass'
USE @dbname
sp_changedbowner '@dbname'
END
GO
EXEC sp_defaultdb '@dbname', '@pass'

 

[BuckleDownBen]

Ask your question in the Microsoft SQL Server Programming newsgroup and it will be answered in 5 minutes.

 

[Mucman]

I found my problem! I had to fine tune my searching on deja.com



<< As far as the Stored Procedure itself goes, there are several commands such
as CREATE DATABASE, that will not allow the use of variables, it has to be a
constant. However, you can get round this by doing the following:

DECLARE @sqlcmd varchar(xxx)

SELECT @sqlcmd = 'CREATE DATABASE ' + @dbname .....

EXEC (@sqlcmd) >>

 

[cyberia]

If you do not get any ideas from the Microsoft's forum, you could try to undo my suggestion #1 (while keeping BEGIN...END from suggestion #2).

If you do get this resolved, post the solution here, so we all can learn.

cyberia

Obviously, it took me to long to type in my last suggestion. I am glad you solved your problem.

 

[Mucman]

Ugh, I got my SP to compile but I get an error when calling my SP

Microsoft OLE DB Provider for SQL Server error '80040e14'

The file named 'blahlbah' is already in use. Choose another name.

/mucdev/createsql.asp, line 46

Here is what the SP looks like right now (which does compile)

CREATE procedure PacficCreateDB
@dbname nvarchar(255),
@pass nvarchar(255)
as
set nocount on
declare @str nvarchar(2000)
select @str='
Use Master
create database '+@dbname+'
on
(name='+@dbname+',
filename = ''e:\SQL\MSSQL\data\'+@dbname+'.mdf'',
size = 10MB)
log on
(name='+@dbname+',
filename = ''d:\SQL\MSSQL\data\'+@dbname+'.bak'',
size = 5MB)'
exec(@str)
GO

 

[cyberia]

So, how does line 46 look like?

 

[Mucman]

line 46 is just the execution of the SQL command

something like

conn.Execute sql

 

[BuckleDownBen]

If you have "conn.Execute sql " then it looks like you are using VB. If so, you may want to look at to the SQL DMO objects, which lets you do pretty much anything with SQL Server that you can do in Enterprise Manager.

 

[Mucman]

BuckleDownBen - Hehe, I know I could have done it that way, but I am very new to SQL programming and I knew nothing about stored procedures... I figured this would be a good way to learn

 

[cyberia]

So, which file does 'blahblah' refers to? Is it possible that either the file specified in 'e:\SQL\MSSQL\data\@dbname.mdf', or the file specified in ''d:\SQL\MSSQL\data\'+@dbname+'.bak'',
already exists? In other words, maybe it was created with some other means and now the SQL server will not overwrite it?

I just noticed: in the first version of your stored procedure, you used .mdf and .ldf extensions, in the last version you are using .mdf and .bak. Is this really what you want to do?