Creating Database at run time??????? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Creating Database at run time???????

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-08-26 : 03:09:26

hello friends!

I got some tricky situation here i want to design run time database and add my some fixed tables in it...i explain whole sinario..see i am using marketing advertising...suppose user create his profile with authentication then automaticaaly create his database like DB_UserProfileID then add my basic tables here like testTbl,testTb2 like that i used stored procedure like

create Proc SP_DBUserProfileID
@UserID varchar(100)
as

declare @DBID as varchar(100)
declare @queryDB as varchar(8000)

set @DBID = @UserID

set @queryDB = 'use master
go

create database ' + '''DB_UserProfileID' + @DBID+ '''
on
primary ( Name = ' + '''DB_UserProfileID' + @DBID + ''',
filename = ' + '''C:\Program Files\Microsoft SQL server 2005\MSSQL.1\MSSQL\DATA\DB_UserProfileID' + @DBID + '.mdf'+''',
size = 100MB,
MaxSize = 200,
filegrowth = 20)
log on
( Name = ' + '''DB_UserProfileID' + @DBID+ '_log'',
filename = ' + '''C:\Program Files\Microsoft SQL server 2005\MSSQL.1\MSSQL\DATA\DB_UserProfileID' + @DBID + '_log.ldf'+''',
size = 100MB,
MaxSize = 200,
filegrowth = 20)
GO

use DB_UserProfileID' +@DBID+
' Alter DB_UserProfileID' +@DBID+ ' ADD table [G117_data].testTbl'

--- i want to add all exception handling (how to handle??) and is there neccesity of Begin Tran statement for security purpose???????

Please correct me if i m wrong

T.I.A

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-08-26 : 06:18:44

Am guessing your aim is to create a table for each user profile ???

Why not create one table and enter each user's profile in it ?

Afrika

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-08-26 : 06:21:42

hi afrika

i want to create database for each user and its related tables in that database :)

T.I.A

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-08-26 : 07:32:41

Well it dont seem right to me.

What you need to is to create a table to contain user profiles, if you end up having 1million users, woudl you then create 1 million tables ?

Also when you declare a variable in should assign the length to it. ie declare @UserID varchar(20),@DBID varchar(20)

You dont need this statment set @DBID = @UserID
as you can use the @userID variable the same way you are using the @DBID

you didnt terminate this statement set @queryDB = 'use master should be set @queryDB = 'use master'

Also dont create your tables in master db, create a seperate db for your use.

What i woudl advice is to create a table for user and enter each person info there.

You can create a home directory for all users under a root directory. That way it seems more efficient.

Afrika

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-08-26 : 08:22:38

thanks afrika

let me clear my point here i want to create domain user database under which there are number of tables.....not domain tables...

see suppose user1 is owner of some warehouse and he has number of customers....now i want to keep that records track and suppose user2 is owner of another warehouse so he has his own customers..as soon as user1 and user2 create login profile for them then i should able to create there Database seperate so that they use their indiviual tables for there customers..like that my idea is and suppose if you print @queryDB then you get clear idea of what i am tring to do...i need this one clear ...

my out put is

use master
go

create database DB_UserProfileID1
on
primary ( Name = 'DB_UserProfileID1',
filename = 'C:\Program Files\Microsoft SQL server 2005\MSSQL.1\MSSQL\DATA\DB_UserProfileID1.mdf',
size = 100MB,
MaxSize = 200,
filegrowth = 20)
log on
( Name = 'DB_UserProfileID1_log',
filename = 'C:\Program Files\Microsoft SQL server 2005\MSSQL.1\MSSQL\DATA\DB_UserProfileID1_log.ldf',
size = 100MB,
MaxSize = 200,
filegrowth = 20)
GO

but i have big problem is how i add my own tables into created database...

T.I.A

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2006-08-26 : 08:53:36

hey i tried this one just check :)

create Proc SP_DBUserProfileID
@UserID varchar(100)
as

declare @DBID as varchar(100)
declare @queryDB as varchar(8000)
declare @query2 as varchar(8000)

set @DBID = @UserID

set @queryDB = 'use master
create database DB_UserProfileID' + @DBID+ '
on
primary ( Name = ' + '''DB_UserProfileID' + @DBID + ''',
filename = ' + '''C:\Program Files\Microsoft SQL server 2005\MSSQL.1\MSSQL\DATA\DB_UserProfileID' + @DBID + '.mdf'+''',
size = 100MB,
MaxSize = 200,
filegrowth = 20)
log on
( Name = ' + '''DB_UserProfileID' + @DBID+ '_log'',
filename = ' + '''C:\Program Files\Microsoft SQL server 2005\MSSQL.1\MSSQL\DATA\DB_UserProfileID' + @DBID + '_log.ldf'+''',
size = 100MB,
MaxSize = 200,
filegrowth = 20)'

set @query2 = '
use DB_UserProfileID' +@DBID+
' select * into testTbl
from
(select * from [G117_data].dbo.testTbl) p'

exec (@queryDB)

exec (@query2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources