change instance name?

Is it easy to rename an instance, or change an instance to default instance?
Basically I have an SQL 2000 Standard and MSDE installed on same machine,
and I would like to swap around their instances.
Thanks
cj
No, you cannot rename an instance or change default to named or vice versa. Backup, (re)install and
restore is what you have to do. some links that might be useful:
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scri...p?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Is it easy to rename an instance, or change an instance to default instance?
> Basically I have an SQL 2000 Standard and MSDE installed on same machine,
> and I would like to swap around their instances.
> Thanks
> cj
>
>
>
|||AFAIK, you can not change instance name, you have to install new
instance and dettach dbs from old instance and attach them to new
instance.
Also you can not change named instance to default instance.
look at
http://vyaskn.tripod.com/administration_faq.htm#q13
Regards
Amish Shah.
|||Ok, that's what I thought :-(
BTW thanks for the list, it is a helpful resource!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oh3zzoJKGHA.500@.TK2MSFTNGP15.phx.gbl...
> No, you cannot rename an instance or change default to named or vice
> versa. Backup, (re)install and restore is what you have to do. some links
> that might be useful:
> Have a look at this list compiled by Andrew Kelly:
> Moving DB's between Servers
> http://www.support.microsoft.com/?id=314546
> Moving SQL Server Databases to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> Using WITH MOVE in a Restore
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map logins to users
> http://www.dbmaint.com/SyncSql Logins.asp
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> Restoring a .mdf
> http://www.sqlservercentral.com/scri...p?scriptid=599
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
> news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
>

change instance name?

Is it easy to rename an instance, or change an instance to default instance?
Basically I have an SQL 2000 Standard and MSDE installed on same machine,
and I would like to swap around their instances.
Thanks
cjNo, you cannot rename an instance or change default to named or vice versa.
Backup, (re)install and
restore is what you have to do. some links that might be useful:
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scr...sp?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Is it easy to rename an instance, or change an instance to default instanc
e?
> Basically I have an SQL 2000 Standard and MSDE installed on same machine,
> and I would like to swap around their instances.
> Thanks
> cj
>
>
>|||AFAIK, you can not change instance name, you have to install new
instance and dettach dbs from old instance and attach them to new
instance.
Also you can not change named instance to default instance.
look at
http://vyaskn.tripod.com/administration_faq.htm#q13
Regards
Amish Shah.|||Ok, that's what I thought :-(
BTW thanks for the list, it is a helpful resource!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oh3zzoJKGHA.500@.TK2MSFTNGP15.phx.gbl...
> No, you cannot rename an instance or change default to named or vice
> versa. Backup, (re)install and restore is what you have to do. some links
> that might be useful:
> Have a look at this list compiled by Andrew Kelly:
> Moving DB's between Servers
> http://www.support.microsoft.com/?id=314546
> Moving SQL Server Databases to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> Using WITH MOVE in a Restore
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map logins to users
> http://www.dbmaint.com/SyncSql Logins.asp
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> Restoring a .mdf
> http://www.sqlservercentral.com/scr...sp?scriptid=599
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
> news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
>

change instance name?

Is it easy to rename an instance, or change an instance to default instance?
Basically I have an SQL 2000 Standard and MSDE installed on same machine,
and I would like to swap around their instances.
Thanks
cjNo, you cannot rename an instance or change default to named or vice versa. Backup, (re)install and
restore is what you have to do. some links that might be useful:
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Is it easy to rename an instance, or change an instance to default instance?
> Basically I have an SQL 2000 Standard and MSDE installed on same machine,
> and I would like to swap around their instances.
> Thanks
> cj
>
>
>|||AFAIK, you can not change instance name, you have to install new
instance and dettach dbs from old instance and attach them to new
instance.
Also you can not change named instance to default instance.
look at
http://vyaskn.tripod.com/administration_faq.htm#q13
Regards
Amish Shah.|||Ok, that's what I thought :-(
BTW thanks for the list, it is a helpful resource!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oh3zzoJKGHA.500@.TK2MSFTNGP15.phx.gbl...
> No, you cannot rename an instance or change default to named or vice
> versa. Backup, (re)install and restore is what you have to do. some links
> that might be useful:
> Have a look at this list compiled by Andrew Kelly:
> Moving DB's between Servers
> http://www.support.microsoft.com/?id=314546
> Moving SQL Server Databases to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> Using WITH MOVE in a Restore
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map logins to users
> http://www.dbmaint.com/SyncSql Logins.asp
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> Restoring a .mdf
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
> news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
>> Is it easy to rename an instance, or change an instance to default
>> instance?
>> Basically I have an SQL 2000 Standard and MSDE installed on same machine,
>> and I would like to swap around their instances.
>> Thanks
>> cj
>>
>>
>

change enterprise to standard edition

I need to change my current SQL 2000 enterprise editon to SQL 2000 standard
edtion. May I know what is the best way to do it?
andrew,
You need to uninstall enterprise and install standard. There's no short
cut I'm afraid. You can upgrade from standard to enterprise though.
Steps:
1) Back up all your dbs
2) Detach all your databases
3) Uninstall enterprise
4) Install standard
5) Attach the databases
If the attach fails, you can restore them.
Read this before doing anything:
INF: Effects of Moving a Database from SQL 2000 Enterprise Edition to
SQL 2000 Standard Edition
http://support.microsoft.com/?id=268361
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
andrew wrote:
> I need to change my current SQL 2000 enterprise editon to SQL 2000 standard
> edtion. May I know what is the best way to do it?
sql

change enterprise to standard edition

