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.
>|||Tim
Detach the database. Rename the windows files and attach as whatever name yo
u like.
Regards
John|||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...
name[vbcol=seagreen]
> you like.
>|||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...
> file
> name
>|||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 crea
ted a database called 'Fred'. I detached 'Fred'. I renamed the MDF and LDF f
iles to 'Pete'. I attached
database as 'Pete'. Database 'Pete' seems to be just fine. What did I miss?
Regards
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...
> 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?
>|||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...
names[vbcol=seagreen]
> to
filenames[vbcol=seagreen]
> to
files[vbcol=seagreen]
logical[vbcol=seagreen]
> He
> 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.
>
worked[vbcol=seagreen]
> I
and[vbcol=seagreen]
seems[vbcol=seagreen]
> to
>

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.
>
|||Tim
Detach the database. Rename the windows files and attach as whatever name you like.
Regards
John
|||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[vbcol=seagreen]
> 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...
name
> you like.
>
|||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...
> file
> name
>
|||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
|||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...
> 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?
>
|||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...[vbcol=seagreen]
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:e%23LFfB$KEHA.620@.TK2MSFTNGP10.phx.gbl...
names[vbcol=seagreen]
> to
filenames[vbcol=seagreen]
> to
files[vbcol=seagreen]
logical
> He
> You are correct. That was my intention. Though it really had to be a
program[vbcol=seagreen]
> 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.
worked[vbcol=seagreen]
> I
and[vbcol=seagreen]
seems
> to
>

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 remove and move the Distribution database?

