Renaming a named SQL Server 2000 instance

Guys
I am trying to rename a named SQL Server instance , please share any inputs
Thanks
SwamiHi Swami,
Try something like this:
sp_dropserver <old_servername\instancename>
go
sp_addserver <new_servername\instancename> , local
go
Good luck
Regards,
Dilip|||No can do. Install a new instance with desired name and transfer the data (backup/restore,
detach/attach, DTS etc...).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Swami" <Swami@.discussions.microsoft.com> wrote in message
news:BD8C5C3D-15D0-447F-AB0D-78AB8B7D8F1B@.microsoft.com...
> Guys
> I am trying to rename a named SQL Server instance , please share any inputs
> Thanks
> Swami|||Hi,
Only default instance of sql server can be renamed. As Tibor mentioned only
way to change the named instance is by doing new Named instance with
required name and then transfer all databases from old instance to new
installed instance.
Thanks
Hari
SQL Server MVP
"Swami" <Swami@.discussions.microsoft.com> wrote in message
news:BD8C5C3D-15D0-447F-AB0D-78AB8B7D8F1B@.microsoft.com...
> Guys
> I am trying to rename a named SQL Server instance , please share any
> inputs
> Thanks
> Swami

Rename a table with a SQL

Hello guys,
is it possible to rename a table with a SQL statement ?
Thanks
Hi Serge
Take a look at sp_rename in the Books Online.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Serge Fournier" <safournier@.free.fr> wrote in message
news:ugihcRlzEHA.3840@.tk2msftngp13.phx.gbl...
> Hello guys,
> is it possible to rename a table with a SQL statement ?
> Thanks
>
|||Thanks
"Kalen Delaney" <replies@.public_newsgroups.com> a crit dans le message de
news: %23pKhWilzEHA.3204@.TK2MSFTNGP10.phx.gbl...
> Hi Serge
> Take a look at sp_rename in the Books Online.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Serge Fournier" <safournier@.free.fr> wrote in message
> news:ugihcRlzEHA.3840@.tk2msftngp13.phx.gbl...
>
sql

Removing Replication MSSQL 2000

Hi Guys, I have recently removed replication from the SQL server (2k)
and everything seemed to be working at first. Initially, the database
still contained all the conflict tables etc... but I got rid of all
those. However, now, when attempting to insert into some tables in the
database, they are failing without errors. If I re-enable replication
for the database the inserts and updates work fine. Anyone ever heard
of this? Any help or suggestions would be appreciated. Thanks!
Regards,
Troy
Troy,
please try running sp_removedbreplication and also take a look at the
tables - do they have any remaining triggers?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Removing IDENTITY from a tabe column

Hi Guys,
I have created a table containg a column with IDENTITY keyword. I have data
in the table. Now i need to alter this table without IDENTITY for that
column. Please help me get a sql script to do this one.
The creation scirpt for the table is :
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EventLog];
CREATE TABLE [dbo].[EventLog] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[eventId] [int] NOT NULL ,
[eventType] [int] NOT NULL ,
[eventDateTime] [datetime] NULL ,
[AimNumber] [int] NULL ,
[eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY];
ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY] ;
Now i need script for altering the just identity column.'?
HariHi,
You need to drop and create the table to remove IDENTITY property.
If you use Enterprise manager it internally copy the data outside,script the
schema, modify the script to remove identity
and create the table back and load data.
Thanks
Hari
SQL Server MVP
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have
> data
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari|||Here's the script:
ALTER TABLE dbo.EventLog ADD NewRecordID int NULL
GO
UPDATE dbo.EventLog SET NewRecordID = RecordId
GO
ALTER TABLE dbo.EventLog DROP CONSTRAINT PK_EventLog
GO
ALTER TABLE dbo.EventLog DROP COLUMN RecordId
GO
EXEC sp_rename 'EventLog.NewRecordID', 'RecordId'
GO
ALTER TABLE dbo.EventLog ALTER COLUMN RecordId int NOT NULL
GO
ALTER TABLE dbo.EventLog ADD CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED
(RecordId) ON [PRIMARY]
GO
Make sure there's nobody making changes to this data while you are running
this script.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
Hi Guys,
I have created a table containg a column with IDENTITY keyword. I have data
in the table. Now i need to alter this table without IDENTITY for that
column. Please help me get a sql script to do this one.
The creation scirpt for the table is :
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EventLog];
CREATE TABLE [dbo].[EventLog] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[eventId] [int] NOT NULL ,
[eventType] [int] NOT NULL ,
[eventDateTime] [datetime] NULL ,
[AimNumber] [int] NULL ,
[eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY];
ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY] ;
Now i need script for altering the just identity column.'?
Hari|||Thank you verymuch for your suggetion.
Hari
"Narayana Vyas Kondreddi" wrote:
> Here's the script:
> ALTER TABLE dbo.EventLog ADD NewRecordID int NULL
> GO
> UPDATE dbo.EventLog SET NewRecordID = RecordId
> GO
> ALTER TABLE dbo.EventLog DROP CONSTRAINT PK_EventLog
> GO
> ALTER TABLE dbo.EventLog DROP COLUMN RecordId
> GO
> EXEC sp_rename 'EventLog.NewRecordID', 'RecordId'
> GO
> ALTER TABLE dbo.EventLog ALTER COLUMN RecordId int NOT NULL
> GO
> ALTER TABLE dbo.EventLog ADD CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED
> (RecordId) ON [PRIMARY]
> GO
> Make sure there's nobody making changes to this data while you are running
> this script.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have data
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari
>
>

