SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 SET IDENTITY INSERT ON
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cray
Starting Member

9 Posts

Posted - 11/18/2005 :  09:53:30  Show Profile  Reply with Quote
Created a new table within my software then went to Enterprise Mgr to set the one key field to be an identity field - so it could be an automatic unique identifier. When I try to enter data get error - IDENTITY INSERT SET TO OFF. Tried to do a

Select *
from dbo.table
SET IDENTITY INSERT table ON

but SQL did not understand INDETITY

Any help appreciated.

My accting software is set on their tables to sequence some tables by user entering 'new' and when you tab out of that field on a new record the software autosets the next sequential number. Want to do same on this table I'm trying to create.

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 11/18/2005 :  09:56:33  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote

SET IDENTITY_INSERT table ON
Insert records
SET IDENTITY_INSERT table OFF



Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

cray
Starting Member

9 Posts

Posted - 11/18/2005 :  10:01:46  Show Profile  Reply with Quote
table is not a user table. Cannot perform SET operation.

What does this mean???? Receive this error after attempting to set identity_insert on.
Go to Top of Page

SamC
White Water Yakist

USA
3464 Posts

Posted - 11/18/2005 :  11:09:36  Show Profile  Reply with Quote
SET IDENTITY_INSERT [your table name goes here] ON
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 11/18/2005 :  22:32:57  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
You might want to check out Books Online. It's included free with SQL Server. Here's a quote from it:

quote:

Transact-SQL Reference


SET IDENTITY_INSERT
Allows explicit values to be inserted into the identity column of a table.

Syntax
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

Arguments
database

Is the name of the database in which the specified table resides.

owner

Is the name of the table owner.

table

Is the name of a table with an identity column.

Remarks
At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

Permissions
Execute permissions default to the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and the object owner.

Examples
This example creates a table with an identity column and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.

-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver')
INSERT INTO products (product) VALUES ('hammer')
INSERT INTO products (product) VALUES ('saw')
INSERT INTO products (product) VALUES ('shovel')
GO

-- Create a gap in the identity values.
DELETE products
WHERE product = 'saw'
GO

SELECT *
FROM products
GO

-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO

-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO

SELECT *
FROM products
GO
-- Drop products table.
DROP TABLE products
GO


See Also

CREATE TABLE

IDENTITY (Property)

INSERT

SET

©1988-2000 Microsoft Corporation. All Rights Reserved.



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

ChrisB
Starting Member

6 Posts

Posted - 11/29/2005 :  20:32:31  Show Profile  Reply with Quote
I've run into the same problem. Sometimes it works, but most of the time it doesn't. I found this on MS: http://support.microsoft.com/default.aspx?scid=kb;en-us;878501
It says that SP4 fixes the problem, so I installed it, but I'm still getting the error. If anyone finds out something, please let me know.

Thanks,
Chris
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 11/29/2005 :  20:40:42  Show Profile  Visit tkizer's Homepage  Reply with Quote
Chris,

Please post the code that you are using that is giving you the error.

Tara Kizer
aka tduggan
Go to Top of Page

ChrisB
Starting Member

6 Posts

Posted - 11/29/2005 :  21:28:18  Show Profile  Reply with Quote
I'm doing this within a stored procedure, looping through a number of tables. I experimented in QA using the below:

declare @PKTable nvarchar(100)
set @PKTable = 'tblActGroup'
SET IDENTITY_INSERT @PKTable ON

exec('SET IDENTITY_INSERT ' + @PKTable + ' ON')

exec('SET IDENTITY_INSERT tblActGroup ON')

SET IDENTITY_INSERT tblActGroup ON
SET IDENTITY_INSERT tblActGroup OFF
SET IDENTITY_INSERT tblActGroupDet ON
SET IDENTITY_INSERT tblActGroupDet OFF

I tried different combinations. The hard-coded commands always worked as expected. The exec('...') commands would say they executed successfully, but in fact they didn't.

I've read the BOL carefully, but can't figure out why the same command behaves differently.

Thanks,
Chris
Go to Top of Page

ChrisB
Starting Member

6 Posts

Posted - 11/29/2005 :  21:30:09  Show Profile  Reply with Quote
Oops, please ignore the line:
SET IDENTITY_INSERT @PKTable ON

I tried it just to see if it would work; it didn't.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37142 Posts

Posted - 11/30/2005 :  12:32:08  Show Profile  Visit tkizer's Homepage  Reply with Quote
You can't do IDENTITY_INSERT dynamically. Dynamic SQL exists in its own session, so you aren't able to get to it once you run the next command. You will have to hard code the table names.

Tara Kizer
aka tduggan
Go to Top of Page

Palinmaj
Starting Member

Spain
1 Posts

Posted - 01/10/2011 :  10:34:41  Show Profile  Reply with Quote
or try using the old trick.

DECLARE @StrSql as varchar(100)
set @StrSql = 'SET IDENTITY_INSERT ' + @PKTable + ' OFF'
exec (@strsql)

i tried it and works fine.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000