We have 3 servers A, B and C.
There were databases on Server B being replicated in C.
The Distribution database is located in server A.
I have been told that the server A is going away.
Question: How do I move the Distribution database from Server A into one of
the other servers?
Do I need to redo the Replication Setup on Servers B and C?
Thanks,
Suresh Kumar
Disclaimer: I tried posting this message in the Replication group but there
were no replies and hence I trying my luck here.Kumar,
I'm trying to envision here because I don't have experience with that
particular case.
I don't think you need to redo the replication. You only need to make sure
the new distribution server is IDENTICAL to the old one. So how do you
mirror the distribution server?
1. Pause the publisher (B). Make sure all transactions are commited. Stop
replication by stopping all agents (log reader, distribution agents,
snapshot agents, etc.) so that there is no open transactions.
2. Backup master, msdb, distribution db on the distribution server. Script
out logins;
3. Bring down the distriubition server;
4. Turn on the new server. Make sure it is set up exactly the same as the
old distribution server. Rename it to A. check IP address, DNS entries, etc.
5. Install SQL server with same version and service packs. Rename the SQL
server name to what is was called on A.
6. Restore master, msdb and distribution databases; Don't forget to run the
login script to recreate them on the new server.
7. At this point, the new server looks the same as the old one. All
replication related tables are there...Neither the publisher nor subscriber
will notice that the server is no longer what is was;
8. Restart replication agents. You should rock...
Richard
"Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
news:eaim$%23EWDHA.1204@.TK2MSFTNGP12.phx.gbl...
> We have 3 servers A, B and C.
> There were databases on Server B being replicated in C.
> The Distribution database is located in server A.
> I have been told that the server A is going away.
> Question: How do I move the Distribution database from Server A into one
of
> the other servers?
> Do I need to redo the Replication Setup on Servers B and C?
> Thanks,
> Suresh Kumar
> Disclaimer: I tried posting this message in the Replication group but
there
> were no replies and hence I trying my luck here.
>|||Suresh,
Please reply to the group.
It seems your plan becomes more difficult. If you move distribution db to
either B or C, then both server need to reconfigure 'cause they now have to
repoint to the new distribution server. The system tables involved in
replications are not easy to modify to reflect the change. Although it might
be doable, it takes pain to get everything seamless. Considering the effort
and difficulty, you'd rather script out the replication, sever it, and set
up from scratch. Once you get publisher, distributor, and subscriber in
place, you can change the server name in the script and apply on the
publisher, or even do it manually.
Richard
"Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
news:eaim$%23EWDHA.1204@.TK2MSFTNGP12.phx.gbl...
> We have 3 servers A, B and C.
> There were databases on Server B being replicated in C.
> The Distribution database is located in server A.
> I have been told that the server A is going away.
> Question: How do I move the Distribution database from Server A into one
of
> the other servers?
> Do I need to redo the Replication Setup on Servers B and C?
> Thanks,
> Suresh Kumar
> Disclaimer: I tried posting this message in the Replication group but
there
> were no replies and hence I trying my luck here.
>|||Richard,
Thanks for your input.
Lucky for me the Replication database has not gone to production yet.
Hence, as per your suggestion, I was able to tear it down and rebuild it
from scratch.
My 2 questions now are:
- There is still an old Distribution database in Server A. How do I delete
it. The Delete is dimmed out under Replication/Distributor properties even
though there are no subscriptions to that database. Can I just delete the
old Ditribution database on Server A (by deleting thru DB properties)
- The Publication and distribution are now on Server B. I am not able to do
pull subscriptions from Server C unless I allow 'Anonymous' subscriptions
for the Publications in B. What are the implications of allowing Anonymous
subsriptions? Does this make us more vulnerable from the securities point of
view?
Thanks,
Suresh Kumar
"Richard Ding" <dingr@.cleanharbors.com> wrote in message
news:%237nfTHHWDHA.652@.tk2msftngp13.phx.gbl...
> Suresh,
> Please reply to the group.
> It seems your plan becomes more difficult. If you move distribution db to
> either B or C, then both server need to reconfigure 'cause they now have
to
> repoint to the new distribution server. The system tables involved in
> replications are not easy to modify to reflect the change. Although it
might
> be doable, it takes pain to get everything seamless. Considering the
effort
> and difficulty, you'd rather script out the replication, sever it, and set
> up from scratch. Once you get publisher, distributor, and subscriber in
> place, you can change the server name in the script and apply on the
> publisher, or even do it manually.
>
> Richard
> "Suresh Kumar" <skumat@.pcdiDOTcom> wrote in message
> news:eaim$%23EWDHA.1204@.TK2MSFTNGP12.phx.gbl...
> > We have 3 servers A, B and C.
> > There were databases on Server B being replicated in C.
> > The Distribution database is located in server A.
> >
> > I have been told that the server A is going away.
> >
> > Question: How do I move the Distribution database from Server A into one
> of
> > the other servers?
> > Do I need to redo the Replication Setup on Servers B and C?
> >
> > Thanks,
> > Suresh Kumar
> >
> > Disclaimer: I tried posting this message in the Replication group but
> there
> > were no replies and hence I trying my luck here.
> >
> >
>|||Hari,
Thanks for your input.
Suresh
<hkvats_1999@.yahoo.com> wrote in message
news:68c8c86d.0308040059.7e3e8d5e@.posting.google.com...
> Hi Suresh,
> You need to follow the complete process for Disabling Distributor.
> --Deletion of Distribution Database
> Execute sp_dropdistributiondb to delete a distribution database.
> --Disable distributors
> Execute sp_dropdistributor with @.no_checks=1
> 'Anonymous' subscriptions
> ==================================================================> Anonymous subscriptions to allow Subscribers unknown to the Publisher
> to subscribe to this publication and not able to change this after the
> publication is created.
> I suggest you go thru with detailed description on this in BOOKS
> ONLINE Topic titled =============="Anonymous Subscriptions"
>
> regards
> Hari Sharma, India

how to remote connection to an SQL server on the same network?

hi
i added 2 databases to my SQL server on a machine next door using Microsoft SQL server management studio express... I made sure that the server allows remote connection

then from this computer i pass a connection string to my SQLCLient in VB2005 that looks a bit like this

Code: ( text )

1. ConnString = "Server=USER-PTDH8Z5ESH\SQLEXPRESS;Database=FSS.MDB;Trusted_Con nection=True;User Instance=True"

for which it gives me this error when i open the connection
SQLConnecton.Open()

System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.Att emptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.Log inNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.Ope nLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ct or(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateC onnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.Creat ePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateOb ject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCrea teRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConne ction(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetCo nnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenCo nnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Flying_School_Scheduler.DBEngine.StartConnection() in

whats the big idea?
i'm in a bit of a tight schedule here... can anyone help me here? ?

First, let me express my aplogies for not getting to your question sooner. It would be not too trite to say that since most of us that take our time to respond to questions such as yours, are volunteers, and we are often also on a tight schedule. Simple, straightforward requests for assistance are looked at and perhaps even responded to far sooner than demanding, whining ones...

As to the 'big deal' with your connection string: There are a couple of possibilites:

Often using 'prohibited' characters in a server name requires extra handling, such as enclosing the name in square brackets. You might try:
Server=[USER-PTDH8Z5ESH\SQLEXPRESS]; Is the database itself named FSS.MDB, or could that, by mistake, be the database file name?|||

Normally, error 26 is caused by a few common problems that are easily fixed. Please take a look at this posting as it should probably fix your problem: http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

Thanks,

Il-Sung.

How to reference items from different database?

Hi all,

I have two databases DB_External and DB_Internal.

I am writing some stored procedures (in DB_Internal) that reads from
the tables in DB_External. I execute my stored procedures from
DB_Internal.

Everytime I want to read the tables in DB_External, I have to refer to
the table as DB_External.dbo.tableName.

Is there a better way of declaring the database DB_External up front
in the stored procedure so I don't have to pre-fix "DB_External.dbo."
in all the table names?

Thanks in advance,
June Moore."June Moore" <jungewum@.yahoo.com.au> wrote in message
news:e5dfaf21.0310100030.3e0067a5@.posting.google.c om...
> Hi all,
> I have two databases DB_External and DB_Internal.
> I am writing some stored procedures (in DB_Internal) that reads from
> the tables in DB_External. I execute my stored procedures from
> DB_Internal.
> Everytime I want to read the tables in DB_External, I have to refer to
> the table as DB_External.dbo.tableName.
> Is there a better way of declaring the database DB_External up front
> in the stored procedure so I don't have to pre-fix "DB_External.dbo."
> in all the table names?
> Thanks in advance,
> June Moore.

You always have to fully qualify the name - there's no way around that. And
it's good practice to include the dbo owner prefix, even in the 'local'
database.

If you're concerned about moving code between servers where the DB names may
be different, you could consider a pre-processor script to 'fill in' or
find/replace the DB names in a code template, then create the procedure.

Simonsql

How to reference different databases on different servers in one query

Hi,

I'm a novice to SQL Server, though I have some experience in Access.

My problem is this:

I have a table on my local database that I want to insert into a remove database on a different server

What are the steps neccessary?

My local server name is 'HOME\SQLEXPRESS' Local database: 'MyDBTest', table name: 'dbo.Courses' (the fields are: ID, CourseName, TeacherID)|||Take a look in Books On Line at a topic called Linked Servers. Although I am not 100% sure that they are supported in Express|||Using linked servers (first create it using te linked server procedures for that) you can issue this query:

SELECT *
FROM [Linkedservername].[ku.univ].dbo.Courses

HTH, Jens Suessmeyer,

http://www.sqlserver2005.de
|||

I think I'm not using the right syntax

I've created the linked server by:

sp_addlinkedserver @.server='REMOTESRV', @.provider='SQLOLEDB',@.srvproduct='',@.datasrc='129.113.271.58\ku.univ'

The log was: Command(s) completed successfully.

I then tried to issue an SQL Statement as you have adviced:

SELECT *

FROM [REMOTESRV].[ku.univ].dbo.Courses

but the log was:

OLE DB provider "SQLNCLI" for linked server "REMOTESRV" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "REMOTESRV" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 65535, Level 16, State 1, Line 0

SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].

I know that the server allows remote connections: when I connect to it using Object browser I can issue INSERT queries - no problem

Maybe I have a problem with syntax

Thanx a lot

|||Check out the 'Linked Servers' topic in the Books Online|||

Hi,

this here:

,@.datasrc='129.113.271.58\ku.univ'

Should be the instance to connect to, so normally this has to be written in the syntax of:

Servername\Instancename,Portnumber (where as the servername can be exchanged by the ip adress and the portnumber (can be left out if you didn′t choose another one that the default one [or you have SQl Server 2005 at the other end using dynamic port and installed the SQL Server Browser]) and the instancename (which can be left out if you choose the default instance on the server.

So in your case for the default port and the default instance, this should be just:

,@.datasrc='129.113.271.58'

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi, Jens

Thanx a lot for helping, I've changed the @.datasrc as you wrote but got a different error this time:

OLE DB provider "SQLNCLI" for linked server "remotesrv2" returned message "Login timeout expired".

OLE DB provider "SQLNCLI" for linked server "remotesrv2" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".

Msg 53, Level 16, State 1, Line 0

Named Pipes Provider: Could not open a connection to SQL Server [53].

I'd really appreciate your help on this one.

Thanx in advance

|||

Other Ideas, anybody?

I'm pretty stuck here, I've read the documentation, did as it said but still grt an error.

Thanx!

|||

http://blogs.msdn.com/sql_protocols/archive/2005/10/22/483684.aspx

HTH

How to reference different databases on different servers in one query

Hi,

I'm a novice to SQL Server, though I have some experience in Access.

My problem is this:

I have a table on my local database that I want to insert into a remove database on a different server

What are the steps neccessary?

My local server name is 'HOME\SQLEXPRESS' Local database: 'MyDBTest', table name: 'dbo.Courses' (the fields are: ID, CourseName, TeacherID)

The remote SQL Server IP is: 129.113.271.58 (I've changed the real IP) The name of the database on this remote server is: 'ku.univ', and the remote table name I would like to update is 'dbo.Courses' (the table has the same fields as the local one).

I use SQL Server Express Edition

Please help!!

Thank you in advance

Michael

Any ideas?

I'm really stuck here - I am able to connect to the remote server and browse the database, but how do I make an INSERT query to the remote table from my local table?

In Access you could just put any table (local and linked) in one query - there has to be a way to do it in SQL Server

Thanx

|||

There are a couple of options for you. You could use BCP to export the table on the local server to a text file, then use the same utility to insert into the remote server. You can also use a linked server.

To setup a linked server run these commands from sqlcmd, replace the appropriate parameters:

exec sp_addlinkedserver 'RemoteServerName', N'SQL Server'
exec sp_addlinkedsrvlogin 'RemoteServerName',@.locallogin=NULL /*any login*/, @.rmtuser = 'remoteusername', @.rmtpassword='rmtpassword'

Then you will be able to run a query such as this when connected to your local server:

insert [remoteserver].[ku.univ].[dbo].[Tableneme] (columns...)

select columns... from localtable

I'm assuming here that the remote server is also SQL Server. If not you'll need to modify the sp_addlinkedserver call to specify the proper connection string.

How to reduce Transaction log file ?

Our SqlServer 2000 database's transaction log is about 70 gig in size. The
Recovery model is "Full". The database and the Transaction log are backed up
everyday, but the Transaction log back up failed because there is not enough
disk space.
How can I reduce the physical file size of Transcaction Log so that I can
back it up and it does not keep growing ?
Thank you.
First, to help ensure no potential for any data loss we need to determine if
the 70gb of space being used by the tran log, is being used by data or if it
is free/white-space being held in the transaction log. There are a couple of
ways to check this, one of which involves highlighting the database in EM,
selecting the TaskPad and looking at the amount of space free/in use within
the Transaction Log device.
If a large amount of space is being consumed by data, then you should
perform a transaction log backup, truncate the log and then immediately
perform a full SQL backup to help preserve data. If you cannot perform the
transaction log backup due to the lack of available disk space, then truncate
the log (ex: backup log <dbname> with truncate_only. Immediately after
performing this step, you must perform a full SQL DB backup in order to
ensure the recoverability of your DB. You should then realize that the
remaining space in your transaction log is now free space that should be
released back to the OS. to perform this, execute a DBCC SHRINKFILE
('<TranLogLogicalName>,<minsize>) where the TranLogLogicalName is the logical
name of the tran log device for that db and the minsize is the least size
that you would like to set the tran log to (in Mbs):
ex:
DBCC SHRINKFILE ('MyDb_log',1)
You should then make sure that the database has ongoing tran log backups
that occur on a repeat frequency throughout the day and a daily/weekly
process.
Regards,
"fortunataw" wrote:

> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed up
> everyday, but the Transaction log back up failed because there is not enough
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>
|||Hi,
Since you do not have the enogh hard disk space; I recommend you to start
the backup cycle again after truncating the Logs.
Execute the below command to truncate the log
Backup log <dbname> with Truncate_only
After that see the size and utilization log using
DBcc SQLPERF(logspace)
Once you see the utilization is less then:-
Use dbname
go
Dbcc shrinkfile('logical_ldf_file_name',size_in_mb_to_s hrink)
go
Get the logical LDF name using the below command:-
use dbname
go
sp_helpfile
Name stands for logical name.
This will ensure that your ldf is shrinked based on the value given.
After doing the above steps do:-
1. Full database backup
2. Start your trasnaction log backup in regular intervals (every 30 minutes
minimum frequency)
The second step will ensure that you LDF will not grow abnormally.
Thanks
Hari
SQL Server MVP
"fortunataw" <fortunataw@.discussions.microsoft.com> wrote in message
news:86FAF3B0-E7F7-4F16-B719-6264666B7694@.microsoft.com...
> Our SqlServer 2000 database's transaction log is about 70 gig in size. The
> Recovery model is "Full". The database and the Transaction log are backed
> up
> everyday, but the Transaction log back up failed because there is not
> enough
> disk space.
> How can I reduce the physical file size of Transcaction Log so that I can
> back it up and it does not keep growing ?
> Thank you.
>

How to Reduce or Delete the Log file... Log file size creating problem

I am using Replication with two of my SQL Server Databases , One Db is Publisher and other one is Subscriber , and data is continously replicating after every 5 mins . Now Publisher Database Log File increased upto 2541 MB and its creating problem while having any transaction or having any operation with DB . It also turned Replication utility Off.

I tried to delete log file by turning SQL Server off , but it makes DB mode SUSPECT and UNACCESSBLE.

Plz help me out to delete or reduce the log file size by maintaining the state of data.

Also let me know wht is the method so that log file can automatically delete its contents when reach to certain limit.

Regards
Imran MalikWhy are risking by deleting the SQL system files.

Use DBCC SHRINKFILE to reduce the Tlog file size, refer to books online for more information.|||You haven't mentioned about the version of SQL used.

If its version 7 then enable 'trunc.log at chkpt.' on the database.
If its version 2K then use SIMPLE RECOVERY model.

For more information again refer BOL.:)|||By changing to the simple recovery model, your only backup mechanism is the full or differential backup, you can no longer perform transaction log backups. If you need to shrink the file, however, and maintain the ability to perform transaction log backups, there is a Stored Procedure I found at SQLServerCentral.com. It's called sp_force_shrink_log and I find it quite useful. You can find it at http://www.sqlservercentral.com/scripts/contributions/26.asp after you register with them.|||Thank You very much Satya and loach for telling me the way to reduce the size , I am using SQL Server 2K .

DBCC SHRINKFILE and sp_force_shrink_log are helpful for reducing file size but for log deletion wht I have done is just deattach the database , remove log file and and attach db again , it refreshed and made DB log again with 1 MB. (I tested this with dummy Dbs.)

and my Databases are also connected through replication utility , so it restricts me to deattach db using in replication , so I have to turned replication off to deattach and attach db . currently I am looking into procedure or way so that without disabling replication make log refresh coz it will take long process to disable and then again follow through steps for having last settings to turn replication ON between 2 databases.

Thanks u All

Regards
Imran|||How about the backups schedule interval for LOG?

how to query against two instances of SQL Server in Query Pane

I have registered two SQL Server instances in Management Studio. I can see the tables and databases from both instances.

My problem is how to combine data from both the servers in a single query?

I tries the following

SELECT s.consortium_contact_data_request_id, s.copy_to_exchange_address_book,i.consortium_contact_data_request_id, i.copy_to_exchange_address_book

FROM sandbox.gbiv.dbo.consortium_contact_data_request s,

ivhost04.gbiv.dbo.consortium_contact_data_request i

WHERE (s.copy_to_exchange_address_book IS NOT NULL)

go

but received a message

Msg 7202, Level 11, State 2, Line 1

Could not find server 'sandbox' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.'

Though SANDBOX is registered and is viewable in the Object Explorer.

Thank you,

Piyush Varma

Piyush,

Adding the server in Management Studio's registered servers is not the same thing as creating a linked server. Registered servers is a way for you to organize your servers for easy access. In addition, while Object Explorer can connect to and display multiple servers, the context for a query window is a single server. In order to query two instances (servers) in a single query you need to link the servers. Look in Books Online under Linked Server for more information on establishing a link between two servers.

Cheers,

Dan

|||Thank you, Dan. I was able to add a linked server using following statement:

USE master;
GO
EXEC sp_addlinkedserver
'sandbox',
N'SQL Server'
GO

Then I was able to query from both the servers as below:

SELECT s.consortium_contact_data_request_id, s.copy_to_exchange_address_book,i.consortium_contact_data_request_id, i.copy_to_exchange_address_book
FROM sandbox.gbiv.dbo.consortium_contact_data_request s,
ivhost04.gbiv.dbo.consortium_contact_data_request i
WHERE (s.copy_to_exchange_address_book IS NOT NULL)
go

Thank you,

Piyush