SQL: StoredProc to Create DB?

[adridude]

Hello,

I've been battling all day with this stored proc I tried to create. All I need it to do is create a table based on an arguement I pass it:

CREATE PROCEDURE [dbo].[sp_CreateUserLocalDB]

AS
SELECT 0
GO


--
-- Definition for stored procedure sp_CreateUserLocalDB :
--

ALTER PROCEDURE [dbo].[sp_CreateUserLocalDB]
@username varchar(255)
AS

CREATE DATABASE QUOTENAME(@username)
USE QUOTENAME(@username)

CREATE TABLE [dbo].[Actions] (
[actionid] int DEFAULT (0) NULL,
[marketingid] int DEFAULT (0) NULL,
[actionno] int DEFAULT (0) NULL,
[actiondate] datetime NULL,
[actiontypeid] int DEFAULT (-1) NULL,
[actiontypeidtext] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[replydate] datetime NULL,
[replyid] int DEFAULT (-1) NULL,
[replyidtext] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
[addedby] int DEFAULT (0) NULL,
[lastedit] datetime NULL
)
ON [PRIMARY]



For starters, it doesnt like the first QUOTENAME(@username); It tells me that there is an error near @username?

Seconds, it reckons I cant use a USE statement in a SP; how do I get the SP to create the tables in the other database (not in master)

 

[mrdenny]

You'll need to use Dynamic SQL to create the table.

This should have been posted in the SQL Server forums.

CREATE PROCEDURE [dbo].[sp_CreateUserLocalDB]

AS
SELECT 0
GO

--
-- Definition for stored procedure sp_CreateUserLocalDB : 
--

ALTER PROCEDURE [dbo].[sp_CreateUserLocalDB]
   @username  varchar(255)
AS

DECLARE @cmd varchar(8000)

set @cmd = 'CREATE DATABASE ' + @username + '
USE ' + @username + '

CREATE TABLE [dbo].[Actions] (
  [actionid] int DEFAULT (0) NULL,
  [marketingid] int DEFAULT (0) NULL,
  [actionno] int DEFAULT (0) NULL,
  [actiondate] datetime NULL,
  [actiontypeid] int DEFAULT (-1) NULL,
  [actiontypeidtext] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
  [replydate] datetime NULL,
  [replyid] int DEFAULT (-1) NULL,
  [replyidtext] nvarchar(255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,
  [addedby] int DEFAULT (0) NULL,
  [lastedit] datetime NULL
)
ON [PRIMARY]'
exec (@cmd)
go

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[adridude]

thanks, much appreciated. i did it another way actually

I used:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_CreateUserLocalDB]
@username varchar(255)
AS
BEGIN

DECLARE @COPY1 varchar(255)
DECLARE @COPY2 varchar(255)
DECLARE @ATTACH varchar(255)
DECLARE @FILEATTACH1 varchar(255)
DECLARE @FILEATTACH2 varchar(255)

SET @COPY1 = 'copy d:\SQLData\UDMRT.mdf d:\SQLData\UDMRT' + @username + '.mdf'
SET @COPY2 = 'copy d:\SQLData\UMDRT_1.ldf d:\SQLData\UDMRT' + @username + '_1.LDF'
SET @ATTACH = 'UDMRT' + @username
SET @FILEATTACH1 = 'd:\SQLData\UDMRT' + @username + '.mdf'
SET @FILEATTACH2 = 'd:\SQLData\UDMRT' + @username + '_1.LDF'

EXEC xp_cmdshell @COPY1

EXEC xp_cmdshell @COPY2

EXEC sp_attach_db @dbname = @ATTACH,
@filename1=@FILEATTACH1,
@filename2=@FILEATTACH2
END


and yes, I'm aware that xp_cmdshell is a dangerous sp to use. This is in a very controleld environment, SQL is nowhere near public, thed atabase fiels are ni a separate folder, and the database which is copied is the ONLY one which has permissions. THe main database which the localDB is created for is nothing more than a temp db which gets deleted after logout.

It seems like a crude hack, btu it works ><

 

[mrdenny]

You can't copy files if the database is still attahed to SQL.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com