Re: stored procedure
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 10/29/04
- Next message: Brian Moran: "Re: Statistics Time"
- Previous message: Dan: "RE: Can't manage SQL"
- 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 15:30:24 +0200
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: Brian Moran: "Re: Statistics Time"
- Previous message: Dan: "RE: Can't manage SQL"
- 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
|
