Alter Table Alter Column

I would like to add an Identity to an existing column in a table using a
stored procedure then add records to the table and then remove the identity
after the records have been added or something similar.
here is a rough idea of what the stored procedure should do. (I do not know
the syntax to accomplish this can anyone help or explain this?

Thanks much,

CBL

CREATE proc dbo.pts_ImportJobs
as

/* add identity to [BarCode Part#] */
alter table dbo.ItemTest
alter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL

/* add records from text file here */

/* remove identity from BarCode Part#] */
alter table dbo.ItemTest
alter column [BarCode Part#] [int] NOT NULL
return

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

here is the original table

CREATE TABLE [ItemTest] (
[BarCode Part#] [int] NOT NULL ,
[File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),
[Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),
[Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Description] DEFAULT (''),
[Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),
[Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),
[Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]
DEFAULT (0),
[Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),
[Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),
[Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]
DEFAULT (0),
[BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#] DEFAULT
(0),
[Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),
[Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),
[Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date] DEFAULT
(getdate()),
CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
(
[BarCode Part#]
) ON [PRIMARY]
) ON [PRIMARY]
GO"me" <me@.work.com> wrote in message
news:10crrjkjjttmbbe@.corp.supernews.com...
> I would like to add an Identity to an existing column in a table using a
> stored procedure then add records to the table and then remove the
identity
> after the records have been added or something similar.
> here is a rough idea of what the stored procedure should do. (I do not
know
> the syntax to accomplish this can anyone help or explain this?
> Thanks much,
> CBL
>
>
> CREATE proc dbo.pts_ImportJobs
> as
> /* add identity to [BarCode Part#] */
> alter table dbo.ItemTest
> alter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL
> /* add records from text file here */
> /* remove identity from BarCode Part#] */
> alter table dbo.ItemTest
> alter column [BarCode Part#] [int] NOT NULL
> return
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> here is the original table
> CREATE TABLE [ItemTest] (
> [BarCode Part#] [int] NOT NULL ,
> [File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),
> [Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),
> [Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Description] DEFAULT (''),
> [Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),
> [Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),
> [Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]
> DEFAULT (0),
> [Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),
> [Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),
> [Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]
> DEFAULT (0),
> [BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#]
DEFAULT
> (0),
> [Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> CONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),
> [Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),
> [Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date]
DEFAULT
> (getdate()),
> CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
> (
> [BarCode Part#]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO

You can't add the IDENTITY property to an existing table - you need to
create a new table with the IDENTITY column. If you have existing data, you
can create it with a different name, INSERT the existing data, drop the
existing table, then rename the new table. Enterprise Manager will do this
for you if you add the property in the table designer.

But there are several ways to INSERT identity values into a table which
already has the IDENTITY property - I suspect that's what you're really
looking for. For loading a text file with BULK INSERT or bcp.exe, there are
options to keep identity values when you import (KEEPIDENTITY and the -E
switch, respectively). For INSERTs from another table, you can use SET
IDENTITY_INSERT ON.

Finally, DBCC CHECKIDENT is used after you've INSERTed, to make sure that
the identity seed is consistent with the table data. See Books Online for
more details on all these commands.

Simon|||Thanks for the help!

CBL

"me" <me@.work.com> wrote in message
news:10crrjkjjttmbbe@.corp.supernews.com...
> I would like to add an Identity to an existing column in a table using a
> stored procedure then add records to the table and then remove the
identity
> after the records have been added or something similar.
> here is a rough idea of what the stored procedure should do. (I do not
know
> the syntax to accomplish this can anyone help or explain this?
> Thanks much,
> CBL
>
>
> CREATE proc dbo.pts_ImportJobs
> as
> /* add identity to [BarCode Part#] */
> alter table dbo.ItemTest
> alter column [BarCode Part#] [int] IDENTITY(1, 1) NOT NULL
> /* add records from text file here */
> /* remove identity from BarCode Part#] */
> alter table dbo.ItemTest
> alter column [BarCode Part#] [int] NOT NULL
> return
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> here is the original table
> CREATE TABLE [ItemTest] (
> [BarCode Part#] [int] NOT NULL ,
> [File Number] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_File Number] DEFAULT (''),
> [Item Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Item Number] DEFAULT (''),
> [Description] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Description] DEFAULT (''),
> [Room Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Room Number] DEFAULT (''),
> [Quantity] [int] NULL CONSTRAINT [DF_ItemTest_Quantity] DEFAULT (0),
> [Label Printed Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Label Printed Cnt]
> DEFAULT (0),
> [Rework] [bit] NULL CONSTRAINT [DF_ItemTest_Rework] DEFAULT (0),
> [Rework Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Rework Cnt] DEFAULT (0),
> [Assembly Scan Cnt] [int] NULL CONSTRAINT [DF_ItemTest_Assembly Scan Cnt]
> DEFAULT (0),
> [BarCode Crate#] [int] NULL CONSTRAINT [DF_ItemTest_BarCode Crate#]
DEFAULT
> (0),
> [Assembly Group#] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
> CONSTRAINT [DF_ItemTest_Assembly Group#] DEFAULT (''),
> [Assembly Name] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> CONSTRAINT [DF_ItemTest_Assembly Name] DEFAULT (''),
> [Import Date] [datetime] NULL CONSTRAINT [DF_ItemTest_Import Date]
DEFAULT
> (getdate()),
> CONSTRAINT [IX_ItemTest] UNIQUE NONCLUSTERED
> (
> [BarCode Part#]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO

Allowing users to truncate log file

I would like to allow a particular user to truncate a log file in a
stored procedure that the user runs every day. At this moment the only
personnel that can truncate the log file are personnel with sysadmin
rights. Is there any way to do this in sql server 2005 without
granting this user sysadmin rights (something we REALLY don't want to
do)? Thanks for all your help in advance.

Dave C.hedgracer (d.christman@.sbcglobal.net) writes:

Quote:

Originally Posted by

I would like to allow a particular user to truncate a log file in a
stored procedure that the user runs every day. At this moment the only
personnel that can truncate the log file are personnel with sysadmin
rights. Is there any way to do this in sql server 2005 without
granting this user sysadmin rights (something we REALLY don't want to
do)? Thanks for all your help in advance.

Yes, this can be done with help of certificates. I have an article on my
web site that describes this in detail:
http://www.sommarskog.se/grantperm.html.

However, this not at all sound right to me, at least if the user would
truncate the log file every day. Truncating the log is something you
only do in exceptional cases when there is an emergency. Normally, you
either:
1) Run with full recovery and schedule regular full backups as well as
transaction log backups.
2) Run with simple recovery and schedule only full backups. The log
will be auto-truncated.

When you run with full recovery, you do so, because you want to be able
to recover the database to any given point in time. But if you truncate
the log, you lose that possibility. Which in fact is self-evident in
SQL 2005, where the only way to do this is to set the database into
simple recovery.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx