Re: stored procedure
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 10/29/04
- Next message: Tibor Karaszi: "Re: From BAD To WORSE: A Disasterous Delete"
- Previous message: Tibor Karaszi: "Re: How to recover transaction log from failed database ?."
- 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:30:09 +0200
What I'm questioning is the design behind the need to create new databases all the time. Why do they
do this?
-- 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:E401618E-A5E6-4159-9FE9-A09E0C06B453@microsoft.com... > No I am not a hosting company. I want to do this to enable an internal dept > in my organisation to automate the creation of databases. Currently they have > to create new ones all the time and I just want to save them some time. > > "Tibor Karaszi" wrote: > >> 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: Tibor Karaszi: "Re: From BAD To WORSE: A Disasterous Delete"
- Previous message: Tibor Karaszi: "Re: How to recover transaction log from failed database ?."
- 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
|
Loading
