query database table: March 2012

Friday, March 30, 2012

How to rename physical file name

I have a database, which consists of two physical files db1.mdf,
db1_log.ldf, both locating on the same folder. I want to copy both the files
to db2.mdf, db2_log.ldf (in the same folder) and attach as DB2. I know the
commands to attach a database with different name. I want to know, how to
change db1.mdf to db2.mdf
TIA
J JustinJustin
I prefer RESTORE DATABASE from QA rather than EM. In the QA just before a
running change the names of the files
RESTORE DATABASE MyDatabase FROM DISK='C:\MyDatabase20050206.bak'
WITH RECOVERY,
MOVE MyDatabase_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\ChangeItHere.mdf',
MOVE MyDatabase _Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\ChangeItHere_log.ldf'
"J Justin" <justin@.gjsoft.com> wrote in message
news:eRxy9uXdFHA.3184@.TK2MSFTNGP15.phx.gbl...
> I have a database, which consists of two physical files db1.mdf,
> db1_log.ldf, both locating on the same folder. I want to copy both the
files
> to db2.mdf, db2_log.ldf (in the same folder) and attach as DB2. I know the
> commands to attach a database with different name. I want to know, how to
> change db1.mdf to db2.mdf
> TIA
> J Justin
>|||Thanks for your quick reply. I don't have BAK file, rather I have the raw
mdf and ldf files. Is there any command now?
Regards
J Justin
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%233lXN0XdFHA.2688@.TK2MSFTNGP14.phx.gbl...
> Justin
> I prefer RESTORE DATABASE from QA rather than EM. In the QA just before a
> running change the names of the files
> RESTORE DATABASE MyDatabase FROM DISK='C:\MyDatabase20050206.bak'
> WITH RECOVERY,
> MOVE MyDatabase_Data' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ChangeItHere.mdf',
> MOVE MyDatabase _Log' TO 'C:\Program Files\Microsoft SQL
> Server\MSSQL\Data\ChangeItHere_log.ldf'
>
>
> "J Justin" <justin@.gjsoft.com> wrote in message
> news:eRxy9uXdFHA.3184@.TK2MSFTNGP15.phx.gbl...
>> I have a database, which consists of two physical files db1.mdf,
>> db1_log.ldf, both locating on the same folder. I want to copy both the
> files
>> to db2.mdf, db2_log.ldf (in the same folder) and attach as DB2. I know
>> the
>> commands to attach a database with different name. I want to know, how to
>> change db1.mdf to db2.mdf
>> TIA
>> J Justin
>>
>|||No, after dettaching database rename the files manual .
"J Justin" <justin@.gjsoft.com> wrote in message
news:%23$K7r9XdFHA.1684@.TK2MSFTNGP09.phx.gbl...
> Thanks for your quick reply. I don't have BAK file, rather I have the raw
> mdf and ldf files. Is there any command now?
> Regards
> J Justin
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%233lXN0XdFHA.2688@.TK2MSFTNGP14.phx.gbl...
> > Justin
> > I prefer RESTORE DATABASE from QA rather than EM. In the QA just before
a
> > running change the names of the files
> >
> > RESTORE DATABASE MyDatabase FROM DISK='C:\MyDatabase20050206.bak'
> > WITH RECOVERY,
> > MOVE MyDatabase_Data' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\ChangeItHere.mdf',
> > MOVE MyDatabase _Log' TO 'C:\Program Files\Microsoft SQL
> > Server\MSSQL\Data\ChangeItHere_log.ldf'
> >
> >
> >
> >
> >
> > "J Justin" <justin@.gjsoft.com> wrote in message
> > news:eRxy9uXdFHA.3184@.TK2MSFTNGP15.phx.gbl...
> >> I have a database, which consists of two physical files db1.mdf,
> >> db1_log.ldf, both locating on the same folder. I want to copy both the
> > files
> >> to db2.mdf, db2_log.ldf (in the same folder) and attach as DB2. I know
> >> the
> >> commands to attach a database with different name. I want to know, how
to
> >> change db1.mdf to db2.mdf
> >>
> >> TIA
> >>
> >> J Justin
> >>
> >>
> >
> >
>

How to rename instance

Hi,
I installed sql05 on a new box and I don't like the name I chose for the
instance.
How do I change it?
TIA,
Mike
Backup your databases, uninstall SQL Server, install it properly, then
restore your databases. There is no sp_rename_instance.
"Mike" <mikenospam@.yahoo.es> wrote in message
news:691977F7-8CB6-4107-B71E-6584B15311D4@.microsoft.com...
> Hi,
> I installed sql05 on a new box and I don't like the name I chose for the
> instance.
> How do I change it?
> TIA,
> Mike
>

How to rename instance

Hi,
I installed sql05 on a new box and I don't like the name I chose for the
instance.
How do I change it?
TIA,
MikeBackup your databases, uninstall SQL Server, install it properly, then
restore your databases. There is no sp_rename_instance.
"Mike" <mikenospam@.yahoo.es> wrote in message
news:691977F7-8CB6-4107-B71E-6584B15311D4@.microsoft.com...
> Hi,
> I installed sql05 on a new box and I don't like the name I chose for the
> instance.
> How do I change it?
> TIA,
> Mike
>

how to rename files before send files?

I'm using script task to rename files inside foreach loop files,code like shown below

Dim file As New System.IO.FileInfo(CStr(Dts.Variables("User::FileName").Value))

dim newname as string = Split(file.Name, "_")(0) & ".jpg"
Dts.Variables("User::outputname").Value = file.DirectoryName & "\" & newname

Then,i use ftp task to send files,but prompt "the variables User::outputname doesn't contains file path(s)"

I tried to use file system task to perform that,but failed either

Rename file operation in file system only can rename a file in a specified location,who can help me?

TIA

Have you looked at what outputname does contain? Does the file specified exist as it should for the FTP task?

The File System Task can use variables, which themselves can be used to supply expressions, see the EvaluateAsExpression property and set the Expression property. This way you can use dynamic paths in the File System Task.

sql

How to Rename Database Files

Is there a way to totally rename a databases? I would like to change the
name of the database - like with sp_renamedb and the assosciated files so
that they still match. Is there an easy way to do this? I would really like
a TSQL script to do this if possible.
Thanks in advance.Hi Tim
What version are you using? Please always mention that right up front.
In SQL Server 2000 you can change the logical file names using ALTER
DATABASE, but in SQL Server 7 you cannot.
To change the physical files names, there is no way to do this in TSQL
(except for tempdb), so it would be tricky to do this in a script.
The steps to change physical file names are:
Detach the database
rename the files at the operating system
Attach the database, specifying the new file names
Check the docs for sp_attachdb for full details.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tim Bird" <tsbdoulos@.nospambtopenworld.com> wrote in message
news:uDWhPZUKEHA.556@.TK2MSFTNGP10.phx.gbl...
> Is there a way to totally rename a databases? I would like to change the
> name of the database - like with sp_renamedb and the assosciated files so
> that they still match. Is there an easy way to do this? I would really
like
> a TSQL script to do this if possible.
> Thanks in advance.
>|||Hi John
Note that this will not change the logical file names. That must be done
separately and in SQL Server 7 it is not possible to change the logical file
names at all.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
news:851FB97A-324C-40DD-95D9-3704D66EB621@.microsoft.com...
> Tim
> Detach the database. Rename the windows files and attach as whatever name
you like.
> Regards
> John|||Hi,
In SQL 2000 the logical file name can be changed by using the below command.
The below step is not possible in SQL 7
ALTER DATABASE
MODIFY FILE
(NAME = logical_file_name,
NEWNAME = new_logical_name)
How to change the physical name in both SQL 2000 / SQL 7
1. detach the database using sp_detach_db <dbname>
2. Rename the physical files to new name
3. attach the database using sp_attach_db 'dbname','new physical MDF name
with path','new physical LDF name with path'
Thanks
Hari
MCDBA
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e$uJwLVKEHA.2756@.TK2MSFTNGP10.phx.gbl...
> Hi John
> Note that this will not change the logical file names. That must be done
> separately and in SQL Server 7 it is not possible to change the logical
file
> names at all.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
> news:851FB97A-324C-40DD-95D9-3704D66EB621@.microsoft.com...
> > Tim
> >
> > Detach the database. Rename the windows files and attach as whatever
name
> you like.
> >
> > Regards
> >
> > John
>|||Yes, I just said that John's approach would not change the logical file
names. Earlier in the thread I told the original poster he could use ALTER
DATABASE to change the logical file names.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:urPajkVKEHA.3704@.TK2MSFTNGP11.phx.gbl...
> Hi,
> In SQL 2000 the logical file name can be changed by using the below
command.
> The below step is not possible in SQL 7
> ALTER DATABASE
> MODIFY FILE
> (NAME = logical_file_name,
> NEWNAME = new_logical_name)
> How to change the physical name in both SQL 2000 / SQL 7
> 1. detach the database using sp_detach_db <dbname>
> 2. Rename the physical files to new name
> 3. attach the database using sp_attach_db 'dbname','new physical MDF name
> with path','new physical LDF name with path'
> Thanks
> Hari
> MCDBA
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e$uJwLVKEHA.2756@.TK2MSFTNGP10.phx.gbl...
> > Hi John
> >
> > Note that this will not change the logical file names. That must be done
> > separately and in SQL Server 7 it is not possible to change the logical
> file
> > names at all.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
> > news:851FB97A-324C-40DD-95D9-3704D66EB621@.microsoft.com...
> > > Tim
> > >
> > > Detach the database. Rename the windows files and attach as whatever
> name
> > you like.
> > >
> > > Regards
> > >
> > > John
> >
> >
>|||Kale
Maybe I am missing something here, I did what I described and it worked just fine. (This is in 2000, I did not have a 7 environment available.) I created a database called 'Fred'. I detached 'Fred'. I renamed the MDF and LDF files to 'Pete'. I attached database as 'Pete'. Database 'Pete' seems to be just fine. What did I miss
Regard
John|||It depends. I understood the OP to say he wanted to change the file names to
match the new database name, and I assume he wanted the logical filenames to
match also. What good is having a database named Pete, with physical files
named Pete, but logical files named Fred?
I'm not surprised the db is working fine for you, but I think your logical
file names are still Fred. I understood the OP wanted all names changed. He
didn't just want it to 'work', he wanted it to 'look right' also.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
news:0C49A43F-4B84-472B-88C0-29F561A56244@.microsoft.com...
> Kalen
> Maybe I am missing something here, I did what I described and it worked
just fine. (This is in 2000, I did not have a 7 environment available.) I
created a database called 'Fred'. I detached 'Fred'. I renamed the MDF and
LDF files to 'Pete'. I attached database as 'Pete'. Database 'Pete' seems to
be just fine. What did I miss?
> Regards
> John|||"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e%23LFfB$KEHA.620@.TK2MSFTNGP10.phx.gbl...
> It depends. I understood the OP to say he wanted to change the file names
to
> match the new database name, and I assume he wanted the logical filenames
to
> match also. What good is having a database named Pete, with physical files
> named Pete, but logical files named Fred?
> I'm not surprised the db is working fine for you, but I think your logical
> file names are still Fred. I understood the OP wanted all names changed.
He
> didn't just want it to 'work', he wanted it to 'look right' also.
You are correct. That was my intention. Though it really had to be a program
based solution that an end user of our system could perform. In the end I
have gone for a slightly different solution. Unless anybody can think of a
way to get the SQL Server itself to rename files. As this is will be run
from a client machine and not the server.
Thanks for all your replies anyone who has helped.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
> news:0C49A43F-4B84-472B-88C0-29F561A56244@.microsoft.com...
> > Kalen
> >
> > Maybe I am missing something here, I did what I described and it worked
> just fine. (This is in 2000, I did not have a 7 environment available.)
I
> created a database called 'Fred'. I detached 'Fred'. I renamed the MDF and
> LDF files to 'Pete'. I attached database as 'Pete'. Database 'Pete' seems
to
> be just fine. What did I miss?
> >
> > Regards
> >
> > John
>|||Tim
Once the database is detached, SQL Server can rename the physical files
using xp_cmdshell. For an end-user to use this, it would have to be in a
stored procedure that you have granted the user permission to execute.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tim Bird" <tsbdoulos@.nospambtopenworld.com> wrote in message
news:OBDshsCLEHA.1348@.TK2MSFTNGP12.phx.gbl...
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23LFfB$KEHA.620@.TK2MSFTNGP10.phx.gbl...
> > It depends. I understood the OP to say he wanted to change the file
names
> to
> > match the new database name, and I assume he wanted the logical
filenames
> to
> > match also. What good is having a database named Pete, with physical
files
> > named Pete, but logical files named Fred?
> >
> > I'm not surprised the db is working fine for you, but I think your
logical
> > file names are still Fred. I understood the OP wanted all names changed.
> He
> > didn't just want it to 'work', he wanted it to 'look right' also.
> You are correct. That was my intention. Though it really had to be a
program
> based solution that an end user of our system could perform. In the end I
> have gone for a slightly different solution. Unless anybody can think of a
> way to get the SQL Server itself to rename files. As this is will be run
> from a client machine and not the server.
> Thanks for all your replies anyone who has helped.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "John Bandettini" <anonymous@.discussions.microsoft.com> wrote in message
> > news:0C49A43F-4B84-472B-88C0-29F561A56244@.microsoft.com...
> > > Kalen
> > >
> > > Maybe I am missing something here, I did what I described and it
worked
> > just fine. (This is in 2000, I did not have a 7 environment available.)
> I
> > created a database called 'Fred'. I detached 'Fred'. I renamed the MDF
and
> > LDF files to 'Pete'. I attached database as 'Pete'. Database 'Pete'
seems
> to
> > be just fine. What did I miss?
> > >
> > > Regards
> > >
> > > John
> >
> >
>|||Thanks. That is pretty much what I came up with. Anoyingly I have got all
this figured out just as the people who decide have decided that this
feature is no longer needed in the software. Isn't that always the way
?!!!!!
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:e2FSTXELEHA.2556@.TK2MSFTNGP11.phx.gbl...
> Tim
> Once the database is detached, SQL Server can rename the physical files
> using xp_cmdshell. For an end-user to use this, it would have to be in a
> stored procedure that you have granted the user permission to execute.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Tim Bird" <tsbdoulos@.nospambtopenworld.com> wrote in message
> news:OBDshsCLEHA.1348@.TK2MSFTNGP12.phx.gbl...
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:e%23LFfB$KEHA.620@.TK2MSFTNGP10.phx.gbl...
> > > It depends. I understood the OP to say he wanted to change the file
> names
> > to
> > > match the new database name, and I assume he wanted the logical
> filenames
> > to
> > > match also. What good is having a database named Pete, with physical
> files
> > > named Pete, but logical files named Fred?
> > >
> > > I'm not surprised the db is working fine for you, but I think your
> logical
> > > file names are still Fred. I understood the OP wanted all names
changed.
> > He
> > > didn't just want it to 'work', he wanted it to 'look right' also.
> >
> > You are correct. That was my intention. Though it really had to be a
> program
> > based solution that an end user of our system could perform. In the end
I
> > have gone for a slightly different solution. Unless anybody can think of
a
> > way to get the SQL Server itself to rename files. As this is will be run
> > from a client machine and not the server.
> >
> > Thanks for all your replies anyone who has helped.
> >
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "John Bandettini" <anonymous@.discussions.microsoft.com> wrote in
message
> > > news:0C49A43F-4B84-472B-88C0-29F561A56244@.microsoft.com...
> > > > Kalen
> > > >
> > > > Maybe I am missing something here, I did what I described and it
> worked
> > > just fine. (This is in 2000, I did not have a 7 environment
available.)
> > I
> > > created a database called 'Fred'. I detached 'Fred'. I renamed the MDF
> and
> > > LDF files to 'Pete'. I attached database as 'Pete'. Database 'Pete'
> seems
> > to
> > > be just fine. What did I miss?
> > > >
> > > > Regards
> > > >
> > > > John
> > >
> > >
> >
> >
>

