November 4, 2011 at 1:54 pm
I have a table that looks like this:
Create table dbo.OOReportFile (
path VARCHAR(256) not null,
sortOrder int not null,
id int identity,
repname VARCHAR(64) not null,
repfilepath VARCHAR(256) not null,
language_cd CHAR(2) null,
updateddate smalldatetime null,
repFormat VARCHAR(10) not null)
on [PRIMARY] ;
sample of the data attached.
I exported the data, then recreated the table and executed:
SET IDENTITY_INSERT dbo.OOReportFile ON
when I try to load the table, I get this error:
Insert error. Row=2 error. 23000(544)[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'OOReportFile' when IDENTITY_INSERT is set to OFF.
can anyone help?
November 4, 2011 at 2:05 pm
Are you setting identity_insert in the same session as the insert statement? That's the scope it's got.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 6, 2011 at 2:15 am
I am:
Connecting to the Database
issuing this against the Database for Table dbo.
SET IDENTITY_INSERT dbo.OOReportFile ON
Committing the Transaction
Disconnecting from the DB
Reconnecting to the DB
WHen I load the table, I still get this?
Insert error. Row=1 error. 37000(128)[Microsoft][ODBC SQL Server Driver][SQL Server]The name "sortOrder" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables.
Insert error. Row=2 error. 23000(544)[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'OOReportFile' when IDENTITY_INSERT is set to OFF.
November 6, 2011 at 3:41 pm
how are you loading the data?
as GSquared has already pointed out you must set identity_insert on within the same session
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 7, 2011 at 1:58 am
Loading the data like this
SET IDENTITY_INSERT dbo.OOReportFile ON
insert into dbo.OOReportFile (path,sortOrder,id,repname,repfilepath,language_cd,updateddate,repFormat) values('templatedata\Report\OOReportFile\data\ReportsAndPeriodicals_pdf', 1, 109, 'report_CircleOfHonor', '/resources/COH_7_31.pdf', 'en', '8/13/2008', 'PDF');
insert into dbo.OOReportFile (path,sortOrder,id,repname,repfilepath,language_cd,updateddate,repFormat) values('templatedata\Report\OOReportFile\data\ReportsAndPeriodicals_pdf', 2, 110, 'report_MonthlyNCDQuota', '/resources/ncdquota_rpt.pdf', 'en', '10/1/2008', 'PDF');
insert into dbo.OOReportFile (path,sortOrder,id,repname,repfilepath,language_cd,updateddate,repFormat) values('templatedata\Report\OOReportFile\data\ReportsAndPeriodicals_pdf', 3, 111, 'report_CouncilsReactivationQuota', '/resources/coreact_rpt.pdf', 'en', '10/1/2008', 'PDF');
insert into dbo.OOReportFile (path,sortOrder,id,repname,repfilepath,language_cd,updateddate,repFormat) values('templatedata\Report\OOReportFile\data\ReportsAndPeriodicals_pdf', 4, 112, 'report_MonthlyRoundTableQuota', '/resources/rt_rpt.pdf', 'en', '10/1/2008', 'PDF');
insert into dbo.OOReportFile (path,sortOrder,id,repname,repfilepath,language_cd,updateddate,repFormat) values('templatedata\Report\OOReportFile\data\ReportsAndPeriodicals_pdf', 5, 113, 'report_MonthlyCollegeMembership', '/resources/cc_rpt.pdf', 'en', '6/3/2008', 'PDF');
insert into dbo.OOReportFile (path,sortOrder,id,repname,repfilepath,language_cd,updateddate,repFormat) values('templatedata\Report\OOReportFile\data\ReportsAndPeriodicals_pdf', 6, 114, 'report_MonthlyDegreeScheduled', '/resources/degrees_sch.pdf', 'en', '5/2/2008', 'PDF');
insert into dbo.OOReportFile (path,sortOrder,id,repname,repfilepath,language_cd,updateddate,repFormat) values('templatedata\Report\OOReportFile\data\ReportsAndPeriodicals_pdf', 7, 115, 'report_MonthlyDegreeStatus', '/resources/degrees_exem.pdf', 'en', '10/2/2008', 'PDF');
insert into dbo.OOReportFile (path,sortOrder,id,repname,repfilepath,language_cd,updateddate,repFormat) values('templatedata\Report\OOReportFile\data\ReportsAndPeriodicals_xls', 1, 88, 'report_CircleOfHonor', '/resources/COH_7_31.xls', 'en', '8/13/2008', 'EXCEL');
insert into dbo.OOReportFile (path,sortOrder,id,repname,repfilepath,language_cd,updateddate,repFormat) values('templatedata\Report\OOReportFile\data\ReportsAndPeriodicals_xls', 2, 89, 'report_MonthlyNCDQuota', '/resources/ncdquota_rpt.xls', 'en', '10/1/2008', 'EXCEL');
insert into dbo.OOReportFile (path,sortOrder,id,repname,repfilepath,language_cd,updateddate,repFormat) values('templatedata\Report\OOReportFile\data\ReportsAndPeriodicals_xls', 3, 90, 'report_CouncilsReactivationQuota', '/resources/coreact_rpt.xls', 'en', '10/1/2008', 'EXCEL');
insert into dbo.OOReportFile (path,sortOrder,id,repname,repfilepath,language_cd,updateddate,repFormat) values('templatedata\Report\OOReportFile\data\ReportsAndPeriodicals_xls', 4, 91, 'report_MonthlyRoundTableQuota', '/resources/rt_rpt.xls', 'en', '10/1/2008', 'EXCEL');
insert into dbo.OOReportFile (path,sortOrder,id,repname,repfilepath,language_cd,updateddate,repFormat) values('templatedata\Report\OOReportFile\data\ReportsAndPeriodicals_xls', 5, 92, 'report_MonthlyCollegeMembership', '/resources/cc_rpt.xls', 'en', '6/3/2008', 'EXCEL');
insert into dbo.OOReportFile (path,sortOrder,id,repname,repfilepath,language_cd,updateddate,repFormat) values('templatedata\Report\OOReportFile\data\ReportsAndPeriodicals_xls', 6, 93, 'report_MonthlyDegreeScheduled', '/resources/degrees_sch.xls', 'en', '5/2/2008', 'EXCEL');
insert into dbo.OOReportFile (path,sortOrder,id,repname,repfilepath,language_cd,updateddate,repFormat) values('templatedata\Report\OOReportFile\data\ReportsAndPeriodicals_xls', 7, 94, 'report_MonthlyDegreeStatus', '/resources/degrees_exem.xls', 'en', '10/2/2008', 'EXCEL');
getting this error:
23000(544)[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'OOReportFile' when IDENTITY_INSERT is set to OFF.
November 7, 2011 at 2:32 am
Hello,
I see you received two error messages,
Jpotucek (11/6/2011)
Insert error. Row=1 error. 37000(128)[Microsoft][ODBC SQL Server Driver][SQL Server]The name "sortOrder" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables.Insert error. Row=2 error. 23000(544)[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert explicit value for identity column in table 'OOReportFile' when IDENTITY_INSERT is set to OFF.
I cannot ensure this but I suppose things could be like that:
- sortOrder is a reserved word, for this reason you receive the first error message.
- After this error the DB engine turns OFF the IDENTITY_INSERT.
- That's the origin for the second error message.
Try to put sortOrder between brackets, maybe this solves your problem.
Regards,
Francesc
November 7, 2011 at 5:15 am
Thank you so much!!! putting [sortorder] in brackets did the trick!!!!!!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply