Set and Remove Identity attribute

Question

Post reply

Set and Remove Identity attribute

Dinesh Asanka

SSChampion

Points: 11058
More actions
May 31, 2004 at 4:49 am

#107894

how can we set and remove Identity attribute from table using T-SQL

My Blog: http://dineshasanka.spaces.live.com/

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 1

You can use the command:

SET IDENTITY_INSERT tablename ON | OFF

To allow for insertion of identity values.

AFAIK You cannot with T-SQL remove the Identity Property of a table, except by recreating the table.

/rockmoose

You must unlearn what You have learnt

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 2

Sorry I couldn't tell you. I want it to do from VB / ADO will I be able to use same command from that also.

I can't try it here until tommorow

My Blog: http://dineshasanka.spaces.live.com/

noeld SSC Guru Points: 96590 More actions · Answer 3

Just Keep in mind That SET IDENTITY INSERT can only be use on 1 table at a time in the database

and Yes you can execute it from vb too just make sure you have the necessary rights

HTH

* Noel

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 4

Thankx I will check and let you know

My Blog: http://dineshasanka.spaces.live.com/

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 5

NOOOOOOO it is not working from VB

here is VB code

connSConfig.Execute "SET IDENTITY_INSERT [AlarmClass] ON"

connSConfig.Execute "Insert into [AlarmClass] (ID,AlarmClass,Priority,BPM,WAVFile,PlayType,VectoringGroupID,AlarmHelpStringID,AlarmMsg,AlarmTextColor,AlarmBackColor,AckTextColor,AckBackColor) Values (11,'Cleared',10,10,'','',0,1,'',16777215,16711680,0,16777215)"

this gives folowing error

Cannot insert explicit value for identity column in table 'AlarmClass' when IDENTITY_INSERT is set to OFF.

But it is working from Query Analyzer

My Blog: http://dineshasanka.spaces.live.com/

Chiara SSC Veteran Points: 212 More actions · Answer 6

Maybe you can try...

connSConfig.Execute "SET IDENTITY_INSERT [AlarmClass] ON; Insert into [AlarmClass] (ID,AlarmClass,Priority,BPM,WAVFile,PlayType,VectoringGroupID,AlarmHelpStringID,AlarmMsg,AlarmTextColor,AlarmBackColor,AckTextColor,AckBackColor) Values (11,'Cleared',10,10,'','',0,1,'',16777215,16711680,0,16777215)"

All in one query...

Bye, Chiara

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 7

Scope problem, try this:

connSConfig.Execute "SET IDENTITY_INSERT [AlarmClass] ON; Insert into [AlarmClass] (ID,AlarmClass,Priority,BPM,WAVFile,PlayType,VectoringGroupID,AlarmHelpStringID,AlarmMsg,AlarmTextColor,AlarmBackColor,AckTextColor,AckBackColor) Values (11,'Cleared',10,10,'','',0,1,'',16777215,16711680,0,16777215)"

/rockmoose

You must unlearn what You have learnt

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 8

Hi Chiara!

You are fast

/rockmoose

You must unlearn what You have learnt

Chiara SSC Veteran Points: 212 More actions · Answer 9

Hi /rockmoose,

I'm a "newbie" but I hope I will contribute...

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 10

I am sure You will Chiara

/rockmoose

You must unlearn what You have learnt

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 11

IT WORKSSSSSSSS!!!!!!!

Thankx rockmoose and Chiara for the great help

My Blog: http://dineshasanka.spaces.live.com/

gljjr SSChampion Points: 10080 More actions · Answer 12

Dinesh,

Now that you have it working I would highly suggest you turn this into a stored procedure instead. Then you can raise an error if the insert fails for some reason. Besides, this will make your VB code much easier to follow as well.

Sample of SP

IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id('usp_AlarmClass_Ins'))

BEGIN

PRINT 'Dropping usp_AlarmClass_Ins'

DROP PROCEDURE usp_AlarmClass_Ins

END

GO

CREATE PROCEDURE usp_AlarmClass_Ins

(

@ID int

,@AlarmClass varchar(25)

,@Priority varchar(25)

,@BPM varchar(25)

,@WAVFile varchar(25)

,@PlayType varchar(25)

,@VectoringGroupID varchar(25)

,@AlarmHelpStringID varchar(25)

,@AlarmMsg varchar(25)

,@AlarmTextColor varchar(25)

,@AlarmBackColor varchar(25)

,@AckTextColor varchar(25)

,@AckBackColor varchar(25)

)

AS

SET IDENTITY_INSERT [AlarmClass] ON

INSERT INTO [AlarmClass]

(

ID

,AlarmClass

,Priority

,BPM

,WAVFile

,PlayType

,VectoringGroupID

,AlarmHelpStringID

,AlarmMsg

,AlarmTextColor

,AlarmBackColor

,AckTextColor

,AckBackColor

)

VALUES

(

@ID

,@AlarmClass

,@Priority

,@BPM

,@WAVFile

,@PlayType

,@VectoringGroupID

,@AlarmHelpStringID

,@AlarmMsg

,@AlarmTextColor

,@AlarmBackColor

,@AckTextColor

,@AckBackColor

)

IF @@ERROR !=0

BEGIN

RAISERROR('Error Inserting Record',16,1)

END

RETURN

GO

Sample of VB Code...(Note: you could also use the Command object with specific parameters too)

connSConfig.Execute "exec usp_AlarmClass_Ins 11,'Cleared',10,10,'','',0,1,'',16777215,16711680,0,16777215"

'Add appropriate error handling here

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 13

I totally agree with Gary that the use of stored procedures is a very good idea.

However IMO consistency in code is also important.

If Dinesh has an application that does not rely on stored procedures, but instead contains the SQL in the code, I am not convinced that breaking the pattern just for this specific case is a good idea.

Now the discussion of wether to embed SQL in the code or use Stored Procedures is a completely differnt issue. I think it is good to encapsulate all the CRUD operations on the database in sp's, but am not sure that Dinesh wihes to rewrite his app

/rockmoose

You must unlearn what You have learnt