How to rename an instance

I have a machine with both SQL Server 2000 and SQL Server 2005. I found
out that I cannot use an instance name for SQL Server 2000 that has
already been used by SQL Server 2005.
I need to rename the instance named "APP_DB" in SQL Server 2005 to be
"APP_DB05", and use "APP_DB" for SQL Server 2000. That way I don't have
to uninstall "APP_DB" from SQL Server 2005 because I need it in the future.
So, how to rename an instance? Thanks.Hi
I don't know of a way to do this other than stop the services for the SQL
2005 instance of APP_DB and install a new instance. You can detach/attach
from one instance to the other. This is not going to save you much and will
use up disc space.
John
"q_test" <jluost1@.yahoo.com> wrote in message
news:4443EC3F.7080901@.yahoo.com...
>I have a machine with both SQL Server 2000 and SQL Server 2005. I found out
>that I cannot use an instance name for SQL Server 2000 that has already
>been used by SQL Server 2005.
> I need to rename the instance named "APP_DB" in SQL Server 2005 to be
> "APP_DB05", and use "APP_DB" for SQL Server 2000. That way I don't have to
> uninstall "APP_DB" from SQL Server 2005 because I need it in the future.
> So, how to rename an instance? Thanks.
>

How to rename an instance

how to rename a temp table

EXEC sp_rename '#customers', '#custs'
result with error :Invalid object name '#customers'.
how to rename a temp table?Sam
sp_rename
Changes the name of a user-created object (for example, table, column, or
user-defined data type) in the current database.
"Sam" <focus10@.zahav.net.il> wrote in message
news:ecE3BxurFHA.2008@.TK2MSFTNGP10.phx.gbl...
> EXEC sp_rename '#customers', '#custs'
> result with error :Invalid object name '#customers'.
> how to rename a temp table?
>
>|||Sam wrote:
> EXEC sp_rename '#customers', '#custs'
> result with error :Invalid object name '#customers'.
> how to rename a temp table?
I'm not sure you can rename a temporary table. Why do you need to do
this?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||so how can i rename the temp table?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OziOz4urFHA.240@.tk2msftngp13.phx.gbl...
> Sam
> sp_rename
> Changes the name of a user-created object (for example, table, column, or
> user-defined data type) in the current database.
>
>
>
> "Sam" <focus10@.zahav.net.il> wrote in message
> news:ecE3BxurFHA.2008@.TK2MSFTNGP10.phx.gbl...
>|||I don't think there's a documented way to do it.
Why would you ever want to rename a temp table? This seems especially
pointless with a local temp table, which after all is intended
precisely to give you a locally-scoped name for the table. I'm sure if
you explain your requirement we can suggest a better solution to avoid
doing this.
David Portas
SQL Server MVP
--|||Uri, I believe sp_rename disallows renaming temp objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OziOz4urFHA.240@.tk2msftngp13.phx.gbl...
> Sam
> sp_rename
> Changes the name of a user-created object (for example, table, column, or
> user-defined data type) in the current database.
>
>
>
> "Sam" <focus10@.zahav.net.il> wrote in message
> news:ecE3BxurFHA.2008@.TK2MSFTNGP10.phx.gbl...
>|||select * into #NewName from #OldName
drop table #OldName
--Brian
(Please reply to the newsgroups only.)
"Sam" <focus10@.zahav.net.il> wrote in message
news:uBSEB%23urFHA.1168@.TK2MSFTNGP11.phx.gbl...
> so how can i rename the temp table?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OziOz4urFHA.240@.tk2msftngp13.phx.gbl...
>|||sp_rename renames the table in the current database. a temp table is created
in tempdb database and tempdb doesnt have sp_rename stored procedure.
the table gets deleted immediately after the session is closed, so why do u
want to
rename it
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Sam" wrote:

> EXEC sp_rename '#customers', '#custs'
> result with error :Invalid object name '#customers'.
> how to rename a temp table?
>
>
>|||Hi, Dan
That was exactly may point.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OpFv7%23urFHA.2540@.TK2MSFTNGP09.phx.gbl...
> Uri, I believe sp_rename disallows renaming temp objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OziOz4urFHA.240@.tk2msftngp13.phx.gbl...
>|||> how to rename a temp table?
What would be the point?

How to rename a table ?

Hi all.

I'm porting an application from eVB-ADOCE to VB.Net-SQL CE

In the old application there are some SQL statements like this:

"ALTER TABLE OldTable TO NewTable"

Is there an equivalent instruction or a simple way to rename a table?

I was not able to find anything simple to do the same.

I really need NOT to do something like

SELECT * INTO NewTable FROM OldTable

because the resulting table could go outside memory and/or spend too much time in elaboration.

Please let me know what I can do.

Many thanks !

As far as I know, there is no such option from SQL. But you can rename a table (and a table column) using OLE DB:

QA: How do I rename a SQL CE table?

|||This is very interesting, and I read your article, but I'm so ignorant in C++ that I've not been able to translate your code in VB.

|||

Don't worry - I'm working on an article that will allow you to do this from .NET CF on a device. I will post this article either today or tomorrow.

|||

Jo?o Paulo Figueira wrote:

Don't worry - I'm working on an article that will allow you to do this from .NET CF on a device. I will post this article either today or tomorrow.

That will be wonderful !

|||

As promised, here is the article:

Renaming a SQL CE Table From a .NET CF Application

|||

Joao Paulo,

I don't know how to say "thank you" for your kindness. I'm going to use your work just now.

If you come to Milano (Italy), please let me know ! I shall organize a good dinner for you

Thank you very much again !

How to rename a Olap Cube (AS2000)

I would like copy an existing production cube to a test environnment in the same server by changing the destination name.

I found nothing to rename the cube before archive or during the restore.

Changing the name of the archive does not change the restauration name.

Hi Georges

Copy and paste the cube with a new name (right click) then reload the cube.

Chris

How to rename a file in SSIS

I want to rename a file that is a variable to a fixed file name in a specific folder in SQL Server System Integration Services using the File System Task Editor.

Example: File1.txt to Users.txt or File2 to Users.txt. The source file is a variable the destination file is not. Here is an example of the File System Task Editor

HOW DO I SPECIFY A FIXED OR VARIABLE FOLDER NAME WITH A SPECIFIC FILE NAME???

File System Task Editor

Properties Value

Destination Connection
IsDestinationPathVariable True
DestinationVariable User::ArchivePath
OverWriteDestination True

General
Name Rename a file
Description File System Task

Operation
Operation Rename file

Source Connection
IsSourcePathVariable True
SourceVariable User::ImportFilePath

Thanks

There is more than one way to do it, the last link is run by DTS/SSIS experts you will find most of what you need. Hope this helps.

http://blogs.conchango.com/jamiethomson/archive/2005/09/14/2149.aspx

http://msdn2.microsoft.com/en-us/library/ms140185.aspx

http://www.sqlis.com

How to rename a database?

every time I try to rename my database I face an error

I want to rename the database file phisically not just changing logigal name with

Alter Database <database name> modify name command

please step by step tell me what to do

whether with using TSql or Managment Studio

thanks

detach the database

rename the files

then "attach as" new database name

sql

How to rename a database?

every time I try to rename my database I face an error

I want to rename the database file phisically not just changing logigal name with

Alter Database <database name> modify name command

please step by step tell me what to do

whether with TSql or Managment Studio

thanks

Workaround.

1. Deattach database files.
2. Rename physical files.
3. Attaching database files again and specify names you want.

you can do all this from SQL Management Studio 2005.

- Jignesh Desai

|||

Best way to do bit Your problem:

Use command:

sp_renamedb <old_name>, <new_name>

That's all. ;)

How to rename a database?

I want to rename the phisical file of the dabase for instance rename <MyDatabase.mdf> to <Renameddatabase.mdf>

every time I do so whether directly renamin the file or by

using Alter Database command I face an error

note that I don't want to logically rename the database

please tell me step by step what to do

thanks in advance

Easiest way is to take a backup of your existing database and restore it with renaming the mdf files. Have you read the books online (help file)?

Are any users (other than yourself) connected to the database while you try to rename the file?

ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name , FILENAME = 'new_path\os_file_name' )Have you tried this command?

How to rename a database without detaching it?

Hello,
Is there an SP for renaming a database without detaching it? Or is there a
method or feature in Enterprise Manager for doing this? How can I do this?
Thanks,
RichYou can use the ALTER DATABASE or the (older technique) sp_renamedb.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0F20173D-603E-489B-AB85-6E789F924192@.microsoft.com...
> Hello,
> Is there an SP for renaming a database without detaching it? Or is there
a
> method or feature in Enterprise Manager for doing this? How can I do this
?
> Thanks,
> Rich
>|||try this.. not sure of the syntax though :)
alter database <old_db_name> modify name <new_db_name>|||Look up sp_renamedb in Books Online.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:0F20173D-603E-489B-AB85-6E789F924192@.microsoft.com...
> Hello,
> Is there an SP for renaming a database without detaching it? Or is there
> a
> method or feature in Enterprise Manager for doing this? How can I do
> this?
> Thanks,
> Rich
>|||small change.. sorry abt that
alter database <old_db_name> modify name = <new_db_name>
--
"Omnibuzz" wrote:

> try this.. not sure of the syntax though :)
> alter database <old_db_name> modify name <new_db_name>
>|||Thank you all for your replies. My problem has been solved.
"Rich" wrote:

> Hello,
> Is there an SP for renaming a database without detaching it? Or is there
a
> method or feature in Enterprise Manager for doing this? How can I do this
?
> Thanks,
> Rich
>

How to rename a database

As caption, how to rename a database?sp_renamedb
It is documented in SQL Book Online :- tsqlref.chm::/ts_sp_ra-rz_3yle.htm
HTH. Ryan
"kei" <kei@.discussions.microsoft.com> wrote in message
news:65DE5F9E-A2DC-44B6-B3BC-06A0EDAFF7FC@.microsoft.com...
> As caption, how to rename a database?|||Also, as of SQL Server 2000, it is recommended to rename a database using AL
TER DATABASE (although
sp_renamedb still exists and is supported).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23kAEKBBHGHA.1288@.TK2MSFTNGP09.phx.gbl...
> sp_renamedb
> It is documented in SQL Book Online :- tsqlref.chm::/ts_sp_ra-rz_3yle.htm
> --
> HTH. Ryan
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:65DE5F9E-A2DC-44B6-B3BC-06A0EDAFF7FC@.microsoft.com...
>|||sp_helptext 'sp_renamedb', will give you clear picture of what sp_rename DB
does.
Its "Alter Database" itself whats hapenning inside sp_renamedb
Thanks,
Sree
"Tibor Karaszi" wrote:

> Also, as of SQL Server 2000, it is recommended to rename a database using
ALTER DATABASE (although
> sp_renamedb still exists and is supported).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%23kAEKBBHGHA.1288@.TK2MSFTNGP09.phx.gbl...
>|||> Its "Alter Database" itself whats hapenning inside sp_renamedb
It's true that sp_renamedb is basically just a wrapper for ALTER DATABASE so
you might as well eliminate the middle man and execute the command directly.
sp_renamedb will be removed in a future version of SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:16C5441C-5988-4B95-AD97-A05184B4964F@.microsoft.com...[vbcol=seagreen]
> sp_helptext 'sp_renamedb', will give you clear picture of what sp_rename
> DB
> does.
> Its "Alter Database" itself whats hapenning inside sp_renamedb
> Thanks,
> Sree
> "Tibor Karaszi" wrote:
>|||I agree with what you're saying, however i was surprised to see that BOL
suggests the opposite..
BOL - ALTER DATABASE
Remarks
To remove a database, use DROP DATABASE. To rename a database, use
sp_renamedb. For more information about decreasing the size of a database,
see DBCC SHRINKDATABASE.
tsqlref.chm::/ts_aa-az_4e5h.htm
HTH. Ryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OTlRGkCHGHA.3728@.tk2msftngp13.phx.gbl...
> It's true that sp_renamedb is basically just a wrapper for ALTER DATABASE
> so you might as well eliminate the middle man and execute the command
> directly. sp_renamedb will be removed in a future version of SQL Server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
> news:16C5441C-5988-4B95-AD97-A05184B4964F@.microsoft.com...
>|||>I agree with what you're saying, however i was surprised to see that BOL
>suggests the opposite..
It appears you are using the original RTM version of the SQL 2000 BOL rather
than the latest update. You can download the latest reference from
http://www.microsoft.com/downloads/...DisplayLang=en.
Here's what the latest SQL Server BOL says:
<Excerpt BOL=SQL2000" href="http://links.10026.com/?link=tsqlref.chm::/ts_aa-az_4e5h.htm">
To remove a database, use DROP DATABASE. To rename a database, use the
MODIFY NAME = new_dbname option with ALTER DATABASE. For more information
about decreasing the size of a database, see DBCC SHRINKDATABASE.
</Excerpt>
Also, from the SQL Server 2005 BOL.
<Excerpt BOL=SQL2005"
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/7dd9d4ff-20e1-4857
-9a8e-a5bff767cf76.htm">
Important:
This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature. Use ALTER DATABASE MODIFY NAME
instead. For more information, see ALTER DATABASE (Transact-SQL).
</Excerpt>
Even though you can still use sp_renamedb in SQL 2000 and SQL 2005, I think
it's best to break old habits.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:OLGYbqCHGHA.3936@.TK2MSFTNGP12.phx.gbl...
>I agree with what you're saying, however i was surprised to see that BOL
>suggests the opposite..
> BOL - ALTER DATABASE
> Remarks
> To remove a database, use DROP DATABASE. To rename a database, use
> sp_renamedb. For more information about decreasing the size of a database,
> see DBCC SHRINKDATABASE.
>
> tsqlref.chm::/ts_aa-az_4e5h.htm
>
> --
> HTH. Ryan
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OTlRGkCHGHA.3728@.tk2msftngp13.phx.gbl...
>|||You're correct... I'm on a loaner PC today, i assumed it had the BOL
update - it doesn't...
Thanks
HTH. Ryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ek6mwNDHGHA.208@.tk2msftngp13.phx.gbl...
> It appears you are using the original RTM version of the SQL 2000 BOL
> rather than the latest update. You can download the latest reference from
> http://www.microsoft.com/downloads/...DisplayLang=en.
> Here's what the latest SQL Server BOL says:
> <Excerpt BOL=SQL2000" href="http://links.10026.com/?link=tsqlref.chm::/ts_aa-az_4e5h.htm">
> To remove a database, use DROP DATABASE. To rename a database, use the
> MODIFY NAME = new_dbname option with ALTER DATABASE. For more information
> about decreasing the size of a database, see DBCC SHRINKDATABASE.
> </Excerpt>
> Also, from the SQL Server 2005 BOL.
> <Excerpt BOL=SQL2005"
> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/7dd9d4ff-20e1-48
57-9a8e-a5bff767cf76.htm">
> Important:
> This feature will be removed in a future version of Microsoft SQL Server.
> Avoid using this feature in new development work, and plan to modify
> applications that currently use this feature. Use ALTER DATABASE MODIFY
> NAME instead. For more information, see ALTER DATABASE (Transact-SQL).
> </Excerpt>
> Even though you can still use sp_renamedb in SQL 2000 and SQL 2005, I
> think it's best to break old habits.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:OLGYbqCHGHA.3936@.TK2MSFTNGP12.phx.gbl...
>

How to rename a database

As caption, how to rename a database?sp_renamedb
It is documented in SQL Book Online :- tsqlref.chm::/ts_sp_ra-rz_3yle.htm
--
HTH. Ryan
"kei" <kei@.discussions.microsoft.com> wrote in message
news:65DE5F9E-A2DC-44B6-B3BC-06A0EDAFF7FC@.microsoft.com...
> As caption, how to rename a database?|||Also, as of SQL Server 2000, it is recommended to rename a database using ALTER DATABASE (although
sp_renamedb still exists and is supported).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23kAEKBBHGHA.1288@.TK2MSFTNGP09.phx.gbl...
> sp_renamedb
> It is documented in SQL Book Online :- tsqlref.chm::/ts_sp_ra-rz_3yle.htm
> --
> HTH. Ryan
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:65DE5F9E-A2DC-44B6-B3BC-06A0EDAFF7FC@.microsoft.com...
>> As caption, how to rename a database?
>|||sp_helptext 'sp_renamedb', will give you clear picture of what sp_rename DB
does.
Its "Alter Database" itself whats hapenning inside sp_renamedb
Thanks,
Sree
"Tibor Karaszi" wrote:
> Also, as of SQL Server 2000, it is recommended to rename a database using ALTER DATABASE (although
> sp_renamedb still exists and is supported).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%23kAEKBBHGHA.1288@.TK2MSFTNGP09.phx.gbl...
> > sp_renamedb
> >
> > It is documented in SQL Book Online :- tsqlref.chm::/ts_sp_ra-rz_3yle.htm
> >
> > --
> > HTH. Ryan
> > "kei" <kei@.discussions.microsoft.com> wrote in message
> > news:65DE5F9E-A2DC-44B6-B3BC-06A0EDAFF7FC@.microsoft.com...
> >> As caption, how to rename a database?
> >
> >
>|||> Its "Alter Database" itself whats hapenning inside sp_renamedb
It's true that sp_renamedb is basically just a wrapper for ALTER DATABASE so
you might as well eliminate the middle man and execute the command directly.
sp_renamedb will be removed in a future version of SQL Server.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:16C5441C-5988-4B95-AD97-A05184B4964F@.microsoft.com...
> sp_helptext 'sp_renamedb', will give you clear picture of what sp_rename
> DB
> does.
> Its "Alter Database" itself whats hapenning inside sp_renamedb
> Thanks,
> Sree
> "Tibor Karaszi" wrote:
>> Also, as of SQL Server 2000, it is recommended to rename a database using
>> ALTER DATABASE (although
>> sp_renamedb still exists and is supported).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
>> news:%23kAEKBBHGHA.1288@.TK2MSFTNGP09.phx.gbl...
>> > sp_renamedb
>> >
>> > It is documented in SQL Book Online :-
>> > tsqlref.chm::/ts_sp_ra-rz_3yle.htm
>> >
>> > --
>> > HTH. Ryan
>> > "kei" <kei@.discussions.microsoft.com> wrote in message
>> > news:65DE5F9E-A2DC-44B6-B3BC-06A0EDAFF7FC@.microsoft.com...
>> >> As caption, how to rename a database?
>> >
>> >
>>|||I agree with what you're saying, however i was surprised to see that BOL
suggests the opposite..
BOL - ALTER DATABASE
Remarks
To remove a database, use DROP DATABASE. To rename a database, use
sp_renamedb. For more information about decreasing the size of a database,
see DBCC SHRINKDATABASE.
tsqlref.chm::/ts_aa-az_4e5h.htm
HTH. Ryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OTlRGkCHGHA.3728@.tk2msftngp13.phx.gbl...
>> Its "Alter Database" itself whats hapenning inside sp_renamedb
> It's true that sp_renamedb is basically just a wrapper for ALTER DATABASE
> so you might as well eliminate the middle man and execute the command
> directly. sp_renamedb will be removed in a future version of SQL Server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
> news:16C5441C-5988-4B95-AD97-A05184B4964F@.microsoft.com...
>> sp_helptext 'sp_renamedb', will give you clear picture of what sp_rename
>> DB
>> does.
>> Its "Alter Database" itself whats hapenning inside sp_renamedb
>> Thanks,
>> Sree
>> "Tibor Karaszi" wrote:
>> Also, as of SQL Server 2000, it is recommended to rename a database
>> using ALTER DATABASE (although
>> sp_renamedb still exists and is supported).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
>> news:%23kAEKBBHGHA.1288@.TK2MSFTNGP09.phx.gbl...
>> > sp_renamedb
>> >
>> > It is documented in SQL Book Online :-
>> > tsqlref.chm::/ts_sp_ra-rz_3yle.htm
>> >
>> > --
>> > HTH. Ryan
>> > "kei" <kei@.discussions.microsoft.com> wrote in message
>> > news:65DE5F9E-A2DC-44B6-B3BC-06A0EDAFF7FC@.microsoft.com...
>> >> As caption, how to rename a database?
>> >
>> >
>>
>|||>I agree with what you're saying, however i was surprised to see that BOL
>suggests the opposite..
It appears you are using the original RTM version of the SQL 2000 BOL rather
than the latest update. You can download the latest reference from
http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en.
Here's what the latest SQL Server BOL says:
<Excerpt BOL=SQL2000" href="http://links.10026.com/?link=tsqlref.chm::/ts_aa-az_4e5h.htm">
To remove a database, use DROP DATABASE. To rename a database, use the
MODIFY NAME = new_dbname option with ALTER DATABASE. For more information
about decreasing the size of a database, see DBCC SHRINKDATABASE.
</Excerpt>
Also, from the SQL Server 2005 BOL.
<Excerpt BOL=SQL2005"
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/7dd9d4ff-20e1-4857-9a8e-a5bff767cf76.htm">
Important:
This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature. Use ALTER DATABASE MODIFY NAME
instead. For more information, see ALTER DATABASE (Transact-SQL).
</Excerpt>
Even though you can still use sp_renamedb in SQL 2000 and SQL 2005, I think
it's best to break old habits.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:OLGYbqCHGHA.3936@.TK2MSFTNGP12.phx.gbl...
>I agree with what you're saying, however i was surprised to see that BOL
>suggests the opposite..
> BOL - ALTER DATABASE
> Remarks
> To remove a database, use DROP DATABASE. To rename a database, use
> sp_renamedb. For more information about decreasing the size of a database,
> see DBCC SHRINKDATABASE.
>
> tsqlref.chm::/ts_aa-az_4e5h.htm
>
> --
> HTH. Ryan
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OTlRGkCHGHA.3728@.tk2msftngp13.phx.gbl...
>> Its "Alter Database" itself whats hapenning inside sp_renamedb
>> It's true that sp_renamedb is basically just a wrapper for ALTER DATABASE
>> so you might as well eliminate the middle man and execute the command
>> directly. sp_renamedb will be removed in a future version of SQL Server.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
>> news:16C5441C-5988-4B95-AD97-A05184B4964F@.microsoft.com...
>> sp_helptext 'sp_renamedb', will give you clear picture of what sp_rename
>> DB
>> does.
>> Its "Alter Database" itself whats hapenning inside sp_renamedb
>> Thanks,
>> Sree
>> "Tibor Karaszi" wrote:
>> Also, as of SQL Server 2000, it is recommended to rename a database
>> using ALTER DATABASE (although
>> sp_renamedb still exists and is supported).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
>> news:%23kAEKBBHGHA.1288@.TK2MSFTNGP09.phx.gbl...
>> > sp_renamedb
>> >
>> > It is documented in SQL Book Online :-
>> > tsqlref.chm::/ts_sp_ra-rz_3yle.htm
>> >
>> > --
>> > HTH. Ryan
>> > "kei" <kei@.discussions.microsoft.com> wrote in message
>> > news:65DE5F9E-A2DC-44B6-B3BC-06A0EDAFF7FC@.microsoft.com...
>> >> As caption, how to rename a database?
>> >
>> >
>>
>>
>|||You're correct... I'm on a loaner PC today, i assumed it had the BOL
update - it doesn't...
Thanks
--
HTH. Ryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ek6mwNDHGHA.208@.tk2msftngp13.phx.gbl...
> >I agree with what you're saying, however i was surprised to see that BOL
> >suggests the opposite..
> It appears you are using the original RTM version of the SQL 2000 BOL
> rather than the latest update. You can download the latest reference from
> http://www.microsoft.com/downloads/details.aspx?FamilyID=a6f79cb1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en.
> Here's what the latest SQL Server BOL says:
> <Excerpt BOL=SQL2000" href="http://links.10026.com/?link=tsqlref.chm::/ts_aa-az_4e5h.htm">
> To remove a database, use DROP DATABASE. To rename a database, use the
> MODIFY NAME = new_dbname option with ALTER DATABASE. For more information
> about decreasing the size of a database, see DBCC SHRINKDATABASE.
> </Excerpt>
> Also, from the SQL Server 2005 BOL.
> <Excerpt BOL=SQL2005"
> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/7dd9d4ff-20e1-4857-9a8e-a5bff767cf76.htm">
> Important:
> This feature will be removed in a future version of Microsoft SQL Server.
> Avoid using this feature in new development work, and plan to modify
> applications that currently use this feature. Use ALTER DATABASE MODIFY
> NAME instead. For more information, see ALTER DATABASE (Transact-SQL).
> </Excerpt>
> Even though you can still use sp_renamedb in SQL 2000 and SQL 2005, I
> think it's best to break old habits.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:OLGYbqCHGHA.3936@.TK2MSFTNGP12.phx.gbl...
>>I agree with what you're saying, however i was surprised to see that BOL
>>suggests the opposite..
>> BOL - ALTER DATABASE
>> Remarks
>> To remove a database, use DROP DATABASE. To rename a database, use
>> sp_renamedb. For more information about decreasing the size of a
>> database, see DBCC SHRINKDATABASE.
>>
>> tsqlref.chm::/ts_aa-az_4e5h.htm
>>
>> --
>> HTH. Ryan
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:OTlRGkCHGHA.3728@.tk2msftngp13.phx.gbl...
>> Its "Alter Database" itself whats hapenning inside sp_renamedb
>> It's true that sp_renamedb is basically just a wrapper for ALTER
>> DATABASE so you might as well eliminate the middle man and execute the
>> command directly. sp_renamedb will be removed in a future version of SQL
>> Server.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
>> news:16C5441C-5988-4B95-AD97-A05184B4964F@.microsoft.com...
>> sp_helptext 'sp_renamedb', will give you clear picture of what
>> sp_rename DB
>> does.
>> Its "Alter Database" itself whats hapenning inside sp_renamedb
>> Thanks,
>> Sree
>> "Tibor Karaszi" wrote:
>> Also, as of SQL Server 2000, it is recommended to rename a database
>> using ALTER DATABASE (although
>> sp_renamedb still exists and is supported).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
>> news:%23kAEKBBHGHA.1288@.TK2MSFTNGP09.phx.gbl...
>> > sp_renamedb
>> >
>> > It is documented in SQL Book Online :-
>> > tsqlref.chm::/ts_sp_ra-rz_3yle.htm
>> >
>> > --
>> > HTH. Ryan
>> > "kei" <kei@.discussions.microsoft.com> wrote in message
>> > news:65DE5F9E-A2DC-44B6-B3BC-06A0EDAFF7FC@.microsoft.com...
>> >> As caption, how to rename a database?
>> >
>> >
>>
>>
>>
>sql

How to rename a database

As caption, how to rename a database?
sp_renamedb
It is documented in SQL Book Online :- tsqlref.chm::/ts_sp_ra-rz_3yle.htm
HTH. Ryan
"kei" <kei@.discussions.microsoft.com> wrote in message
news:65DE5F9E-A2DC-44B6-B3BC-06A0EDAFF7FC@.microsoft.com...
> As caption, how to rename a database?
|||Also, as of SQL Server 2000, it is recommended to rename a database using ALTER DATABASE (although
sp_renamedb still exists and is supported).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:%23kAEKBBHGHA.1288@.TK2MSFTNGP09.phx.gbl...
> sp_renamedb
> It is documented in SQL Book Online :- tsqlref.chm::/ts_sp_ra-rz_3yle.htm
> --
> HTH. Ryan
> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:65DE5F9E-A2DC-44B6-B3BC-06A0EDAFF7FC@.microsoft.com...
>
|||sp_helptext 'sp_renamedb', will give you clear picture of what sp_rename DB
does.
Its "Alter Database" itself whats hapenning inside sp_renamedb
Thanks,
Sree
"Tibor Karaszi" wrote:

> Also, as of SQL Server 2000, it is recommended to rename a database using ALTER DATABASE (although
> sp_renamedb still exists and is supported).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:%23kAEKBBHGHA.1288@.TK2MSFTNGP09.phx.gbl...
>
|||> Its "Alter Database" itself whats hapenning inside sp_renamedb
It's true that sp_renamedb is basically just a wrapper for ALTER DATABASE so
you might as well eliminate the middle man and execute the command directly.
sp_renamedb will be removed in a future version of SQL Server.
Hope this helps.
Dan Guzman
SQL Server MVP
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:16C5441C-5988-4B95-AD97-A05184B4964F@.microsoft.com...[vbcol=seagreen]
> sp_helptext 'sp_renamedb', will give you clear picture of what sp_rename
> DB
> does.
> Its "Alter Database" itself whats hapenning inside sp_renamedb
> Thanks,
> Sree
> "Tibor Karaszi" wrote:
|||I agree with what you're saying, however i was surprised to see that BOL
suggests the opposite..
BOL - ALTER DATABASE
Remarks
To remove a database, use DROP DATABASE. To rename a database, use
sp_renamedb. For more information about decreasing the size of a database,
see DBCC SHRINKDATABASE.
tsqlref.chm::/ts_aa-az_4e5h.htm
HTH. Ryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OTlRGkCHGHA.3728@.tk2msftngp13.phx.gbl...
> It's true that sp_renamedb is basically just a wrapper for ALTER DATABASE
> so you might as well eliminate the middle man and execute the command
> directly. sp_renamedb will be removed in a future version of SQL Server.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
> news:16C5441C-5988-4B95-AD97-A05184B4964F@.microsoft.com...
>
|||>I agree with what you're saying, however i was surprised to see that BOL
>suggests the opposite..
It appears you are using the original RTM version of the SQL 2000 BOL rather
than the latest update. You can download the latest reference from
http://www.microsoft.com/downloads/d...isplayLang=en.
Here's what the latest SQL Server BOL says:
<Excerpt BOL=SQL2000" href="http://links.10026.com/?link=tsqlref.chm::/ts_aa-az_4e5h.htm">
To remove a database, use DROP DATABASE. To rename a database, use the
MODIFY NAME = new_dbname option with ALTER DATABASE. For more information
about decreasing the size of a database, see DBCC SHRINKDATABASE.
</Excerpt>
Also, from the SQL Server 2005 BOL.
<Excerpt BOL=SQL2005"
href="ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/7dd9d4ff-20e1-4857-9a8e-a5bff767cf76.htm">
Important:
This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature. Use ALTER DATABASE MODIFY NAME
instead. For more information, see ALTER DATABASE (Transact-SQL).
</Excerpt>
Even though you can still use sp_renamedb in SQL 2000 and SQL 2005, I think
it's best to break old habits.
Hope this helps.
Dan Guzman
SQL Server MVP
"Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:OLGYbqCHGHA.3936@.TK2MSFTNGP12.phx.gbl...
>I agree with what you're saying, however i was surprised to see that BOL
>suggests the opposite..
> BOL - ALTER DATABASE
> Remarks
> To remove a database, use DROP DATABASE. To rename a database, use
> sp_renamedb. For more information about decreasing the size of a database,
> see DBCC SHRINKDATABASE.
>
> tsqlref.chm::/ts_aa-az_4e5h.htm
>
> --
> HTH. Ryan
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OTlRGkCHGHA.3728@.tk2msftngp13.phx.gbl...
>
|||You're correct... I'm on a loaner PC today, i assumed it had the BOL
update - it doesn't...
Thanks
HTH. Ryan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:ek6mwNDHGHA.208@.tk2msftngp13.phx.gbl...
> It appears you are using the original RTM version of the SQL 2000 BOL
> rather than the latest update. You can download the latest reference from
> http://www.microsoft.com/downloads/d...isplayLang=en.
> Here's what the latest SQL Server BOL says:
> <Excerpt BOL=SQL2000" href="http://links.10026.com/?link=tsqlref.chm::/ts_aa-az_4e5h.htm">
> To remove a database, use DROP DATABASE. To rename a database, use the
> MODIFY NAME = new_dbname option with ALTER DATABASE. For more information
> about decreasing the size of a database, see DBCC SHRINKDATABASE.
> </Excerpt>
> Also, from the SQL Server 2005 BOL.
> <Excerpt BOL=SQL2005"
> href="http://links.10026.com/?link=ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/7dd9d4ff-20e1-4857-9a8e-a5bff767cf76.htm">
> Important:
> This feature will be removed in a future version of Microsoft SQL Server.
> Avoid using this feature in new development work, and plan to modify
> applications that currently use this feature. Use ALTER DATABASE MODIFY
> NAME instead. For more information, see ALTER DATABASE (Transact-SQL).
> </Excerpt>
> Even though you can still use sp_renamedb in SQL 2000 and SQL 2005, I
> think it's best to break old habits.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ryan" <Ryan_Waight@.nospam.hotmail.com> wrote in message
> news:OLGYbqCHGHA.3936@.TK2MSFTNGP12.phx.gbl...
>

