Rename server names in Enterprise Manager?

Hi there.

Sorry 'bout my bad english.
Is there any possibility, to change the current server name from the stuctureview in the Enterprise Manger. I'm registering a lot of online-servers from my customers. The remote servername is fixed of course, but I want to change it local, to explain it a little bit better
(e.g. "W2KSQLSRV" -> "CustomerXY")

Can someone give me a hint, please?

Thanx.Not mentioned the version of SQL server used>

If its SQL 2K then you can use SP_DROPSERVER & SP_ADDSERVER which will take affect at the restart of SQL services.

If its version 7 then you've to run the setup again to take new changes affected.

Refer to books online about Renaming Servers topic.|||Originally posted by LuckyLights
Hi there.

Sorry 'bout my bad english.
Is there any possibility, to change the current server name from the stuctureview in the Enterprise Manger. I'm registering a lot of online-servers from my customers. The remote servername is fixed of course, but I want to change it local, to explain it a little bit better
(e.g. "W2KSQLSRV" -> "CustomerXY")

Can someone give me a hint, please?

Thanx. It sounds to me like you want to register a server, but have a completely different name show up on the screen in the Server collection. IF I understood what you wanted correctly, according to How to register a server (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/howtosql/ht_servpem_7jua.asp), that can't be done.

-PatP

Rename Server

We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
Edition. We need to change the server name and IP address. Will there be
any problem of accessing the existing database? Thanks.Diane,
You'll need to update the connection information to the SQL Server in your
code as well to reflect the name and IP change i.e., connection strings,
DSNs, etc...
HTH
Jerry
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
>|||Thanks very much for your prompt response. Exactly what and where do I need
to change in SQL code? I know how to change the DNS entry. Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OsbTtdsvFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Diane,
> You'll need to update the connection information to the SQL Server in your
> code as well to reflect the name and IP change i.e., connection strings,
> DSNs, etc...
> HTH
> Jerry
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>|||DSNs, ADO/ADO.NET connection strings and any other connection sources for
you applications. Touch base with your developers prior to making this
change. Also, if possible test the applications in a testing environment
first before making the changes in the production environment. Consider
having a roll-back strategy in place "just in case" something doesn't go as
expected.
HTH
Jerry
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:utkVW7svFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Thanks very much for your prompt response. Exactly what and where do I
> need to change in SQL code? I know how to change the DNS entry. Thanks.
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OsbTtdsvFHA.3252@.TK2MSFTNGP10.phx.gbl...
>|||"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
>
After you rename the computer, open up Query Analyzer so that you can reset
the SQL Server's reference. It won't know who it is anymore. ;-)
--
USE master
GO
-- Drop the old computer name
IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
BEGIN
SET NOCOUNT ON
exec sp_dropserver @.server = 'OldName'
END
-- Add the new computer name
IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname =
'NewName')
BEGIN
SET NOCOUNT ON
exec sp_addserver @.server = 'NewName', @.local = 'local'
END
Client applications that currently point to the old computer name/IP address
will need to be modified to point at the new computer name/ip address.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OaW%23LGtvFHA.1276@.TK2MSFTNGP10.phx.gbl...
> DSNs, ADO/ADO.NET connection strings and any other connection sources for
> you applications. Touch base with your developers prior to making this
> change. Also, if possible test the applications in a testing environment
> first before making the changes in the production environment. Consider
> having a roll-back strategy in place "just in case" something doesn't go
> as expected.
> HTH
> Jerry
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:utkVW7svFHA.3500@.TK2MSFTNGP09.phx.gbl...
>|||Thanks very much.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> After you rename the computer, open up Query Analyzer so that you can
> reset the SQL Server's reference. It won't know who it is anymore. ;-)
> --
> USE master
> GO
> -- Drop the old computer name
> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
> BEGIN
> SET NOCOUNT ON
> exec sp_dropserver @.server = 'OldName'
> END
> -- Add the new computer name
> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname =
> 'NewName')
> BEGIN
> SET NOCOUNT ON
> exec sp_addserver @.server = 'NewName', @.local = 'local'
> END
>
> Client applications that currently point to the old computer name/IP
> address will need to be modified to point at the new computer name/ip
> address.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
And to add to the other replies, if you have replication enabled on this
server or remote logins then you will find that you cannot use sp_dropserver
until you disable replication.
http://msdn.microsoft.com/library/d...nstall_5r8f.asp
I had to go through this myself 2 days ago
Dan|||http://www.karaszi.com/SQLServer/in...server_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx
.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
Edition. We need to
> change the server name and IP address. Will there be any problem of acces
sing the existing
> database? Thanks.
>|||Thank you very much for your input.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ex%23ngH2vFHA.2504@.tk2msftngp13.phx.gbl...
> http://www.karaszi.com/SQLServer/in...server_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>

Rename Server

We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
Edition. We need to change the server name and IP address. Will there be
any problem of accessing the existing database? Thanks.
Diane,
You'll need to update the connection information to the SQL Server in your
code as well to reflect the name and IP change i.e., connection strings,
DSNs, etc...
HTH
Jerry
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
>
|||Thanks very much for your prompt response. Exactly what and where do I need
to change in SQL code? I know how to change the DNS entry. Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OsbTtdsvFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Diane,
> You'll need to update the connection information to the SQL Server in your
> code as well to reflect the name and IP change i.e., connection strings,
> DSNs, etc...
> HTH
> Jerry
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>
|||DSNs, ADO/ADO.NET connection strings and any other connection sources for
you applications. Touch base with your developers prior to making this
change. Also, if possible test the applications in a testing environment
first before making the changes in the production environment. Consider
having a roll-back strategy in place "just in case" something doesn't go as
expected.
HTH
Jerry
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:utkVW7svFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Thanks very much for your prompt response. Exactly what and where do I
> need to change in SQL code? I know how to change the DNS entry. Thanks.
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OsbTtdsvFHA.3252@.TK2MSFTNGP10.phx.gbl...
>
|||"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
>
After you rename the computer, open up Query Analyzer so that you can reset
the SQL Server's reference. It won't know who it is anymore. ;-)
USE master
GO
-- Drop the old computer name
IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
BEGIN
SET NOCOUNT ON
exec sp_dropserver @.server = 'OldName'
END
-- Add the new computer name
IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname =
'NewName')
BEGIN
SET NOCOUNT ON
exec sp_addserver @.server = 'NewName', @.local = 'local'
END
Client applications that currently point to the old computer name/IP address
will need to be modified to point at the new computer name/ip address.
Rick Sawtell
MCT, MCSD, MCDBA
|||Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OaW%23LGtvFHA.1276@.TK2MSFTNGP10.phx.gbl...
> DSNs, ADO/ADO.NET connection strings and any other connection sources for
> you applications. Touch base with your developers prior to making this
> change. Also, if possible test the applications in a testing environment
> first before making the changes in the production environment. Consider
> having a roll-back strategy in place "just in case" something doesn't go
> as expected.
> HTH
> Jerry
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:utkVW7svFHA.3500@.TK2MSFTNGP09.phx.gbl...
>
|||Thanks very much.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> After you rename the computer, open up Query Analyzer so that you can
> reset the SQL Server's reference. It won't know who it is anymore. ;-)
> --
> USE master
> GO
> -- Drop the old computer name
> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
> BEGIN
> SET NOCOUNT ON
> exec sp_dropserver @.server = 'OldName'
> END
> -- Add the new computer name
> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname =
> 'NewName')
> BEGIN
> SET NOCOUNT ON
> exec sp_addserver @.server = 'NewName', @.local = 'local'
> END
>
> Client applications that currently point to the old computer name/IP
> address will need to be modified to point at the new computer name/ip
> address.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
|||"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
And to add to the other replies, if you have replication enabled on this
server or remote logins then you will find that you cannot use sp_dropserver
until you disable replication.
http://msdn.microsoft.com/library/de...stall_5r8f.asp
I had to go through this myself 2 days ago
Dan
|||http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard Edition. We need to
> change the server name and IP address. Will there be any problem of accessing the existing
> database? Thanks.
>
|||Thank you very much for your input.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ex%23ngH2vFHA.2504@.tk2msftngp13.phx.gbl...
> http://www.karaszi.com/SQLServer/inf...erver_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>

Rename Server

