ALTER DATABASE dynamic SQL

I am in a bind.
I am writing a stored proc that needs to change the logical name of a
database as one step in the process. The issue is that I can't seem to pass
the database in a variable to the Alter Database command.
What are the options I have for making this work?
Code snippet:
DECLARE @.NewDB
@.NewDB = 'TestDB'
@.NewLogicalName = 'TestDB'
@.OldLogicalName = 'TemplateDB'
ALTER DATABASE @.NewDB <-- This generates an error
MODIFY FILE (
NAME = @.OldLogicalName,
NEWNAME = @.NewLogicalName
)
Thanks!!!!!
RSHRSH wrote:
> I am in a bind.
> I am writing a stored proc that needs to change the logical name of a
> database as one step in the process. The issue is that I can't seem to pa
ss
> the database in a variable to the Alter Database command.
> What are the options I have for making this work?
> Code snippet:
> DECLARE @.NewDB
> @.NewDB = 'TestDB'
> @.NewLogicalName = 'TestDB'
> @.OldLogicalName = 'TemplateDB'
> ALTER DATABASE @.NewDB <-- This generates an error
> MODIFY FILE (
> NAME = @.OldLogicalName,
> NEWNAME = @.NewLogicalName
> )
> Thanks!!!!!
> RSH
You will have to use EXEC for that. For example:
EXEC (@.alter_stmt)
Where @.alter_stmt is a string containing your ALTER DATABASE statement.
But why would you want to do such a thing in a proc?
David Portas
SQL Server MVP
--|||Thanks.
It is a long story but basically we have template databases and when a new
company is created we backup the template database and restore it to a new
name. It works good and everything changes except the Logical name so i
needed a way to change the logical name to the new database name.
This stems from the fact that our application was built using Access
databases in which this was standard procedure. We are moving over to SQL
and unfortunately we can't justify writing a few thousand lines of
application code, so we are stuck bending a few best practices here and
there to initially get things running.
Thanks,
RSH
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1133963892.610144.63210@.z14g2000cwz.googlegroups.com...
> RSH wrote:
>
> You will have to use EXEC for that. For example:
> EXEC (@.alter_stmt)
> Where @.alter_stmt is a string containing your ALTER DATABASE statement.
> But why would you want to do such a thing in a proc?
> --
> David Portas
> SQL Server MVP
> --
>|||RSH wrote:
> This stems from the fact that our application was built using Access
> databases in which this was standard procedure. We are moving over to SQL
> and unfortunately we can't justify writing a few thousand lines of
> application code
Experience suggests this is a recipe for failure. Virtually every
Access database I've ever seen is incompatible with good design in SQL
Server (or in most other database systems for that matter!). Wait until
you can justify rewriting it is my advice. No solution at all is
frequently better than a bad solution.
Good luck.
David Portas
SQL Server MVP
--|||> It is a long story but basically we have template databases and when a new
> company is created we backup the template database and restore it to a new
> name.
I use model for this. Life is much simpler (except when I need to create a
database that's not based on the template).
A|||RSH (way_beyond_oops@.yahoo.com) writes:
> It is a long story but basically we have template databases and when a new
> company is created we backup the template database and restore it to a new
> name. It works good and everything changes except the Logical name so i
> needed a way to change the logical name to the new database name.
But why would you worry about the logical name? If all files in all
databases have the same logical name, so what? Easier to manage, because
you always know what the names are. :-)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||If the database named SALES_GA_2005 had a logical file name of SALES_FL and
physical name of SALES_2001.MDF, it could be confusing for the administrator
who manages the server.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9725AFA87588BYazorman@.127.0.0.1...
> RSH (way_beyond_oops@.yahoo.com) writes:
> But why would you worry about the logical name? If all files in all
> databases have the same logical name, so what? Easier to manage, because
> you always know what the names are. :-)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||I fixed it by calling the stored procedure Alter Database from my
application rather than parameterize it in a SP.
I agree in a perfect world it would be nice to re layout the DBs from
scratch and modify the application logic to utilize it...but the fact of the
matter is the people with the wallets want a solution and while it isn't
perfect it will work for the meantime. We are going to be porting our
application to .Net next year and that is when we are going to be revamping
things to take advantage of SQL server but until then we have to keep
generating revenue.
The logical name needs to be changed because I will still be referencing the
template database by name and I can't very well have 100 logical names all
the same...that would be quite an administrative nightmare.
Thanks for the help and insight.
:-)
"JT" <someone@.microsoft.com> wrote in message
news:eB$Odu0%23FHA.3464@.TK2MSFTNGP15.phx.gbl...
> If the database named SALES_GA_2005 had a logical file name of SALES_FL
> and physical name of SALES_2001.MDF, it could be confusing for the
> administrator who manages the server.
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9725AFA87588BYazorman@.127.0.0.1...
>|||> If the database named SALES_GA_2005 had a logical file name of SALES_FL
> and physical name of SALES_2001.MDF, it could be confusing for the
> administrator who manages the server.
Well, the intent was probably more like giving the logical file names a more
generic descriptor, like "data" and "log"... of course the MDF/LDF file
names would have to be unique, unless each pair is in its own folder.|||RSH wrote:
> The logical name needs to be changed because I will still be referencing t
he
> template database by name and I can't very well have 100 logical names all
> the same...that would be quite an administrative nightmare.
>
Are you sure you understand what the logical file name is? A database
is never referenced by its logical file name (for one reason because
there are at least two logical file names per database). The only time
you reference the logical name is if you need to change one of the file
properties such as size, location, growth, etc. In that case you always
need the database name as well. So I can't make sense of why it would
be an "administrative nightmare" to have the same names in each DB. In
some ways it makes things easier if you do this stuff dynamically.
I do agree that it makes sense to have a good naming convention but I'm
not convinced you understand what the logical file name is for.
David Portas
SQL Server MVP
--