Removing IDENTITY from a tabe column

Hi Guys,
I have created a table containg a column with IDENTITY keyword. I have data
in the table. Now i need to alter this table without IDENTITY for that
column. Please help me get a sql script to do this one.
The creation scirpt for the table is :
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EventLog];
CREATE TABLE [dbo].[EventLog] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[eventId] [int] NOT NULL ,
[eventType] [int] NOT NULL ,
[eventDateTime] [datetime] NULL ,
[AimNumber] [int] NULL ,
[eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY];
ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY] ;
Now i need script for altering the just identity column.'?
HariI think
alter table [dbo].[EventLog]
alter column [RecordId] [int]
--
thsi will work
Hari wrote:
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have dat
a
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari|||I think
alter table [dbo].[EventLog]
alter column [RecordId] [int]
--
this will work
Hari wrote:
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have dat
a
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari|||I think
alter table [dbo].[EventLog]
alter column [RecordId] [int]
--
this will work
Hari wrote:
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have dat
a
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari|||Hi,
You can not remove the IDENTITY property using Alter table command.
Only way is export data into a new table using
1. SELECT * INTO newtable from existing table
2. create new table with out identity
3. insert the data from new table
4. drop the existing table and rename the new table using sp_rename
Enterprise manager internally almost does the above steps to remove identity
property.
Thanks
Hari
SQL Server MVP
"sajeevp" <sajeev.padmanabhan@.gmail.com> wrote in message
news:1122522742.838595.315250@.f14g2000cwb.googlegroups.com...
>I think
> --
> alter table [dbo].[EventLog]
> alter column [RecordId] [int]
> --
> this will work
>
>
> Hari wrote:
>|||>I think
> --
> alter table [dbo].[EventLog]
> alter column [RecordId] [int]
> --
> thsi will work
No, it will not. Did you try it?|||that was a wrong post .. sorry|||Thanks for your suggetion.
Hari
"Hari Pra" wrote:

> Hi,
> You can not remove the IDENTITY property using Alter table command.
> Only way is export data into a new table using
> 1. SELECT * INTO newtable from existing table
> 2. create new table with out identity
> 3. insert the data from new table
> 4. drop the existing table and rename the new table using sp_rename
> Enterprise manager internally almost does the above steps to remove identi
ty
> property.
> Thanks
> Hari
> SQL Server MVP
>
>
> "sajeevp" <sajeev.padmanabhan@.gmail.com> wrote in message
> news:1122522742.838595.315250@.f14g2000cwb.googlegroups.com...
>
>

Removing IDENTITY from a tabe column

