Changing Computer Name

Here's my situation. We have a production SQL server 2000 with about 18
different databases running on it. The lease is up on the server and now i
must roll everything over to the new server. What i need to do is join the
domain with the new server using a different ip address and name than the
current SQL server, get everything installed, test it, then take both servers
down, rename the new server and change it's ip address to what the old one
was, and put it into production. Does anyone know how painful this will be
and what obstacles i need to look out for? Also, if there's a step by step
guide somewhere that'd be pretty great too. The old server has windows 2000
on it, the new server will have windows 2003. Thanks.
See answer to "Moving SQL Server"
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.
|||Double Post. Answered in "Moving SQL Server".
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.
|||"get everything installed"
Install SQL Server, service packs, restore the databases...
"test it"
How are you going to test it? Can you just leave the new server name in
place and change the connection string to connect to the new computer? I
suppose not, or you would not have the question. Anyway, this is the
easiest option.
"rename the new server"
When the box is restarted SQL Server will display an error message on start.
You will have to pop in the SQL Server CD and choose install. This step
will not actually install SQL Server...it will just fix up some registry
entries.
If you have jobs defined on your server you might have to update the value
as stored within the originating_server column.
SELECT originating_server FROM msdb..sysjobs
If @.@.servername is incorrect you will have to drop and add server using the
stored procedures listed below:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName', 'local'
Keith
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.

Changing Computer Name

Here's my situation. We have a production SQL server 2000 with about 18
different databases running on it. The lease is up on the server and now i
must roll everything over to the new server. What i need to do is join the
domain with the new server using a different ip address and name than the
current SQL server, get everything installed, test it, then take both server
s
down, rename the new server and change it's ip address to what the old one
was, and put it into production. Does anyone know how painful this will be
and what obstacles i need to look out for? Also, if there's a step by step
guide somewhere that'd be pretty great too. The old server has windows 2000
on it, the new server will have windows 2003. Thanks.See answer to "Moving SQL Server"
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.|||Double Post. Answered in "Moving SQL Server".
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.|||"get everything installed"
Install SQL Server, service packs, restore the databases...
"test it"
How are you going to test it? Can you just leave the new server name in
place and change the connection string to connect to the new computer? I
suppose not, or you would not have the question. Anyway, this is the
easiest option.
"rename the new server"
When the box is restarted SQL Server will display an error message on start.
You will have to pop in the SQL Server CD and choose install. This step
will not actually install SQL Server...it will just fix up some registry
entries.
If you have jobs defined on your server you might have to update the value
as stored within the originating_server column.
SELECT originating_server FROM msdb..sysjobs
If @.@.servername is incorrect you will have to drop and add server using the
stored procedures listed below:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName', 'local'
Keith
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.

Changing Computer Name

Here's my situation. We have a production SQL server 2000 with about 18
different databases running on it. The lease is up on the server and now i
must roll everything over to the new server. What i need to do is join the
domain with the new server using a different ip address and name than the
current SQL server, get everything installed, test it, then take both servers
down, rename the new server and change it's ip address to what the old one
was, and put it into production. Does anyone know how painful this will be
and what obstacles i need to look out for? Also, if there's a step by step
guide somewhere that'd be pretty great too. The old server has windows 2000
on it, the new server will have windows 2003. Thanks.See answer to "Moving SQL Server"
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.|||Double Post. Answered in "Moving SQL Server".
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.|||"get everything installed"
Install SQL Server, service packs, restore the databases...
"test it"
How are you going to test it? Can you just leave the new server name in
place and change the connection string to connect to the new computer? I
suppose not, or you would not have the question. Anyway, this is the
easiest option.
"rename the new server"
When the box is restarted SQL Server will display an error message on start.
You will have to pop in the SQL Server CD and choose install. This step
will not actually install SQL Server...it will just fix up some registry
entries.
If you have jobs defined on your server you might have to update the value
as stored within the originating_server column.
SELECT originating_server FROM msdb..sysjobs
If @.@.servername is incorrect you will have to drop and add server using the
stored procedures listed below:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName', 'local'
--
Keith
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.

Changing a column to an Identity column. Please Help...

I have a situation where we converted over a large database. The database
had one table that used autonumber(Access) that we need to preserve the IDs
upon conversion for a table that refereneces the field. So during
conversion I turned off the identity and it brought over the ID column with
the correct values...but now I find that I can't turn the Identity back on
for that column!!!!!
I am in big trouble here...is there some way to take an existing int, no
null column into an identity column'
Please help.
Thanks,
Ron"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:%23cWhTLZBGHA.3580@.TK2MSFTNGP11.phx.gbl...
> I have a situation where we converted over a large database. The database
> had one table that used autonumber(Access) that we need to preserve the
> IDs upon conversion for a table that refereneces the field. So during
> conversion I turned off the identity and it brought over the ID column
> with the correct values...but now I find that I can't turn the Identity
> back on for that column!!!!!
> I am in big trouble here...is there some way to take an existing int, no
> null column into an identity column'
> Please help.
> Thanks,
> Ron
First of all, you didn't need to turn it off, you could have used SET
IDENTITY_INSERT.
How did you turn it off? With Enterprise Manager?
You should be able to modify the column to Identity with EM.|||Hi
Did you use "SET IDENTITY_INSERT ON" then if you are still using the same
session then you can use SET IDENTITY_INSERT OFF, other/new sessions will no
t
be affected.
If you removed the IDENTITY property, then you will have to create a new
column with the identity property and move the data into it using the above
commands to allow insertion.
John
"RSH" wrote:

> I have a situation where we converted over a large database. The database
> had one table that used autonumber(Access) that we need to preserve the ID
s
> upon conversion for a table that refereneces the field. So during
> conversion I turned off the identity and it brought over the ID column wit
h
> the correct values...but now I find that I can't turn the Identity back on
> for that column!!!!!
> I am in big trouble here...is there some way to take an existing int, no
> null column into an identity column'
> Please help.
> Thanks,
> Ron
>
>|||I tried this but I still get the ERROR:
Msg 8102, Level 16, State 1, Line 2
Cannot update identity column 'ID'.
SET IDENTITY_INSERT [TESTACH].[dbo].[DirectDeposit] ON
GO
UPDATE [TESTACH].[dbo].[DirectDeposit] SET ID = 73986 WHERE DirectDepositID
= 73986
GO
What am I doing wrong'
THANKS!
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:uHwK7ZZBGHA.2840@.TK2MSFTNGP12.phx.gbl...
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:%23cWhTLZBGHA.3580@.TK2MSFTNGP11.phx.gbl...
> First of all, you didn't need to turn it off, you could have used SET
> IDENTITY_INSERT.
> How did you turn it off? With Enterprise Manager?
> You should be able to modify the column to Identity with EM.
>sql

Changing a column to an Identity column. Please Help...

RSH wrote:
> I have a situation where we converted over a large database. The database
> had one table that used autonumber(Access) that we need to preserve the ID
s
> upon conversion for a table that refereneces the field. So during
> conversion I turned off the identity and it brought over the ID column wit
h
> the correct values...but now I find that I can't turn the Identity back on
> for that column!!!!!
> I am in big trouble here...is there some way to take an existing int, no
> null column into an identity column'
> Please help.
> Thanks,
> Ron
You can't change this property for an existing column and the IDENTITY
value can only be set on INSERT. What this means is that you'll have to
create a new table and INSERT the existing data into it using the SET
IDENTITY_INSERT option. You can then drop the old table and rename the
new one.
If you modify the table in Enterprise Manager (SQL Server 2000) or
Management Studio (2005) then it will make the change for you or will
generate a script to do it.
David Portas
SQL Server MVP
--Okay next question...is there a simple query that will allow me to do
something like...
INSERT * FROM ACHMaster INTO ACH
?
I need someway to perform the insert from one table to the other.
Thanks for all of your help!!!!
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1135105250.447899.52020@.g43g2000cwa.googlegroups.com...
> RSH wrote:
> You can't change this property for an existing column and the IDENTITY
> value can only be set on INSERT. What this means is that you'll have to
> create a new table and INSERT the existing data into it using the SET
> IDENTITY_INSERT option. You can then drop the old table and rename the
> new one.
> If you modify the table in Enterprise Manager (SQL Server 2000) or
> Management Studio (2005) then it will make the change for you or will
> generate a script to do it.
> --
> David Portas
> SQL Server MVP
> --
>|||INSERT ACH SELECT * FROM ACHMaster
Note: SELECT * shouldn't be used for production queries. For a one-time
fix, however, it should work, provided the tables were created with the
columns in the same order. It is always best to spell out the column list,
both for the INSERT and the SELECT.
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:etKU9oZBGHA.2476@.TK2MSFTNGP10.phx.gbl...
> Okay next question...is there a simple query that will allow me to do
> something like...
> INSERT * FROM ACHMaster INTO ACH
> ?
> I need someway to perform the insert from one table to the other.
> Thanks for all of your help!!!!
>
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1135105250.447899.52020@.g43g2000cwa.googlegroups.com...
>|||THANKS ALOT!!!!!!
--Ron
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uybyzPbBGHA.3292@.TK2MSFTNGP09.phx.gbl...
> INSERT ACH SELECT * FROM ACHMaster
> Note: SELECT * shouldn't be used for production queries. For a one-time
> fix, however, it should work, provided the tables were created with the
> columns in the same order. It is always best to spell out the column
> list, both for the INSERT and the SELECT.
>
> "RSH" <way_beyond_oops@.yahoo.com> wrote in message
> news:etKU9oZBGHA.2476@.TK2MSFTNGP10.phx.gbl...
>

Change Password in XP and Lose Servers in Enterprise

I have a frustrating situation but I have been unable to
find help elsewhere on this topic. Each time I change my
password at work on my Windows XP Professional machine I
lose all the servers that I had registered in SQL
Enterprise Manager. This is very frustrating because I
have to change my password every 45 days and I have over
20 servers registered. Any ideas on how to fix this to
not lose my registrations? Please help.
thanks,
allen...It happened few times to few developers at my company. After forced to
change the password, all registered servers were gone. However we haven't
seen this come back for about 8 - 10 months. Probably a windows update or
SQL update fixed it?
"Allen" <AMora@.tradestation.com> wrote in message
news:0d6a01c36e45$e5b48e40$a401280a@.phx.gbl...
> I have a frustrating situation but I have been unable to
> find help elsewhere on this topic. Each time I change my
> password at work on my Windows XP Professional machine I
> lose all the servers that I had registered in SQL
> Enterprise Manager. This is very frustrating because I
> have to change my password every 45 days and I have over
> 20 servers registered. Any ideas on how to fix this to
> not lose my registrations? Please help.
> thanks,
> allen...|||I've never lost any of my registrations this way. However, perhaps if you
use SQL authentication instead of Windows authentication for your
registration properties?
"Allen" <AMora@.tradestation.com> wrote in message
news:0d6a01c36e45$e5b48e40$a401280a@.phx.gbl...
> I have a frustrating situation but I have been unable to
> find help elsewhere on this topic. Each time I change my
> password at work on my Windows XP Professional machine I
> lose all the servers that I had registered in SQL
> Enterprise Manager. This is very frustrating because I
> have to change my password every 45 days and I have over
> 20 servers registered. Any ideas on how to fix this to
> not lose my registrations? Please help.
> thanks,
> allen...|||Allen,
Is this the issue?
FIX: Registered Remote Servers Disappear from SQL Enterprise Manager in
Windows XP When Non-Domain User Password is Changed
http://support.microsoft.com/default.aspx?scid=kb;en-us;323280
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Allen" <AMora@.tradestation.com> wrote in message
news:0d6a01c36e45$e5b48e40$a401280a@.phx.gbl...
> I have a frustrating situation but I have been unable to
> find help elsewhere on this topic. Each time I change my
> password at work on my Windows XP Professional machine I
> lose all the servers that I had registered in SQL
> Enterprise Manager. This is very frustrating because I
> have to change my password every 45 days and I have over
> 20 servers registered. Any ideas on how to fix this to
> not lose my registrations? Please help.
> thanks,
> allen...|||btw, we are using SQL logins, not Windows ...
"Flicker" <hthan@.superioraccess.com> wrote in message
news:%23s3IRckbDHA.1600@.TK2MSFTNGP09.phx.gbl...
> It happened few times to few developers at my company. After forced to
> change the password, all registered servers were gone. However we haven't
> seen this come back for about 8 - 10 months. Probably a windows update or
> SQL update fixed it?
> "Allen" <AMora@.tradestation.com> wrote in message
> news:0d6a01c36e45$e5b48e40$a401280a@.phx.gbl...
> > I have a frustrating situation but I have been unable to
> > find help elsewhere on this topic. Each time I change my
> > password at work on my Windows XP Professional machine I
> > lose all the servers that I had registered in SQL
> > Enterprise Manager. This is very frustrating because I
> > have to change my password every 45 days and I have over
> > 20 servers registered. Any ideas on how to fix this to
> > not lose my registrations? Please help.
> >
> > thanks,
> > allen...
>