How to rename a column or table on the desktop?

I am using the compact edition on a desktop using VS2005 as well as SQL Server Management Studio. None of those tools allow me to rename a column or rename a table. Can someone point me a tool the runs on the desktop (as opposed to running on a CE device) that allows me to do the renaming?

Thanks

A 3rd party tool from this company may be able to help you, please contact the company (I am not affiliated): http://www.primeworks-mobile.com/

|||

I have published an article on this subject that you can adapt to the desktop:

Renaming a SQL CE Table From a .NET CF Application

Although some of my products do that (I am affiliated to that company ), you don't have to purchase one just to rename a table or column name. The code I published on that article can be easily adapted to the desktop. If you have problems with it, just let me know.

|||Great tool! Instead of copying, MS should just buy your tool.

How to rename a column in SqlCE?

When I execute EXEC sp_rename 'PATHSPECIMEN.PVVALUE', 'PVPROC', 'COLUMN', I am getting the following error:
Major Error 0x80004005, Minor Error 25621
> EXEC sp_rename 'PATHSPECIMEN.PVVALUE', 'PVPROC', 'COLUMN'
The specified argument value for the procedure is not valid. [ Argument # = 3,Name of procedure(if known) = sp_rename ]

Could some one guide me where I am wrong?
I am executing this query against SqlCE in Sql Server 2005 Management Studio.

The sp_rename procedure is not avialable in SQL CE!

In Sql Server 2005 Management Studio you have to create a new column with the new name, then you have to update it with the values from your old column and then you have to delete your old column. The last action is difficult if the column is (part of) an index.

ALTER TABLE YOURTABLENAME ADD PVPROC INTEGER (or your datatype)
UPDATE YOURTABLENAME SET PVPROC = PATHSPECIMEN.PVVALUE
ALTER TABLE School DROP COLUMN PATHSPECIMEN.PVVALUE

I did it yesterday myself in this way!

Anne-Jan Tuinstra

|||Hi,

Thanks for your prompt reply and for the nice solution.

As you said that column is a part of index, how to go to resolve this case?.

Thanks
GSR|||

I think you/ I have to remove the index.

Up till now it didn't work with the manager.

My table now has one column to many!!!!!

How to removing document map

I add document map to my report but I forgot

how can I remove it

thanks!I believe that you put something in the label property of something to make it appear in the documents map. If you delete what you entered in the label property you should be okay.|||I removed all of the entries in the Document Map label fields. When I run the report in RS, the document map pane is still displayed with the report name on the document map pane. Any idea how to make the document pane not show at all?|||Once I removed all of the labels for the testboxes showing in the document map, I was able to view my report without viewing the document map.

How to removing document map

I add document map to my report but I forgot

how can I remove it

How to remove WITH FILLFACTOR = 100 when Generate Sql Script ?

Hello,

I'm using Entreprise Manager (for Sql Server 2000) to generate my
database's script. By mistake, i've changer FillFactor one time. And,
now I can't remove this data from generated sql script. How to remove
that ?

Thank's a lot.rabii (rabii.mail@.gmail.com) writes:
> I'm using Entreprise Manager (for Sql Server 2000) to generate my
> database's script. By mistake, i've changer FillFactor one time. And,
> now I can't remove this data from generated sql script. How to remove
> that ?

Can't you just run the file through a search/replace session in some
text editor? Then you could build a new database, and then script
from that one.

Even better, have all your SQL code under version control, so you
don't have to script from the databaes.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for reply :),

I know this solutions :), but, the problem that I have a production
database. For each new version of my product, I have to compare my new
database schema and the old one (current production database). So the
tool "Sql Server Compare" (by comparing the databases script) show me
always that all the table are different although the difference is
simply the FILLFACTOR (in production database). Now I can change it
using Entreprise Manager but I can't remove it ?|||If that's Red-Gate's SQL Compare, I think that there is an option to
ignore fillfactors, among other things. Unfortunately, I'm at a machine
that doesn't have that tool installed, so I can't confirm that. I know
that you can definitely ignore some things, and I *think* that
FILLFACTOR is one of them.