Hi Guys,
I have created a table containg a column with IDENTITY keyword. I have data
in the table. Now i need to alter this table without IDENTITY for that
column. Please help me get a sql script to do this one.
The creation scirpt for the table is :
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EventLog];
CREATE TABLE [dbo].[EventLog] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[eventId] [int] NOT NULL ,
[eventType] [int] NOT NULL ,
[eventDateTime] [datetime] NULL ,
[AimNumber] [int] NULL ,
[eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY];
ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY] ;
Now i need script for altering the just identity column.??
Hari
Hi,
You need to drop and create the table to remove IDENTITY property.
If you use Enterprise manager it internally copy the data outside,script the
schema, modify the script to remove identity
and create the table back and load data.
Thanks
Hari
SQL Server MVP
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have
> data
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.??
> Hari
|||Here's the script:
ALTER TABLE dbo.EventLog ADD NewRecordID int NULL
GO
UPDATE dbo.EventLog SET NewRecordID = RecordId
GO
ALTER TABLE dbo.EventLog DROP CONSTRAINT PK_EventLog
GO
ALTER TABLE dbo.EventLog DROP COLUMN RecordId
GO
EXEC sp_rename 'EventLog.NewRecordID', 'RecordId'
GO
ALTER TABLE dbo.EventLog ALTER COLUMN RecordId int NOT NULL
GO
ALTER TABLE dbo.EventLog ADD CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED
(RecordId) ON [PRIMARY]
GO
Make sure there's nobody making changes to this data while you are running
this script.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
Hi Guys,
I have created a table containg a column with IDENTITY keyword. I have data
in the table. Now i need to alter this table without IDENTITY for that
column. Please help me get a sql script to do this one.
The creation scirpt for the table is :
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[EventLog];
CREATE TABLE [dbo].[EventLog] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[eventId] [int] NOT NULL ,
[eventType] [int] NOT NULL ,
[eventDateTime] [datetime] NULL ,
[AimNumber] [int] NULL ,
[eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY];
ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY] ;
Now i need script for altering the just identity column.??
Hari
|||Thank you verymuch for your suggetion.
Hari
"Narayana Vyas Kondreddi" wrote:

> Here's the script:
> ALTER TABLE dbo.EventLog ADD NewRecordID int NULL
> GO
> UPDATE dbo.EventLog SET NewRecordID = RecordId
> GO
> ALTER TABLE dbo.EventLog DROP CONSTRAINT PK_EventLog
> GO
> ALTER TABLE dbo.EventLog DROP COLUMN RecordId
> GO
> EXEC sp_rename 'EventLog.NewRecordID', 'RecordId'
> GO
> ALTER TABLE dbo.EventLog ALTER COLUMN RecordId int NOT NULL
> GO
> ALTER TABLE dbo.EventLog ADD CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED
> (RecordId) ON [PRIMARY]
> GO
> Make sure there's nobody making changes to this data while you are running
> this script.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have data
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.??
> Hari
>
>

Removing IDENTITY from a tabe column

Hi Guys,
I have created a table containg a column with IDENTITY keyword. I have data
in the table. Now i need to alter this table without IDENTITY for that
column. Please help me get a sql script to do this one.
The creation scirpt for the table is :
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable'
) = 1)
drop table [dbo].[EventLog];
CREATE TABLE [dbo].[EventLog] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[eventId] [int] NOT NULL ,
[eventType] [int] NOT NULL ,
[eventDateTime] [datetime] NULL ,
[AimNumber] [int] NULL ,
[eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_C
I_AS
NULL
) ON [PRIMARY];
ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY] ;
Now i need script for altering the just identity column.'?
HariHi,
You need to drop and create the table to remove IDENTITY property.
If you use Enterprise manager it internally copy the data outside,script the
schema, modify the script to remove identity
and create the table back and load data.
Thanks
Hari
SQL Server MVP
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have
> data
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTabl
e') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1
_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari|||Here's the script:
ALTER TABLE dbo.EventLog ADD NewRecordID int NULL
GO
UPDATE dbo.EventLog SET NewRecordID = RecordId
GO
ALTER TABLE dbo.EventLog DROP CONSTRAINT PK_EventLog
GO
ALTER TABLE dbo.EventLog DROP COLUMN RecordId
GO
EXEC sp_rename 'EventLog.NewRecordID', 'RecordId'
GO
ALTER TABLE dbo.EventLog ALTER COLUMN RecordId int NOT NULL
GO
ALTER TABLE dbo.EventLog ADD CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED
(RecordId) ON [PRIMARY]
GO
Make sure there's nobody making changes to this data while you are running
this script.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
Hi Guys,
I have created a table containg a column with IDENTITY keyword. I have data
in the table. Now i need to alter this table without IDENTITY for that
column. Please help me get a sql script to do this one.
The creation scirpt for the table is :
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTable'
) = 1)
drop table [dbo].[EventLog];
CREATE TABLE [dbo].[EventLog] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[eventId] [int] NOT NULL ,
[eventType] [int] NOT NULL ,
[eventDateTime] [datetime] NULL ,
[AimNumber] [int] NULL ,
[eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1_C
I_AS
NULL
) ON [PRIMARY];
ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY] ;
Now i need script for altering the just identity column.'?
Hari|||Thank you verymuch for your suggetion.
Hari
"Narayana Vyas Kondreddi" wrote:

> Here's the script:
> ALTER TABLE dbo.EventLog ADD NewRecordID int NULL
> GO
> UPDATE dbo.EventLog SET NewRecordID = RecordId
> GO
> ALTER TABLE dbo.EventLog DROP CONSTRAINT PK_EventLog
> GO
> ALTER TABLE dbo.EventLog DROP COLUMN RecordId
> GO
> EXEC sp_rename 'EventLog.NewRecordID', 'RecordId'
> GO
> ALTER TABLE dbo.EventLog ALTER COLUMN RecordId int NOT NULL
> GO
> ALTER TABLE dbo.EventLog ADD CONSTRAINT PK_EventLog PRIMARY KEY CLUSTERED
> (RecordId) ON [PRIMARY]
> GO
> Make sure there's nobody making changes to this data while you are running
> this script.
> --
> HTH,
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:F8F07062-110A-4E07-8526-5D93E3B6C063@.microsoft.com...
> Hi Guys,
> I have created a table containg a column with IDENTITY keyword. I have dat
a
> in the table. Now i need to alter this table without IDENTITY for that
> column. Please help me get a sql script to do this one.
> The creation scirpt for the table is :
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EventLog]') and OBJECTPROPERTY(id, N'IsUserTabl
e') = 1)
> drop table [dbo].[EventLog];
> CREATE TABLE [dbo].[EventLog] (
> [RecordId] [int] IDENTITY (1, 1) NOT NULL ,
> [eventId] [int] NOT NULL ,
> [eventType] [int] NOT NULL ,
> [eventDateTime] [datetime] NULL ,
> [AimNumber] [int] NULL ,
> [eventMsg] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [opId] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [comments] [nvarchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NU
LL ,
> [eventProperties] [nvarchar] (1024) COLLATE SQL_Latin1_General_CP1
_CI_AS
> NULL
> ) ON [PRIMARY];
> ALTER TABLE [dbo].[EventLog] WITH NOCHECK ADD
> CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED
> (
> [RecordId]
> ) ON [PRIMARY] ;
>
> Now i need script for altering the just identity column.'?
> Hari
>
>

Removing a table from a database

Guys,
I have a few tables that I want to remove. Any thing I have to be concern
about?
tnt
You can check the dependencies to make sure there are no objects that depend
on the tables. Not perfect but in most cases will do a good job. Then
performing a backup before dropping the tables, just in case you need them
back.
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||How do I do that- checking the dependecies. I was looking at the database
relationship manually.
tnt
"Plamen Ratchev" wrote:

> You can check the dependencies to make sure there are no objects that depend
> on the tables. Not perfect but in most cases will do a good job. Then
> performing a backup before dropping the tables, just in case you need them
> back.
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>
|||SQL Server 2005 - In SQL Server Management Studio right click on the table
name and select View Dependencies
SQL Server 2000 - In Enterprise Manager right click on the table name and
select All Tasks -> Display Dependencies
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Removing a table from a database

Guys,
I have a few tables that I want to remove. Any thing I have to be concern
about?
tntYou can check the dependencies to make sure there are no objects that depend
on the tables. Not perfect but in most cases will do a good job. Then
performing a backup before dropping the tables, just in case you need them
back.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||How do I do that- checking the dependecies. I was looking at the database
relationship manually.
tnt
"Plamen Ratchev" wrote:

> You can check the dependencies to make sure there are no objects that depe
nd
> on the tables. Not perfect but in most cases will do a good job. Then
> performing a backup before dropping the tables, just in case you need them
> back.
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>|||SQL Server 2005 - In SQL Server Management Studio right click on the table
name and select View Dependencies
SQL Server 2000 - In Enterprise Manager right click on the table name and
select All Tasks -> Display Dependencies
HTH,
Plamen Ratchev
http://www.SQLStudio.com

Removing a table from a database

Guys,
I have a few tables that I want to remove. Any thing I have to be concern
about?
tntYou can check the dependencies to make sure there are no objects that depend
on the tables. Not perfect but in most cases will do a good job. Then
performing a backup before dropping the tables, just in case you need them
back.
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||How do I do that- checking the dependecies. I was looking at the database
relationship manually.
tnt
"Plamen Ratchev" wrote:
> You can check the dependencies to make sure there are no objects that depend
> on the tables. Not perfect but in most cases will do a good job. Then
> performing a backup before dropping the tables, just in case you need them
> back.
> HTH,
> Plamen Ratchev
> http://www.SQLStudio.com
>|||SQL Server 2005 - In SQL Server Management Studio right click on the table
name and select View Dependencies
SQL Server 2000 - In Enterprise Manager right click on the table name and
select All Tasks -> Display Dependencies
HTH,
Plamen Ratchev
http://www.SQLStudio.com