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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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..
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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!

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'

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?
mel150Author Commented:
sorry, I meant to show you the results of the select:
A4989|dbo|products|BASE TABLE
Aneesh RetnakaranDatabase AdministratorCommented:
it may be the issue with the Servive pack

read this MSDN article

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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!
Aneesh RetnakaranDatabase AdministratorCommented:
oh sure..
you can use the following command to get the SQL server details

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')
mel150Author Commented:
Sorry, sorry!! Forgot this was open.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.