I need to change my current SQL 2000 enterprise editon to SQL 2000 standard
edtion. May I know what is the best way to do it?andrew,
You need to uninstall enterprise and install standard. There's no short
cut I'm afraid. You can upgrade from standard to enterprise though.
Steps:
1) Back up all your dbs
2) Detach all your databases
3) Uninstall enterprise
4) Install standard
5) Attach the databases
If the attach fails, you can restore them.
Read this before doing anything:
INF: Effects of Moving a Database from SQL 2000 Enterprise Edition to
SQL 2000 Standard Edition
http://support.microsoft.com/?id=268361
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
andrew wrote:
> I need to change my current SQL 2000 enterprise editon to SQL 2000 standard
> edtion. May I know what is the best way to do it?

change enterprise to standard edition

I need to change my current SQL 2000 enterprise editon to SQL 2000 standard
edtion. May I know what is the best way to do it?andrew,
You need to uninstall enterprise and install standard. There's no short
cut I'm afraid. You can upgrade from standard to enterprise though.
Steps:
1) Back up all your dbs
2) Detach all your databases
3) Uninstall enterprise
4) Install standard
5) Attach the databases
If the attach fails, you can restore them.
Read this before doing anything:
INF: Effects of Moving a Database from SQL 2000 Enterprise Edition to
SQL 2000 Standard Edition
http://support.microsoft.com/?id=268361
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
andrew wrote:
> I need to change my current SQL 2000 enterprise editon to SQL 2000 standar
d
> edtion. May I know what is the best way to do it?

change DB owner

I have several Production Databases on my SQL Server 2000 Standard Edition Server (mixed mode authentication) that I'd like to change DBO Permissions on. When I look at the Properties of the DB(s) from EM, the Database owner is showing up as a Windows NT USer who is no longer even with the company!!

Can I use the sp_changedbowner 'sa' command to change the database owner to sa without disrupting Production? Might sound like a dumb question.. but ya never know!!!!! Also, I had someone on another forum tell me that 'sa' as db owner is a bad idea but I don't know why??? can anyone elaborate on that???I am also interested in these questions (and so have posted to just put it back to the top of the list in the hope someone can answer - is that bad etiquette?)|||Yes, you can always change the database owner to sa. At least as far as I know, this is a good idea in almost every case.

As far as the person that thought it was a bad idea, I'd love to hear their reasoning... While I can concoct a case where it was a bad idea, it would take some considerable doing, and would probably never happen in "the real world".

-PatP|||Thank's Pat.

jpotucek, even though the user that own's the database has left, does their Windows account still exist? Does it cause you any issues?|||The only reason I ever had a person other than sa as dbo, was when I had a person who kept forgetting to put dbo in front of objects she created.

Tim S|||I don't manage the NT accounts here, just the logins on the SQL Server. The accounts still exist and they may even be disabled, i don't know. I'm going to change the DB Owner on all to sa and drop the users from my sql servers - then I know i won't have any problems!!!!

THank you one and all

Change connection port in SSMSE

Where can I chage the port for a connection to a SQL server using a non standard port. In SEM, I used the Client Network Utility, but I cannot find it anymore. There does not seem to be one for SSMSE. Any help is appreciated.

Thanks!

You can use the syntax MachineName,portnumber (without instance name) it should redirect you to the designated instance.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Challenge to all DBAs

I have a challenge for all DBAs. I am getting INCONSISTENT TIMINGS for Querry Results from a SQL Server 2000 Standard Edition on a HP Proliant 2 CPU Server with 4 GB RAM and SCSI DrivAFAe.

Our Database is 72 million records and have 8 columns. Most of them are indexed, which are used in the "Where…" clause. In addition to independent indexes, we also have a covering index for 3 most frequently searched fields.

My challenge is that out of the 7000 odd queries that hit the server with various search conditions in a Week, 5% of the queries return result in less than a minute.

The same query at a different Time or with a different Value, returns results inconsistently. For e.g. searching FirstName = "Anna"; LastName = "Williams" returns result in 0.01 sec. Searching for FirstName "Benjamin"; LastName = "Watson" returns in 5 minutes.

Any kind of help is welcome and will be highly appreciated.

- Santy

san.rely@.gmail.com
Note: Edited to fix white on white font.

Run the queries in query analyzer, have it show the execution plan, and look and see what is different, if anything.

cdosysmail with attachment

With cdosysmail I have gotten my code to work to programatically send emails
out as HTML.
Does anyone know how to tweak the standard CDO example to send the query
results as an attachment?
thanksI strongly, strongly, strongly urge you to use xp_smtp_sendmail instead of
cdo. See http://www.aspfaq.com/2403 (which incidentally does have a sample
of using CDO to send an attachment, but please read my first sentence
again).
A
"26point2er" <26point2er@.discussions.microsoft.com> wrote in message
news:D8D7CBB4-2174-4F79-B94D-18520BC6B060@.microsoft.com...
> With cdosysmail I have gotten my code to work to programatically send
> emails
> out as HTML.
> Does anyone know how to tweak the standard CDO example to send the query
> results as an attachment?
> thanks
>|||Thank you for the input. I will post my results here.
"Aaron Bertrand [SQL Server MVP]" wrote:

> I strongly, strongly, strongly urge you to use xp_smtp_sendmail instead of
> cdo. See http://www.aspfaq.com/2403 (which incidentally does have a sampl
e
> of using CDO to send an attachment, but please read my first sentence
> again).
> A
>
>
> "26point2er" <26point2er@.discussions.microsoft.com> wrote in message
> news:D8D7CBB4-2174-4F79-B94D-18520BC6B060@.microsoft.com...
>
>