Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SET IDENTITY_INSERT PRODUCTS ON not working

Posted on 2006-04-22
11
Medium Priority
?
452 Views
Last Modified: 2011-10-03
Hi,

 First I'm a real beginner, so be very basic with me, ok? I deleted a row I needed, and I have been successful in inserting a new row with all the variables. However I would like the id column value to be 384. I understand that I can re-insert the whole row if I SET IDENTITY_INSERT PRODUCTS ON, (where my table name is products), and then insert the row, with the value for id being 384:

Insert into products ("id", "code", "name")
VALUES (' 384', 'CTG', 'Heat and Eat!')

Then I should SET IDENTITY_INSERT PRODUCTS OFF.

However, if I try this, I still get an error:
"Cannot insert explicit value for identity column in table products when IDENTITY_INSERT is set to OFF."

Can anyone help? Thanks!
0
Comment
Question by:mel150
11 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 16517075
To clarify, is this the entire script that youre running:


SET IDENTITY_INSERT PRODUCTS ON

Insert into products ("id", "code", "name")
VALUES (' 384', 'CTG', 'Heat and Eat!')

SET IDENTITY_INSERT PRODUCTS OFF



If id truly is an identity column then you should be inserting a number not a string - see script below and note that 384 is nor surrounded by quotes.

Also note I have surrounded column names with square brackets. This is the default syntax for SQL Server. This may or may not have been causing you problems also. Normally you need to set Quote_Identifiers on to do it in your original syntax.



SET IDENTITY_INSERT PRODUCTS ON

Insert into products ([id], [code], [name])
VALUES (384, 'CTG', 'Heat and Eat!')

SET IDENTITY_INSERT PRODUCTS OFF
0
 

Author Comment

by:mel150
ID: 16517214
Hi- thanks for this, unfortunately I am getting the same result. I copied and pasted your script. SET IDENTITY_INSERT PRODUCTS ON  hit run, then  

Insert into products ([id], [code], [name])
VALUES (384, 'CTG', 'Heat and Eat!')

when I hit run, I get the same error as before..
0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 16517446
Unfortunately on this one, schema for the table is going to be necessary to know which column is being seen as an IDENTITY column.

Ben.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:mel150
ID: 16517486
Sure thing-I hope this is what you need, I ran:
select * from information_schema.columns where table_name = 'products':

It's pretty big, if you only want certain columns please let me know:

A4989|dbo|products|id|1||No |int|||10|10|0||||||||||
A4989|dbo|products|code|2||YES|varchar|100|100|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|sale_price_apply|27|(0)|YES|tinyint|||3|10|0||||||||||
A4989|dbo|products|price_display|28||YES|varchar|50|50|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|large_picture|29||YES|varchar|100|100|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|model_number|30||YES|varchar|100|100|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|baseprod_charge|31|(0)|YES|float|||53|10|||||||||||
A4989|dbo|products|product_type|32|(2)|YES|int|||10|10|0||||||||||
A4989|dbo|products|classDate|33||YES|datetime|||23||3|3|||||||||
A4989|dbo|products|Prod_shortname|34||YES|varchar|50|50|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|special_session|35|(0)|YES|bit|||1||0||||||||||
A4989|dbo|products|custom_session|36|(0)|YES|bit|||1||0||||||||||
A4989|dbo|products|special_email|37||YES|text|2147483647|2147483647|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|send_email|38|(0)|YES|bit|||1||0||||||||||
A4989|dbo|products|required_items|39||YES|int|||10|10|0||||||||||
A4989|dbo|products|custom_cat|40|(0)|YES|bit|||1||0||||||||||
A4989|dbo|products|custom_image_default|41||YES|varchar|50|50|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|custom_image_additional|42||YES|varchar|50|50|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|custom_path|43|(0)|YES|bit|||1||0||||||||||
A4989|dbo|products|alt_addition_Cat_short_description|44||YES|text|2147483647|2147483647|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|nutrition_flag|45|(0)|YES|bit|||1||0||||||||||
A4989|dbo|products|reheating_flag|46|(0)|YES|bit|||1||0||||||||||
A4989|dbo|products|reheating_text|47||YES|text|2147483647|2147483647|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|wine_flag|48|(0)|YES|bit|||1||0||||||||||
A4989|dbo|products|primary_choice|49||YES|text|2147483647|2147483647|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|secondary_choice|50||YES|text|2147483647|2147483647|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|display_name|51|(0)|YES|bit|||1||0||||||||||
A4989|dbo|products|to_go|52|(0)|YES|bit|||1||0||||||||||
A4989|dbo|products|maximum_item|53|(0)|YES|int|||10|10|0||||||||||
A4989|dbo|products|personal_chef|54|(0)|YES|bit|||1||0||||||||||
A4989|dbo|products|sp_delivery|55|(0)|YES|bit|||1||0||||||||||
A4989|dbo|products|weight|56|(0)|YES|float|||53|10|||||||||||
A4989|dbo|products|charge_tax|57|(0)|YES|bit|||1||0||||||||||
A4989|dbo|products|heart|58||YES|text|2147483647|2147483647|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|dsp_short_desc|59|(0)|YES|bit|||1||0||||||||||
A4989|dbo|products|oversize|60|(0)|YES|smallint|||5|10|0||||||||||
A4989|dbo|products|specific_product_type_id|61||YES|int|||10|10|0||||||||||
A4989|dbo|products|high_cost|62|(0.0000)|No |money|||19|10|4||||||||||
A4989|dbo|products|reporting_category|63||YES|varchar|50|50|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|ingredients|64||YES|varchar|1000|1000|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|class_day_time|65||YES|varchar|50|50|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|max_seats_in_class|66||YES|int|||10|10|0||||||||||
A4989|dbo|products|production_time|67|(0)|No |int|||10|10|0||||||||||
A4989|dbo|products|batch_size|68|(0)|No |int|||10|10|0||||||||||
A4989|dbo|products|weekly_average|69|(0)|No |float|||53|10|||||||||||
A4989|dbo|products|activation_date|70|(getdate())|No |datetime|||23||3|3|||||||||
A4989|dbo|products|deactivation_date|71|(getdate())|YES|datetime|||23||3|3|||||||||
A4989|dbo|products|decrement_inventory|72|(1)|No |tinyint|||3|10|0||||||||||
A4989|dbo|products|packaging_time|73|(0)|No |int|||10|10|0||||||||||
A4989|dbo|products|name|3||YES|varchar|256|256|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|description|4||YES|text|2147483647|2147483647|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|short_description|5||YES|text|2147483647|2147483647|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|keywords|6||YES|text|2147483647|2147483647|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|ship_note|7||YES|text|2147483647|2147483647|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|image_file_name|8||YES|varchar|100|100|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|active|9|(1)|No |tinyint|||3|10|0||||||||||
A4989|dbo|products|visible_web_only|10|(0)|YES|tinyint|||3|10|0||||||||||
A4989|dbo|products|new|11||YES|tinyint|||3|10|0||||||||||
A4989|dbo|products|special|12|(0)|YES|tinyint|||3|10|0||||||||||
A4989|dbo|products|discount|13|(0)|YES|tinyint|||3|10|0||||||||||
A4989|dbo|products|taxable|14|(1)|YES|tinyint|||3|10|0||||||||||
A4989|dbo|products|price|15|(0)|YES|money|||19|10|4||||||||||
A4989|dbo|products|sale_price|16|(0)|YES|money|||19|10|4||||||||||
A4989|dbo|products|cost|17|(0)|No |money|||19|10|4||||||||||
A4989|dbo|products|unit|18||YES|varchar|50|50|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||
A4989|dbo|products|manufacturer_id|19||YES|int|||10|10|0||||||||||
A4989|dbo|products|stock|20|(0)|YES|int|||10|10|0||||||||||
A4989|dbo|products|us_only|21|(0)|YES|tinyint|||3|10|0||||||||||
A4989|dbo|products|gift_cert|22|(0)|YES|bit|||1||0||||||||||
A4989|dbo|products|gift_cert_force_value|23|(0)|YES|bit|||1||0||||||||||
A4989|dbo|products|sizes|24|(0)|YES|tinyint|||3|10|0||||||||||
A4989|dbo|products|inventory_control|25|(0)|YES|int|||10|10|0||||||||||
A4989|dbo|products|thumbnail|26||YES|varchar|50|50|||||||iso_1|||SQL_Latin1_General_CP1_CI_AS|||


Thanks so much!

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16517643
Cn you check whetther there exists, more than one table with the name 'products' with different owners...
you can use enterprise manger or the following Query

select * from INFORMATION_SCHEMA.TABLES
WHERE  table_name = 'products'

0
 

Author Comment

by:mel150
ID: 16517648
No, only the one table products:
select * from INFORMATION_SCHEMA.TABLES
WHERE  table_name = 'products'

select * from INFORMATION_SCHEMA.TABLES
WHERE  table_name = 'products'

However, I have a development website, and I know that the product id 384 shows up in various tables in various guises, for instance it may be in product_table_group_member where product_id=384

Could that be the issue here?
0
 

Author Comment

by:mel150
ID: 16517651
sorry, I meant to show you the results of the select:
A4989|dbo|products|BASE TABLE
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 16517668
it may be the issue with the Servive pack

read this MSDN article

http://support.microsoft.com/kb/878501/EN-US/
0
 

Author Comment

by:mel150
ID: 16517692
Oh, boy, this should be fun, since the SQlL server is at our web-host. Thanks a lot, though, I am pretty sure I'll give you the points on this, just give me till tomorrow to do a little investigation. Thanks again!
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16517694
oh sure..
you can use the following command to get the SQL server details

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
0
 

Author Comment

by:mel150
ID: 16588556
Sorry, sorry!! Forgot this was open.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question