Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

remove identity property Expand / Collapse
Author
Message
Posted Tuesday, August 28, 2012 11:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 3:54 AM
Points: 157, Visits: 690
hello all.
I want to insert into identity column and i want to first remove identity property and insert value and then enable identity property.I use this script:SET IDENTITY_INSERT IdentityTable ON

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (3, 'First Row')

SET IDENTITY_INSERT IdentityTable OFF but get error :Cannot insert explicit value for identity column . ho w do i do for this goal?please guide me.thanks
Post #1351394
Posted Tuesday, August 28, 2012 11:49 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:39 PM
Points: 286, Visits: 582
Permissions issue? IDENTITY_INSERT requires that you are the owner of the table or are a member of db_owner or db_ddladmin on the database.

Have you correctly qualified the table name with the schema?


Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1351398
Posted Wednesday, August 29, 2012 12:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 3:54 AM
Points: 157, Visits: 690
I login with sa.is that enough?how to member of db_owner or db_owner?
Post #1351402
Posted Wednesday, August 29, 2012 12:13 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:39 PM
Points: 286, Visits: 582
Permissions are not the issue then if you're a member of sysadmins.

I'm not sure what else can be the issue; its a fairly straightforward process usually. When you run SET IDENTITY_INSERT IdentityTable ON; do you get any error message?


Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1351406
Posted Wednesday, August 29, 2012 12:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 3:54 AM
Points: 157, Visits: 690
sa is db_owner and when I select dbo.ddladmin for my database ,get error:can not use the special principal dbo.
how do i do?
Post #1351409
Posted Wednesday, August 29, 2012 12:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 3:54 AM
Points: 157, Visits: 690
my error just is:
An explicit value for the identity column in table 'WOS_WorkOrderType' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Post #1351410
Posted Wednesday, August 29, 2012 12:20 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:39 PM
Points: 286, Visits: 582
As I said in my last post, if you're running the code as a member of sysadmins then permissions are not a problem and you don't need to do anything.

Are you getting any error message when you execute SET IDENTITY_INSERT IdentityTable ON; ?


Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1351412
Posted Wednesday, August 29, 2012 12:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 3:54 AM
Points: 157, Visits: 690
my error :
An explicit value for the identity column in table 'WOS_WorkOrderType' can only be specified when a column list is used and IDENTITY_INSERT is ON.
and I have only this error
Post #1351414
Posted Wednesday, August 29, 2012 12:41 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:39 PM
Points: 286, Visits: 582
Ok, so I do this...


SET IDENTITY_INSERT dbo.TestTable ON

and get this...


Command(s) completed successfully.


What message do you get?


Clare
_________________________________________________________________________________________________________________
Measure twice; cut once (and have a good saw)

Hey, just a thought.....did you check Books Online yet?
Post #1351418
Posted Wednesday, August 29, 2012 12:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 3:54 AM
Points: 157, Visits: 690
yes get command complete succesfully.
but when insert into identity table get error:
An explicit value for the identity column in table 'WOS_WorkOrderType' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Post #1351419
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse