SET IDENTITY_INSERT PRODUCTS ON not working

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!
mel150Asked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
it may be the issue with the Servive pack

read this MSDN article

http://support.microsoft.com/kb/878501/EN-US/
0
 
nmcdermaidCommented:
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
 
mel150Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DBAduck - Ben MillerPrincipal ConsultantCommented:
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
 
mel150Author Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
mel150Author Commented:
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
 
mel150Author Commented:
sorry, I meant to show you the results of the select:
A4989|dbo|products|BASE TABLE
0
 
mel150Author Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
oh sure..
you can use the following command to get the SQL server details

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
0
 
mel150Author Commented:
Sorry, sorry!! Forgot this was open.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.