We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
Edition. We need to change the server name and IP address. Will there be
any problem of accessing the existing database? Thanks.Diane,
You'll need to update the connection information to the SQL Server in your
code as well to reflect the name and IP change i.e., connection strings,
DSNs, etc...
HTH
Jerry
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
>|||Thanks very much for your prompt response. Exactly what and where do I need
to change in SQL code? I know how to change the DNS entry. Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OsbTtdsvFHA.3252@.TK2MSFTNGP10.phx.gbl...
> Diane,
> You'll need to update the connection information to the SQL Server in your
> code as well to reflect the name and IP change i.e., connection strings,
> DSNs, etc...
> HTH
> Jerry
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
>>
>|||DSNs, ADO/ADO.NET connection strings and any other connection sources for
you applications. Touch base with your developers prior to making this
change. Also, if possible test the applications in a testing environment
first before making the changes in the production environment. Consider
having a roll-back strategy in place "just in case" something doesn't go as
expected.
HTH
Jerry
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:utkVW7svFHA.3500@.TK2MSFTNGP09.phx.gbl...
> Thanks very much for your prompt response. Exactly what and where do I
> need to change in SQL code? I know how to change the DNS entry. Thanks.
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OsbTtdsvFHA.3252@.TK2MSFTNGP10.phx.gbl...
>> Diane,
>> You'll need to update the connection information to the SQL Server in
>> your code as well to reflect the name and IP change i.e., connection
>> strings, DSNs, etc...
>> HTH
>> Jerry
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003
>> Standard Edition. We need to change the server name and IP address.
>> Will there be any problem of accessing the existing database? Thanks.
>>
>>
>|||"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
>
After you rename the computer, open up Query Analyzer so that you can reset
the SQL Server's reference. It won't know who it is anymore. ;-)
--
USE master
GO
-- Drop the old computer name
IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
BEGIN
SET NOCOUNT ON
exec sp_dropserver @.server = 'OldName'
END
-- Add the new computer name
IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname ='NewName')
BEGIN
SET NOCOUNT ON
exec sp_addserver @.server = 'NewName', @.local = 'local'
END
Client applications that currently point to the old computer name/IP address
will need to be modified to point at the new computer name/ip address.
Rick Sawtell
MCT, MCSD, MCDBA|||http://support.microsoft.com/kb/303774/
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
>|||Thanks.
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OaW%23LGtvFHA.1276@.TK2MSFTNGP10.phx.gbl...
> DSNs, ADO/ADO.NET connection strings and any other connection sources for
> you applications. Touch base with your developers prior to making this
> change. Also, if possible test the applications in a testing environment
> first before making the changes in the production environment. Consider
> having a roll-back strategy in place "just in case" something doesn't go
> as expected.
> HTH
> Jerry
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:utkVW7svFHA.3500@.TK2MSFTNGP09.phx.gbl...
>> Thanks very much for your prompt response. Exactly what and where do I
>> need to change in SQL code? I know how to change the DNS entry. Thanks.
>>
>> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
>> news:OsbTtdsvFHA.3252@.TK2MSFTNGP10.phx.gbl...
>> Diane,
>> You'll need to update the connection information to the SQL Server in
>> your code as well to reflect the name and IP change i.e., connection
>> strings, DSNs, etc...
>> HTH
>> Jerry
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003
>> Standard Edition. We need to change the server name and IP address.
>> Will there be any problem of accessing the existing database? Thanks.
>>
>>
>>
>|||Thanks very much.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
>>
> After you rename the computer, open up Query Analyzer so that you can
> reset the SQL Server's reference. It won't know who it is anymore. ;-)
> --
> USE master
> GO
> -- Drop the old computer name
> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
> BEGIN
> SET NOCOUNT ON
> exec sp_dropserver @.server = 'OldName'
> END
> -- Add the new computer name
> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname => 'NewName')
> BEGIN
> SET NOCOUNT ON
> exec sp_addserver @.server = 'NewName', @.local = 'local'
> END
>
> Client applications that currently point to the old computer name/IP
> address will need to be modified to point at the new computer name/ip
> address.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Thanks very much.
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:O32s4MtvFHA.1276@.TK2MSFTNGP10.phx.gbl...
> http://support.microsoft.com/kb/303774/
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
>>
>|||"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We need to change the server name and IP address. Will there be
> any problem of accessing the existing database? Thanks.
And to add to the other replies, if you have replication enabled on this
server or remote logins then you will find that you cannot use sp_dropserver
until you disable replication.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
I had to go through this myself 2 days ago :)
Dan|||http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard Edition. We need to
> change the server name and IP address. Will there be any problem of accessing the existing
> database? Thanks.
>|||Thank you very much for your input. I appreciate it very much.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:OLUnfy1vFHA.2880@.TK2MSFTNGP12.phx.gbl...
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
> And to add to the other replies, if you have replication enabled on this
> server or remote logins then you will find that you cannot use
> sp_dropserver until you disable replication.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_afterinstall_5r8f.asp
> I had to go through this myself 2 days ago :)
> Dan
>|||Thank you very much for your input.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ex%23ngH2vFHA.2504@.tk2msftngp13.phx.gbl...
> http://www.karaszi.com/SQLServer/info_change_server_name.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
>>
>|||Hi Rick,
Your instructions work. I appreciated that you took the time to write me
the detail instructions. I have a question and I don't know if it is
related to renaming the server or not. I got this error message when I
typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
Server: Msg 2501, Level 16, State 45, Line 1
Could not find a table or object named 'SMART_DATA'. Check sysobjects.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Do you have any suggestions? Thanks.
"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard
>> Edition. We need to change the server name and IP address. Will there
>> be any problem of accessing the existing database? Thanks.
>>
> After you rename the computer, open up Query Analyzer so that you can
> reset the SQL Server's reference. It won't know who it is anymore. ;-)
> --
> USE master
> GO
> -- Drop the old computer name
> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
> BEGIN
> SET NOCOUNT ON
> exec sp_dropserver @.server = 'OldName'
> END
> -- Add the new computer name
> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname => 'NewName')
> BEGIN
> SET NOCOUNT ON
> exec sp_addserver @.server = 'NewName', @.local = 'local'
> END
>
> Client applications that currently point to the old computer name/IP
> address will need to be modified to point at the new computer name/ip
> address.
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>|||Are you in the right database when running the command? Can you check if the object exists in
sysobjects? Also, who is the owner of the object?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
> Hi Rick,
> Your instructions work. I appreciated that you took the time to write me the detail instructions.
> I have a question and I don't know if it is related to renaming the server or not. I got this
> error message when I typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
> Server: Msg 2501, Level 16, State 45, Line 1
> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
> DBCC execution completed. If DBCC printed error messages, contact your system administrator.
> Do you have any suggestions? Thanks.
> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
> news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard Edition. We need to
>> change the server name and IP address. Will there be any problem of accessing the existing
>> database? Thanks.
>>
>> After you rename the computer, open up Query Analyzer so that you can reset the SQL Server's
>> reference. It won't know who it is anymore. ;-)
>> --
>> USE master
>> GO
>> -- Drop the old computer name
>> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_dropserver @.server = 'OldName'
>> END
>> -- Add the new computer name
>> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'NewName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_addserver @.server = 'NewName', @.local = 'local'
>> END
>>
>> Client applications that currently point to the old computer name/IP address will need to be
>> modified to point at the new computer name/ip address.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>|||Thanks for your prompt response, Tibor.
Yes, I am in the right dabase when running the command. From Query
Analyzer, I selected Query, Change Database and point to Smart_Data dabase
and ran the command.
I see the object exists in sysobjects. I went to Tools, Object Search. I
saw Smart_Data under db_name for sysobjects. The owner of the object is
dbo.
Tibor, I think I made a BIG mistake by not following Rick's instructions.
Rick's instructions said to "USE master". But, I changed "master" to my
database name "smart_data". So, for every master, I changed to smart_data.
Maybe that was the reason that I got the error message when I typed the
command DBCC SHOWCONTIG. Do you know if I still can rerun Rick's
instructions by using the master table? Do you have any other suggestions?
Thanks.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:epg1vjh0FHA.2752@.TK2MSFTNGP12.phx.gbl...
> Are you in the right database when running the command? Can you check if
> the object exists in sysobjects? Also, who is the owner of the object?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write me
>> the detail instructions. I have a question and I don't know if it is
>> related to renaming the server or not. I got this error message when I
>> typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
>> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>> news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003
>> Standard Edition. We need to change the server name and IP address.
>> Will there be any problem of accessing the existing database? Thanks.
>>
>> After you rename the computer, open up Query Analyzer so that you can
>> reset the SQL Server's reference. It won't know who it is anymore. ;-)
>> --
>> USE master
>> GO
>> -- Drop the old computer name
>> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname =>> 'OldName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_dropserver @.server = 'OldName'
>> END
>> -- Add the new computer name
>> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname =>> 'NewName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_addserver @.server = 'NewName', @.local = 'local'
>> END
>>
>> Client applications that currently point to the old computer name/IP
>> address will need to be modified to point at the new computer name/ip
>> address.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>|||You don't really have to be in master when executing sp_Dropserver and sp_addserver, so that should
not be the cause of your problem. I've never seen a case where SHOWCONTIG doesn't work if the table
exists and there isn't an object owner problem. I'm out of ideas, I'm afraid...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:eoqJQEm0FHA.3780@.TK2MSFTNGP12.phx.gbl...
> Thanks for your prompt response, Tibor.
> Yes, I am in the right dabase when running the command. From Query Analyzer, I selected Query,
> Change Database and point to Smart_Data dabase and ran the command.
> I see the object exists in sysobjects. I went to Tools, Object Search. I saw Smart_Data under
> db_name for sysobjects. The owner of the object is dbo.
> Tibor, I think I made a BIG mistake by not following Rick's instructions. Rick's instructions said
> to "USE master". But, I changed "master" to my database name "smart_data". So, for every master,
> I changed to smart_data. Maybe that was the reason that I got the error message when I typed the
> command DBCC SHOWCONTIG. Do you know if I still can rerun Rick's instructions by using the master
> table? Do you have any other suggestions? Thanks.
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:epg1vjh0FHA.2752@.TK2MSFTNGP12.phx.gbl...
>> Are you in the right database when running the command? Can you check if the object exists in
>> sysobjects? Also, who is the owner of the object?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write me the detail
>> instructions. I have a question and I don't know if it is related to renaming the server or not.
>> I got this error message when I typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query
>> Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your system administrator.
>> Do you have any suggestions? Thanks.
>> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>> news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003 Standard Edition. We need to
>> change the server name and IP address. Will there be any problem of accessing the existing
>> database? Thanks.
>>
>> After you rename the computer, open up Query Analyzer so that you can reset the SQL Server's
>> reference. It won't know who it is anymore. ;-)
>> --
>> USE master
>> GO
>> -- Drop the old computer name
>> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'OldName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_dropserver @.server = 'OldName'
>> END
>> -- Add the new computer name
>> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname = 'NewName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_addserver @.server = 'NewName', @.local = 'local'
>> END
>>
>> Client applications that currently point to the old computer name/IP address will need to be
>> modified to point at the new computer name/ip address.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>|||"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
> Hi Rick,
> Your instructions work. I appreciated that you took the time to write me
> the detail instructions. I have a question and I don't know if it is
> related to renaming the server or not. I got this error message when I
> typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
> Server: Msg 2501, Level 16, State 45, Line 1
> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Do you have any suggestions? Thanks.
In your other replies to this you have Smart_Data as the table name. Have
you tried
DBCC SHOWCONTIG (Smart_Data)
It's possible that you have your codepage set to a case-sensitive one, in
which case you must use the correct case for any object name.
Dan|||Thanks for your response, Tibor.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23USAzov0FHA.904@.tk2msftngp13.phx.gbl...
> You don't really have to be in master when executing sp_Dropserver and
> sp_addserver, so that should not be the cause of your problem. I've never
> seen a case where SHOWCONTIG doesn't work if the table exists and there
> isn't an object owner problem. I'm out of ideas, I'm afraid...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:eoqJQEm0FHA.3780@.TK2MSFTNGP12.phx.gbl...
>> Thanks for your prompt response, Tibor.
>> Yes, I am in the right dabase when running the command. From Query
>> Analyzer, I selected Query, Change Database and point to Smart_Data
>> dabase and ran the command.
>> I see the object exists in sysobjects. I went to Tools, Object Search.
>> I saw Smart_Data under db_name for sysobjects. The owner of the object
>> is dbo.
>> Tibor, I think I made a BIG mistake by not following Rick's instructions.
>> Rick's instructions said to "USE master". But, I changed "master" to my
>> database name "smart_data". So, for every master, I changed to
>> smart_data. Maybe that was the reason that I got the error message when I
>> typed the command DBCC SHOWCONTIG. Do you know if I still can rerun
>> Rick's instructions by using the master table? Do you have any other
>> suggestions? Thanks.
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:epg1vjh0FHA.2752@.TK2MSFTNGP12.phx.gbl...
>> Are you in the right database when running the command? Can you check if
>> the object exists in sysobjects? Also, who is the owner of the object?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write
>> me the detail instructions. I have a question and I don't know if it is
>> related to renaming the server or not. I got this error message when I
>> typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
>> "Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
>> news:eySwWJtvFHA.460@.TK2MSFTNGP15.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%236bzDJsvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>> We are running MS SQL 2000 Enterprise Edition under Windows 2003
>> Standard Edition. We need to change the server name and IP address.
>> Will there be any problem of accessing the existing database?
>> Thanks.
>>
>> After you rename the computer, open up Query Analyzer so that you can
>> reset the SQL Server's reference. It won't know who it is anymore.
>> ;-)
>> --
>> USE master
>> GO
>> -- Drop the old computer name
>> IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname =>> 'OldName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_dropserver @.server = 'OldName'
>> END
>> -- Add the new computer name
>> IF NOT EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname =>> 'NewName')
>> BEGIN
>> SET NOCOUNT ON
>> exec sp_addserver @.server = 'NewName', @.local = 'local'
>> END
>>
>> Client applications that currently point to the old computer name/IP
>> address will need to be modified to point at the new computer name/ip
>> address.
>>
>> Rick Sawtell
>> MCT, MCSD, MCDBA
>>
>>
>>
>|||Thanks for your suggestions, Dan.
I have tried Smart_Data, SMART_DATA, smart_data. The database name under
Enterprise Manager is SMART_DATA. Do you have any other suggestions?
Thanks.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:OYWLCdx0FHA.3956@.TK2MSFTNGP09.phx.gbl...
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write me
>> the detail instructions. I have a question and I don't know if it is
>> related to renaming the server or not. I got this error message when I
>> typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
> In your other replies to this you have Smart_Data as the table name. Have
> you tried
> DBCC SHOWCONTIG (Smart_Data)
> It's possible that you have your codepage set to a case-sensitive one, in
> which case you must use the correct case for any object name.
> Dan
>|||> The database name under
> Enterprise Manager is SMART_DATA.
Database name? Earlier you said that the name of the table is SMART_DATA.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:uDcBs9y0FHA.2888@.TK2MSFTNGP10.phx.gbl...
> Thanks for your suggestions, Dan.
> I have tried Smart_Data, SMART_DATA, smart_data. The database name under
> Enterprise Manager is SMART_DATA. Do you have any other suggestions?
> Thanks.
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:OYWLCdx0FHA.3956@.TK2MSFTNGP09.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write me
>> the detail instructions. I have a question and I don't know if it is
>> related to renaming the server or not. I got this error message when I
>> typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
>> In your other replies to this you have Smart_Data as the table name. Have
>> you tried
>> DBCC SHOWCONTIG (Smart_Data)
>> It's possible that you have your codepage set to a case-sensitive one, in
>> which case you must use the correct case for any object name.
>> Dan
>|||Tibor,
You answered my question!!! It was my mistake. I used the database name
instead of the table name. By using the table name with DBCC SHOWCONTIG, I
now have statistics.
Thanks very much for asking the questions. I sincerely apologize for my
errors.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evqdRQz0FHA.2964@.TK2MSFTNGP10.phx.gbl...
>> The database name under Enterprise Manager is SMART_DATA.
> Database name? Earlier you said that the name of the table is SMART_DATA.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:uDcBs9y0FHA.2888@.TK2MSFTNGP10.phx.gbl...
>> Thanks for your suggestions, Dan.
>> I have tried Smart_Data, SMART_DATA, smart_data. The database name under
>> Enterprise Manager is SMART_DATA. Do you have any other suggestions?
>> Thanks.
>> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
>> news:OYWLCdx0FHA.3956@.TK2MSFTNGP09.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write
>> me the detail instructions. I have a question and I don't know if it
>> is related to renaming the server or not. I got this error message
>> when I typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query
>> Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
>> In your other replies to this you have Smart_Data as the table name.
>> Have you tried
>> DBCC SHOWCONTIG (Smart_Data)
>> It's possible that you have your codepage set to a case-sensitive one,
>> in which case you must use the correct case for any object name.
>> Dan
>>|||> Thanks very much for asking the questions. I sincerely apologize for my
> errors.
No need to apologize. I'm glad you sorted it out. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Diane Walker" <ett9300@.yahoo.com> wrote in message news:ueCbj2z0FHA.3524@.tk2msftngp13.phx.gbl...
> Tibor,
> You answered my question!!! It was my mistake. I used the database name
> instead of the table name. By using the table name with DBCC SHOWCONTIG, I
> now have statistics.
> Thanks very much for asking the questions. I sincerely apologize for my
> errors.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:evqdRQz0FHA.2964@.TK2MSFTNGP10.phx.gbl...
>> The database name under Enterprise Manager is SMART_DATA.
>> Database name? Earlier you said that the name of the table is SMART_DATA.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:uDcBs9y0FHA.2888@.TK2MSFTNGP10.phx.gbl...
>> Thanks for your suggestions, Dan.
>> I have tried Smart_Data, SMART_DATA, smart_data. The database name under
>> Enterprise Manager is SMART_DATA. Do you have any other suggestions?
>> Thanks.
>> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
>> news:OYWLCdx0FHA.3956@.TK2MSFTNGP09.phx.gbl...
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:uMZo1kg0FHA.3568@.TK2MSFTNGP15.phx.gbl...
>> Hi Rick,
>> Your instructions work. I appreciated that you took the time to write
>> me the detail instructions. I have a question and I don't know if it
>> is related to renaming the server or not. I got this error message
>> when I typed the command "DBCC SHOWCONTIG (SMART_DATA) under Query
>> Analyzer
>> Server: Msg 2501, Level 16, State 45, Line 1
>> Could not find a table or object named 'SMART_DATA'. Check sysobjects.
>> DBCC execution completed. If DBCC printed error messages, contact your
>> system administrator.
>> Do you have any suggestions? Thanks.
>> In your other replies to this you have Smart_Data as the table name.
>> Have you tried
>> DBCC SHOWCONTIG (Smart_Data)
>> It's possible that you have your codepage set to a case-sensitive one,
>> in which case you must use the correct case for any object name.
>> Dan
>>
>

rename login (Possible OT)

I need to rename a login that owns some tables. I cannot do this via the
enterprise manager? Can I do it via SQL scripting?
Please advise (also if there is a better NG to post this to)
ThanksDon't think so, butyou could create the new login and then change the owner
of all the object that the old login owns using sp_changeobjectowner and/or
sp_changedbowner (if they own a database). Doing this would accomplish the
rename.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Hi,
There is no commands in sql server to rename a Login. The better
recommendation is to set a strong password
which can not be guessed by any one.
Otherwise create a new login/user and assign the object owner to that new
user using sp_changeobjectowner procedure
and drop the existing user.
Thanks
Hari
MCDBA
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Matt,
Is this a SQL Server login or a domain login? (I assume SQL Server.) There
is not a way to do this directly (although you can monkey with system tables
this is to be avoided).
Suggestion:
1. Create a login with the new name.
2. Give it the same rights the old login had.
3. Use sp_changeobjectowner for all objects affected to point them to the
new owner.
4. Drop the old login.
Russell Fields
"Matt Pollicove" <matt@.maxware.com> wrote in message
news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> I need to rename a login that owns some tables. I cannot do this via the
> enterprise manager? Can I do it via SQL scripting?
> Please advise (also if there is a better NG to post this to)
> Thanks
>|||Thanks.
I have to reset the user not because of password issues but because the
database was moved without detaching it first and the username was held in
the db but not the login name.
thanks for all your help, I will try this method.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:OmTBpusLEHA.3216@.tk2msftngp13.phx.gbl...
> Matt,
> Is this a SQL Server login or a domain login? (I assume SQL Server.)
There
> is not a way to do this directly (although you can monkey with system
tables
> this is to be avoided).
> Suggestion:
> 1. Create a login with the new name.
> 2. Give it the same rights the old login had.
> 3. Use sp_changeobjectowner for all objects affected to point them to the
> new owner.
> 4. Drop the old login.
> Russell Fields
> "Matt Pollicove" <matt@.maxware.com> wrote in message
> news:e0tH9msLEHA.3596@.tk2msftngp13.phx.gbl...
> > I need to rename a login that owns some tables. I cannot do this via
the
> > enterprise manager? Can I do it via SQL scripting?
> >
> > Please advise (also if there is a better NG to post this to)
> >
> > Thanks
> >
> >
>

Rename database

Hi,
Is there any easy way to rename a database?
It can be done using SQL script or just
Enterprise Manager.
I'm using SQLServer 2000
Regards
Miroomust use T-SQL script, two means:
1.sp_renamedb dbold, dbnew
2.ALTER DATABASE dbold MODIFY NAME=dbnew
"Miroo_news" <miroo@.USUNTO.poczta.fm> wrote in message
news:bm09qr$78l$1@.atlantis.news.tpi.pl...
> Hi,
> Is there any easy way to rename a database?
> It can be done using SQL script or just
> Enterprise Manager.
> I'm using SQLServer 2000
> Regards
> Miroo|||U¿ytkownik "Henry" <qiuliang@.163.net> napisa³ w wiadomo¶ci
news:OyD5KTWjDHA.684@.TK2MSFTNGP09.phx.gbl...
> must use T-SQL script, two means:
> 2.ALTER DATABASE dbold MODIFY NAME=dbnew
Thank you, it works OK.
Regards,
Miroo

removing replication table

Hi all,

Can Enterprise Manager remove a table from replicating to another server? I've tried looking at the article tab of the property of the publication but couldn't find a way to delete a table from the article.

Any idea?

Thanks!
Hi Alan,

In SQL Server 2000, if there is an active subscription or the publication allows anonymous subscriptions, you will not be able to drop an article either through Enterprise manager or through scripts.

This has been relaxed in SQL Server 2005 and you can freely add/drop articles and in some cases you need to reinitialize the subscriptions.|||Many thanks for the info!
|||

Hello Mahesh,

Background:

I installed MS CRM 1.2 last year for the company to test out. When MS released CRM 3.0 to us (we are part of the MS PP via the Action Pack) I tried installing that. I had a database issue (error on my part in SQL Server with null values) that I corrected. However when I went back into to reinstall CRM 3.0 the app complained about database replication. I have not used replication at anytime prior to any of this. So when I went into the database to look sure enough a database MyCompanyName_MSCRMDistrubution was there and had database replication on it. However when I looked into the tables there was nothing in there (no publishers, no subscribers, null, ect). So I first tried to us the MS KB article on removing database replication however the first couple of stored_procs need pubs and subs params (args) which I could not include. So I then found a stored_proc named sp_removedreplication which I ran successfully. However through EM the offending database still is showing replication and hence I can not detach and remove it.

Someone mentioned that I should remove the MyCompanyName_METABASE (which I can at this point) to see if this helped. Another mentioned I should open up a Support Case with MSB.

Before opening up a case do you have any recommendations? (or anyone else for that matter)

Thank you in advance.

|||

Not sure if this is a recommended approach but you can try and update the category column in the sysdatabases table (for your db) to 0 and that should take care of the problem. Also have the Allow modifications to be made directly to the system catalogs option checked before you do the update.

update sysdatabases

set category = 0

where name = <your database name>

|||

A better way is to try:

If using Merge replication:

exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'merge publish', 'false'

If Tran replication:

exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'publish', 'false'

See if that serves the purpose.

|||

Mahesh,

I tried this and it ran successfully, but to no avail. The replication was still on at this point. However see my replied to Sachin

|||

Sachin,

Thank you. What I did was the following:

1. I ran Mahesh stored_proc for merged replication, it ran fine but the replication was still there.

2. I attempted to run your script above but I did not have ad hoc updates on.

3. Being curious I rans some simple selects and verifyed that you were correct that for that particular db and the category was a value of 16. So after turning on the ad hoc updates I created an update table and changed the cat to 0.

4. Then I created a drop database query and ran that.

5. That dropped the table from the system just fine!

Thank you.

|||

Don,

I am glad I could help. :)

|||

Sachin,

Me too. Step 5 should have been "dropped the database" instead of table.

Best Regards~

Don

removing replication table

Hi all,

Can Enterprise Manager remove a table from replicating to another server? I've tried looking at the article tab of the property of the publication but couldn't find a way to delete a table from the article.

Any idea?

Thanks!
Hi Alan,

In SQL Server 2000, if there is an active subscription or the publication allows anonymous subscriptions, you will not be able to drop an article either through Enterprise manager or through scripts.

This has been relaxed in SQL Server 2005 and you can freely add/drop articles and in some cases you need to reinitialize the subscriptions.|||Many thanks for the info!
|||

Hello Mahesh,

Background:

I installed MS CRM 1.2 last year for the company to test out. When MS released CRM 3.0 to us (we are part of the MS PP via the Action Pack) I tried installing that. I had a database issue (error on my part in SQL Server with null values) that I corrected. However when I went back into to reinstall CRM 3.0 the app complained about database replication. I have not used replication at anytime prior to any of this. So when I went into the database to look sure enough a database MyCompanyName_MSCRMDistrubution was there and had database replication on it. However when I looked into the tables there was nothing in there (no publishers, no subscribers, null, ect). So I first tried to us the MS KB article on removing database replication however the first couple of stored_procs need pubs and subs params (args) which I could not include. So I then found a stored_proc named sp_removedreplication which I ran successfully. However through EM the offending database still is showing replication and hence I can not detach and remove it.

Someone mentioned that I should remove the MyCompanyName_METABASE (which I can at this point) to see if this helped. Another mentioned I should open up a Support Case with MSB.

Before opening up a case do you have any recommendations? (or anyone else for that matter)

Thank you in advance.

|||

Not sure if this is a recommended approach but you can try and update the category column in the sysdatabases table (for your db) to 0 and that should take care of the problem. Also have the Allow modifications to be made directly to the system catalogs option checked before you do the update.

update sysdatabases

set category = 0

where name = <your database name>

|||

A better way is to try:

If using Merge replication:

exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'merge publish', 'false'

If Tran replication:

exec sp_replicationdboption 'MyCompanyName_MSCRMDistrubution ', 'publish', 'false'

See if that serves the purpose.

|||

Mahesh,

I tried this and it ran successfully, but to no avail. The replication was still on at this point. However see my replied to Sachin

|||

Sachin,

Thank you. What I did was the following:

1. I ran Mahesh stored_proc for merged replication, it ran fine but the replication was still there.

2. I attempted to run your script above but I did not have ad hoc updates on.

3. Being curious I rans some simple selects and verifyed that you were correct that for that particular db and the category was a value of 16. So after turning on the ad hoc updates I created an update table and changed the cat to 0.

4. Then I created a drop database query and ran that.

5. That dropped the table from the system just fine!

Thank you.

|||

Don,

I am glad I could help. :)

|||

Sachin,

Me too. Step 5 should have been "dropped the database" instead of table.

Best Regards~

Don

Removing Identity property SQL2K5

What is the fastest way to remove identity property of a column.?
I traced Enterprise Manager way of doing, it creates a replicate of the
table and copies all rows and constratints to it and deletes old table and
renames the new table with the original column.This is taking long time
Please suggest.
thxThat's probably pretty much it. i doubt SQL Server can do it any other way.
Anything else would involve the same thing, but in different ways e.g.
DTSing the data into a duplicate table, dropping the old table and renaming
the other one.
"skg" <skg@.yahoo.com> wrote in message
news:%23RcwXTSRGHA.424@.TK2MSFTNGP12.phx.gbl...
> What is the fastest way to remove identity property of a column.?
> I traced Enterprise Manager way of doing, it creates a replicate of the
> table and copies all rows and constratints to it and deletes old table and
> renames the new table with the original column.This is taking long time
> Please suggest.
> thx
>|||I guess you could add another column, copy the data over using an UPDATE sta
tement, drop the old
column and rename the column. But the columns would no be in the same order
as they were originally.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Ian Boyd" <admin@.SWIFTPA.NET> wrote in message news:%230zTElSRGHA.1688@.TK2MSFTNGP11.phx.gb
l...
> That's probably pretty much it. i doubt SQL Server can do it any other way
.
> Anything else would involve the same thing, but in different ways e.g.
> DTSing the data into a duplicate table, dropping the old table and renamin
g the other one.
> "skg" <skg@.yahoo.com> wrote in message news:%23RcwXTSRGHA.424@.TK2MSFTNGP12
.phx.gbl...
>|||Thanks!!!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uTn$x4fRGHA.252@.TK2MSFTNGP10.phx.gbl...
>I guess you could add another column, copy the data over using an UPDATE
>statement, drop the old column and rename the column. But the columns would
>no be in the same order as they were originally.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Ian Boyd" <admin@.SWIFTPA.NET> wrote in message
> news:%230zTElSRGHA.1688@.TK2MSFTNGP11.phx.gbl...
>

Removing identity column

We can easily remove identity columnn through enterprise manager but how can it be done through transact sql?

The only way i found is to create a new column and pass values if identity column in it and then remove this identity column is there any better method of doing it?

Open up the Northwind DB an select identity NO for the OrderID

The script that Enterprise Manager creates is this (Click on the 3rd Icon from the left, left side of the scissors)

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Shippers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Employees
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT FK_Orders_Customers
GO
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Orders
DROP CONSTRAINT DF_Orders_Freight
GO
CREATE TABLE dbo.Tmp_Orders
(
OrderID int NOT NULL,
CustomerID nchar(5) NULL,
EmployeeID int NULL,
OrderDate datetime NULL,
RequiredDate datetime NULL,
ShippedDate datetime NULL,
ShipVia int NULL,
Freight money NULL,
ShipName nvarchar(40) NULL,
ShipAddress nvarchar(60) NULL,
ShipCity nvarchar(15) NULL,
ShipRegion nvarchar(15) NULL,
ShipPostalCode nvarchar(10) NULL,
ShipCountry nvarchar(15) NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Orders ADD CONSTRAINT
DF_Orders_Freight DEFAULT (0) FOR Freight
GO
IF EXISTS(SELECT * FROM dbo.Orders)
EXEC('INSERT INTO dbo.Tmp_Orders (OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry FROM dbo.Orders TABLOCKX')
GO
ALTER TABLE dbo.[Order Details]
DROP CONSTRAINT FK_Order_Details_Orders
GO
DROP TABLE dbo.Orders
GO
EXECUTE sp_rename N'dbo.Tmp_Orders', N'Orders', 'OBJECT'
GO
ALTER TABLE dbo.Orders ADD CONSTRAINT
PK_Orders PRIMARY KEY CLUSTERED
(
OrderID
) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX CustomerID ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX CustomersOrders ON dbo.Orders
(
CustomerID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeeID ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX EmployeesOrders ON dbo.Orders
(
EmployeeID
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX OrderDate ON dbo.Orders
(
OrderDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippedDate ON dbo.Orders
(
ShippedDate
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShippersOrders ON dbo.Orders
(
ShipVia
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX ShipPostalCode ON dbo.Orders
(
ShipPostalCode
) ON [PRIMARY]
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Customers FOREIGN KEY
(
CustomerID
) REFERENCES dbo.Customers
(
CustomerID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Employees FOREIGN KEY
(
EmployeeID
) REFERENCES dbo.Employees
(
EmployeeID
)
GO
ALTER TABLE dbo.Orders WITH NOCHECK ADD CONSTRAINT
FK_Orders_Shippers FOREIGN KEY
(
ShipVia
) REFERENCES dbo.Shippers
(
ShipperID
)
GO
GRANT REFERENCES ON dbo.Orders TO public AS dbo
GRANT SELECT ON dbo.Orders TO public AS dbo
GRANT UPDATE ON dbo.Orders TO public AS dbo
GRANT INSERT ON dbo.Orders TO public AS dbo
GRANT DELETE ON dbo.Orders TO public AS dbo
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.[Order Details] WITH NOCHECK ADD CONSTRAINT
FK_Order_Details_Orders FOREIGN KEY
(
OrderID
) REFERENCES dbo.Orders
(
OrderID
)
GO
COMMIT

so as you can see there is a lot that has to be done behind the scenes

Denis the SQL Menace

http://sqlservercode.blogspot.com/

Removing a SQL server backup

In SQL Enterprise Manager, an employee right-clicked the 'Database' object
and created a scheduled backup. Then they expanded the 'Database' object,
found the target database 'XYZdatabase', right-clicked that too and
scheduled a second backup to a different drive.
How do I delete the backup job(s) because I have two running right now?
If I remove the file location where it backs up to, it tells me I must
select a file name / location. There is no way to remove the databse in the
SQL Server backup window, only an option from the dropdown to select
different databases.
Thanks in advance.
r042wal schrieb:
> In SQL Enterprise Manager, an employee right-clicked the 'Database'
> object and created a scheduled backup. Then they expanded the
> 'Database' object, found the target database 'XYZdatabase',
> right-clicked that too and scheduled a second backup to a different drive.
> How do I delete the backup job(s) because I have two running right now?
look at the SQL Agent Jobs. There you find the scheduled jobs and can
delete one.

> If I remove the file location where it backs up to, it tells me I must
> select a file name / location. There is no way to remove the databse in
> the SQL Server backup window, only an option from the dropdown to select
> different databases.
> Thanks in advance.
hth
Gregor Stefka

Removing a SQL server backup

In SQL Enterprise Manager, an employee right-clicked the 'Database' object
and created a scheduled backup. Then they expanded the 'Database' object,
found the target database 'XYZdatabase', right-clicked that too and
scheduled a second backup to a different drive.
How do I delete the backup job(s) because I have two running right now?
If I remove the file location where it backs up to, it tells me I must
select a file name / location. There is no way to remove the databse in the
SQL Server backup window, only an option from the dropdown to select
different databases.
Thanks in advance.r042wal schrieb:
> In SQL Enterprise Manager, an employee right-clicked the 'Database'
> object and created a scheduled backup. Then they expanded the
> 'Database' object, found the target database 'XYZdatabase',
> right-clicked that too and scheduled a second backup to a different drive.
> How do I delete the backup job(s) because I have two running right now?
look at the SQL Agent Jobs. There you find the scheduled jobs and can
delete one.
> If I remove the file location where it backs up to, it tells me I must
> select a file name / location. There is no way to remove the databse in
> the SQL Server backup window, only an option from the dropdown to select
> different databases.
> Thanks in advance.
hth
Gregor Stefka

Removing a SQL server backup

In SQL Enterprise Manager, an employee right-clicked the 'Database' object
and created a scheduled backup. Then they expanded the 'Database' object,
found the target database 'XYZdatabase', right-clicked that too and
scheduled a second backup to a different drive.
How do I delete the backup job(s) because I have two running right now?
If I remove the file location where it backs up to, it tells me I must
select a file name / location. There is no way to remove the databse in the
SQL Server backup window, only an option from the dropdown to select
different databases.
Thanks in advance.r042wal schrieb:
> In SQL Enterprise Manager, an employee right-clicked the 'Database'
> object and created a scheduled backup. Then they expanded the
> 'Database' object, found the target database 'XYZdatabase',
> right-clicked that too and scheduled a second backup to a different drive.
> How do I delete the backup job(s) because I have two running right now?
look at the SQL Agent Jobs. There you find the scheduled jobs and can
delete one.

> If I remove the file location where it backs up to, it tells me I must
> select a file name / location. There is no way to remove the databse in
> the SQL Server backup window, only an option from the dropdown to select
> different databases.
> Thanks in advance.
hth
Gregor Stefka