Re: stored procedure
From: jonjo (jonjo_at_discussions.microsoft.com)
Date: 10/29/04
- Next message: Leila: "Join Styles"
- Previous message: Brian Moran: "Re: Index Tuning Wizard"
- 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 06:44:06 -0700
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: Leila: "Join Styles"
- Previous message: Brian Moran: "Re: Index Tuning Wizard"
- 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
|
