Re: stored procedure
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 10/29/04
- Next message: Adam Machanic: "Re: deadlock/blocking in sql server"
- Previous message: Tibor Karaszi: "Re: From BAD To WORSE: A Disasterous Delete"
- In reply to: jonjo: "Re: stored procedure"
- Next in thread: jonjo: "Re: stored procedure"
- Reply: jonjo: "Re: stored procedure"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 29 Oct 2004 16:17:58 +0200
The big question boils down to design. For what purpose do you create these databases? Are you a
hosting company that allow your customers to store whatever tables etc they want inside the
databases you create for them? Of do you want to keep track of your customers , if so, you shouldn't
create a new database for each customer (and not even a table for each customer).
-- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ "jonjo" <jonjo@discussions.microsoft.com> wrote in message news:B1A2F0C6-7B66-48A0-9A86-7861A7720E1A@microsoft.com... > Hi > > Thanks for the reply. Perhaps you can think of a better way for me to do > this. Basically I want to automate the creation of databases that might just > have a different ending e.g. accounts1, accounts2, accounts3 etc. I thought I > might be able to do this by creating a parameterised sp which would let the > user just type in the name of the db they want to create and then run the sp > to create that db. > > Is that possible or is there a better way? > > Thanks > > "Tibor Karaszi" wrote: > >> Seems you can't have a variable for the database name in CREATE DATABASE. You would have to build >> the whole CRE¤ATE DATABASE command in a variable and use EXEC(@sql) to execute the statement. >> >> But why do you want to create a database from inside a stored procedure? >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> >> >> "jonjo" <jonjo@discussions.microsoft.com> wrote in message >> news:60BC3CF7-432C-4AA5-B978-5B7D5663CC49@microsoft.com... >> > Hi >> > >> > I have taken away the USE statement but when I take the brackets away from >> > the @dbname variable in the CREATE DATABASE statement it gives me :msg 170 >> > incorrect syntax near dbname >> > >> > >> > >> > "Tibor Karaszi" wrote: >> > >> >> 1. The USE doesn't do anything for you. The database doesn't exist so the use will fail, and >> >> the >> >> use >> >> is done inside EXEC so it is in its own scope. >> >> >> >> 2. Don't put brackets around the variable holding the database name in the CREATE DATABASE >> >> command. >> >> >> >> If you still don't get it to work, please also post the error message. >> >> >> >> -- >> >> Tibor Karaszi, SQL Server MVP >> >> http://www.karaszi.com/sqlserver/default.asp >> >> http://www.solidqualitylearning.com/ >> >> >> >> >> >> "jonjo" <jonjo@discussions.microsoft.com> wrote in message >> >> news:5182DD96-53AD-4223-9352-A944AFE1F47A@microsoft.com... >> >> > Hi >> >> > >> >> > I'm making a bit of progress but I think I'm passing the wrong parameters in >> >> > the procedure. This is what I am trying to do but when I enter the parameter >> >> > it is not feeding thru? I'm obviously doing something wrong along the line so >> >> > any help would be appreciated. >> >> > >> >> > CREATE PROCEDURE TEST2 @dbname nvarchar(10) AS >> >> > BEGIN >> >> > DECLARE @quotedbname nvarchar(10) >> >> > SET @quotedbname= quotename (@dbname) >> >> > >> >> > EXEC (' >> >> > USE '+ @quotedbname + '') >> >> > >> >> > CREATE DATABASE [@dbname] ON (NAME = N'@dbname_Data', FILENAME = >> >> > N'e:\Program Files\Microsoft SQL Server\MSSQL\data\@dbname_Data.MDF' , SIZE = >> >> > 1, FILEGROWTH = 10%) LOG ON (NAME = N'@dbname_Log', FILENAME = N'e:\Program >> >> > Files\Microsoft SQL Server\MSSQL\data\@dbname_Log.LDF' , SIZE = 1, FILEGROWTH >> >> > = 10%) >> >> > COLLATE Latin1_General_CI_AS >> >> > >> >> > exec sp_dboption N'@dbname', N'autoclose', N'false' >> >> > >> >> > exec sp_dboption N'@dbname', N'bulkcopy', N'false' >> >> > >> >> > exec sp_dboption N'@dbname', N'trunc. log', N'true' >> >> > >> >> > exec sp_dboption N'@dbname', N'torn page detection', N'false' >> >> > >> >> > exec sp_dboption N'@dbname', N'read only', N'false' >> >> > >> >> > exec sp_dboption N'@dbname', N'dbo use', N'false' >> >> > >> >> > exec sp_dboption N'@dbname', N'single', N'false' >> >> > >> >> > exec sp_dboption N'@dbname', N'autoshrink', N'false' >> >> > >> >> > exec sp_dboption N'@dbname', N'ANSI null default', N'false' >> >> > >> >> > exec sp_dboption N'@dbname', N'recursive triggers', N'false' >> >> > >> >> > exec sp_dboption N'@dbname', N'ANSI nulls', N'false' >> >> > >> >> > exec sp_dboption N'@dbname', N'concat null yields null', N'false' >> >> > >> >> > exec sp_dboption N'@dbname', N'cursor close on commit', N'false' >> >> > >> >> > exec sp_dboption N'@dbname', N'default to local cursor', N'false' >> >> > >> >> > exec sp_dboption N'@dbname', N'quoted identifier', N'false' >> >> > >> >> > exec sp_dboption N'@dbname', N'ANSI warnings', N'false' >> >> > >> >> > exec sp_dboption N'@dbname', N'auto create statistics', N'false' >> >> > >> >> > exec sp_dboption N'@dbname', N'auto update statistics', N'false' >> >> > >> >> > >> >> > if( ( (@@microsoftversion / power(2, 24) = 8) and (@@microsoftversion & >> >> > 0xffff >= 724) ) or ( (@@microsoftversion / power(2, 24) = 7) and >> >> > (@@microsoftversion & 0xffff >= 1082) ) ) >> >> > exec sp_dboption N'@dbname', N'db chaining', N'false' >> >> > END >> >> > GO >> >> > >> >> > "GYK" wrote: >> >> > >> >> >> You need to pass database name as a variable... Following script might be >> >> >> useful for you.. >> >> >> >> >> >> CREATE PROCEDURE p_test_proc >> >> >> @dbname nvarchar(256) >> >> >> AS >> >> >> BEGIN >> >> >> -- Quote the database name with brackets >> >> >> DECLARE @quoteddbname nvarchar(256) >> >> >> SET @quoteddbname = quotename( @dbname ) >> >> >> >> >> >> EXEC(' >> >> >> USE '+ @quoteddbname +' >> >> >> DECLARE ..................... >> >> >> ............................. >> >> >> ............................ ') >> >> >> END >> >> >> >> >> >> HTH >> >> >> GYK >> >> >> >> >> >> >> >> >> "jonjo" wrote: >> >> >> >> >> >> > Hi >> >> >> > >> >> >> > I'm trying to create a stored procedure that will create a blank database >> >> >> > and then create certain tables. The create database works fine but I then >> >> >> > want to create the tables in the database which I have just created. I tried >> >> >> > putting in the 'use accounts' (accounts being the name of the db I just >> >> >> > created) in the stored procedure but this is obviously not allowed as a msg >> >> >> > returns:- >> >> >> > >> >> >> > error 154 a use database statement is not allowed in a procedure or trigger >> >> >> > >> >> >> > If I run the script in QA it works without any problem. Any ideas as how to >> >> >> > get round this? >> >> >> > >> >> >> > Thanks in advance >> >> >> > >> >> >> > >> >> >> >> >> >> >> >> >>
- Next message: Adam Machanic: "Re: deadlock/blocking in sql server"
- Previous message: Tibor Karaszi: "Re: From BAD To WORSE: A Disasterous Delete"
- In reply to: jonjo: "Re: stored procedure"
- Next in thread: jonjo: "Re: stored procedure"
- Reply: jonjo: "Re: stored procedure"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
