asked on

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!

nmcdermaid

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

mel150

ASKER

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..

DBAduck - Ben Miller

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.

mel150

ASKER

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!

Aneesh

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'

mel150

ASKER

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?

mel150

ASKER

sorry, I meant to show you the results of the select:
A4989|dbo|products|BASE TABLE

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mel150

ASKER

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!

Aneesh

oh sure..
you can use the following command to get the SQL server details

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

mel150

ASKER

Sorry, sorry!! Forgot this was open.