Good luck,
-Tom.|||rabii (rabii.mail@.gmail.com) writes:
> I know this solutions :), but, the problem that I have a production
> database. For each new version of my product, I have to compare my new
> database schema and the old one (current production database). So the
> tool "Sql Server Compare" (by comparing the databases script) show me
> always that all the table are different although the difference is
> simply the FILLFACTOR (in production database). Now I can change it
> using Entreprise Manager but I can't remove it ?

So where is the fill factor wrong? In the development database or in
the production database? But whichever, can't you just adapt the
fillfactor of the development database to the production database?

Then again, fill factor is one of these things that could be different
from database to database, because different instances of the schema
has different data load. So you should probably see if your comparison
tool can ignore difference in fill factor.

Finally, I can't keep from saying that the whole thing of comparing
database schemas to build change scripts is not a very good idea. Do
you really want all sort of test junk in the dev database hit the
production server? If you have your code under version control,
you can build change scripts from the version-control system. This
gives you a much more solid base to stand on.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thanks again for every one :)

I've said that I'm using the tool "Sql Server Compare" (it's a free
tool).

You're right when you said that it is better to use a version control
solution. But now the problem, that by mistake, I've added fillfactor =
x in the production database. In the dev database, I havn't did that.
When I compare the two databases, I notice that the comprator (sql
server compare) show me that all the table are different. When I
analyse the code, simply the fill factor is not the same. I can resolve
that by adding the same fillfactor in my dev database but I wan't to
know " if there is a way by script to remove fillfactor = x" ?|||rabii (rabii.mail@.gmail.com) writes:
> I've said that I'm using the tool "Sql Server Compare" (it's a free
> tool).
> You're right when you said that it is better to use a version control
> solution. But now the problem, that by mistake, I've added fillfactor =
> x in the production database. In the dev database, I havn't did that.
> When I compare the two databases, I notice that the comprator (sql
> server compare) show me that all the table are different. When I
> analyse the code, simply the fill factor is not the same. I can resolve
> that by adding the same fillfactor in my dev database but I wan't to
> know " if there is a way by script to remove fillfactor = x" ?

For an index you should be able to get rid of with with CREATE INDEX ...
WITH DROP_EXISTING. If it's a PRIMARY KEY constraint of a UNIQUE constraint,
I think you have to drop and recreate. Since you typically have FK
constraints to a PK, that can be kind of messy.

I played around a little, and it seems that Enterprise Manager does not
include fillfactor for primary keys, so changing your dev database may
not help.

Looks as if you either have to rebuild the production database or give
SQL Server Compare the boot...

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to remove user authentication for my report server.

Hi Experts,
I had tried to remove report authentication (on a report-by-report
basis), select
a report in the Report Manager, then select the Properties tab and
select the very bottom radio button that says 'Credentials are not
required' and then 'Apply.'
I have tried above things but after doing like that report is not
rendring it is showng error that
credential is not set.
So please tell what to do... This i done for a single report only
suppoese if i want to remove authentication from my report server it
self then what to do, how i can implement that.
Or can any body tell me how to remove authentication from my report
server so that any body can run the report and it should not ask any
user name or pass word.
Regards
DineshI presume that you are using Shared Data source,then open your project and go
to shared data source. click properties and there you select "Credentials not
required", and before doing that go to the "Edit" data source and click on
Advance and click on security "UserID" give the admin user id or an Id
equivalent to admin.
Amarnath, MCTS.
"Dinesh" wrote:
> Hi Experts,
> I had tried to remove report authentication (on a report-by-report
> basis), select
> a report in the Report Manager, then select the Properties tab and
> select the very bottom radio button that says 'Credentials are not
> required' and then 'Apply.'
> I have tried above things but after doing like that report is not
> rendring it is showng error that
> credential is not set.
> So please tell what to do... This i done for a single report only
> suppoese if i want to remove authentication from my report server it
> self then what to do, how i can implement that.
> Or can any body tell me how to remove authentication from my report
> server so that any body can run the report and it should not ask any
> user name or pass word.
> Regards
> Dinesh
>

How to remove unwanted characters in SQL?

Hi all,

I have a stored procedure that generates the following SQL WHERE clause

UserName LIKE 'Adi234%' AND Fname LIKE 'David%' AND LName LIKE 'Justin%' AND

It is good except that it i can not remove the last AND which is not neccessary at the end of the clause.

I want to remove the last AND that come up at the end, my code places AND after each data field(UserName, Fname, Lname) .

Thanks

a quick and dirty way: add

1=1

after the query (which always is good Wink

|||

In your stored procedure, you can use a Substring to remove the end AND:

Assume @.strSQL is the SQL statement string you are returning now. Add this line:

SET @.strSQL=SUBSTRING(@.strSQL, 0,LEN(@.strSQL)-3)

|||

Thanks Limno, that works great

How to remove unwanted characters during ETL?

Can unwanted characters (e.g. control codes) be replaced or removed in varchar fields during extraction inside DTS package?

SQL Server/SSIS 2005.

Thanks, Andrei.

Inside of an SSIS package, yes. DTS is old news now! You can use the REPLACE() function along with a CHAR() function in a derived column transformation.

To remove tabs: REPLACE([column],char(9),"")|||

Thanks, Phil!

This is the way to go.

Unfortunatelly there are couple problems.
1) Function CHAR isn't supported in SSIS (DTSx) package.

2) I'd like to have a filter against any invalid character, e.g. ASCII range 0-31. I'm not sure if SSIS's REPLACE function can do that.

I wonder if a custom function, e.g. from a CLR assembly, could be used in SSIS.

Regards, Andrei.

|||

Andrei Kuzmenkov wrote:
Thanks, Phil!
This is the way to go.
Unfortunatelly there are couple problems.
1) Function CHAR isn't supported in SSIS (DTSx) package.
2) I'd like to have a filter against any invalid character, e.g. ASCII range 0-31. I'm not sure if SSIS's REPLACE function can do that.
I wonder if a custom function, e.g. from a CLR assembly, could be used in SSIS.
Regards, Andrei.

CHAR() is too supported, it's just not in the drop down list. You can use it inside the replace function, just as I noted. You just have to type it in there. You could always write 31|||

Andrei Kuzmenkov wrote:

I wonder if a custom function, e.g. from a CLR assembly, could be used in SSIS.

Yes, you can create Script Transform and easily call custom function from there. Or if it is one-time code, you may simply place it in the Script Transform code as well.

Note that the custom assembly needs to be placed to GAC and because of to limitation of VSA we use for scripting it also should be in Windows\Microsoft.NET\Framework\v2.0.50727 (this is only needed for design time).

|||

In VS2005 I'm getting "The Function CHAR was not recognized".

Friday, March 30, 2012

How to rename physical file name

How to rename instance

How to rename instance

how to rename files before send files?

How to Rename Database Files

How to Rename Database Files

How to Rename Database Files

How to rename an instance

How to rename an instance

how to rename a temp table

How to rename a table ?

How to rename a Olap Cube (AS2000)

How to rename a file in SSIS

How to rename a database?

How to rename a database?

How to rename a database?

How to rename a database without detaching it?

How to rename a database

How to rename a database

How to rename a database

How to rename a column or table on the desktop?

How to rename a column in SqlCE?

How to removing document map

How to removing document map

How to remove WITH FILLFACTOR = 100 when Generate Sql Script ?

How to remove user authentication for my report server.

How to remove unwanted characters in SQL?

How to remove unwanted characters during ETL?

query database table

Blog Archive

About Me