Re: stored procedure
From: jonjo (jonjo_at_discussions.microsoft.com)
Date: 10/29/04
- Next message: Kjell Gunnarsson: "How to recover transaction log from failed database ?."
- Previous message: Liz: "SQL Job stopping - sp_help_job results"
- In reply to: Tibor Karaszi: "Re: stored procedure"
- Next in thread: Tibor Karaszi: "Re: stored procedure"
- Reply: Tibor Karaszi: "Re: stored procedure"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 29 Oct 2004 07:28:06 -0700
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: Kjell Gunnarsson: "How to recover transaction log from failed database ?."
- Previous message: Liz: "SQL Job stopping - sp_help_job results"
- In reply to: Tibor Karaszi: "Re: stored procedure"
- Next in thread: Tibor Karaszi: "Re: stored procedure"
- Reply: Tibor Karaszi: "Re: stored procedure"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
