[Help] Error inserting data into skills table (shstudio 7.5.5)

01/16/2015 10:44 DeviL125#1

Hello all, I have been away from this for some time now and am CERTAINLY rusty.. however I haven't had this particular error before. This is really more of an SQL error than a shaiya problem but this being a typical scenario for many of you I thought I'd ask here. In MSSQL Server 2012 I was syncing up my database to my client files, generating the .sql files from Castor's new version of shStudio (v7.5.5) most went fine... as normal.. but running the query for PS_GameDefs.dbo.Skills and it is formatted as follows

Code:

DELETE FROM [PS_GameDefs].[dbo].[Skills] WHERE SkillLevel<100;
GO
INSERT INTO PS_GameDefs.dbo.Skills values (1,1,'Strength Training Lv1',6,1,0,1,0,0,0,0,1,0,1,1,0,0,4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,23,10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,GetDate())
INSERT INTO PS_GameDefs.dbo.Skills values (1,2,'Strength Training Lv2',6,1,0,1,0,0,0,0,12,0,2,1,0,0,4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,23,18,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,GetDate())
INSERT INTO PS_GameDefs.dbo.Skills values (1,3,'Strength Training Lv3',6,1,0,1,0,0,0,0,23,0,2,1,0,0,4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,23,26,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,GetDate())
INSERT INTO PS_GameDefs.dbo.Skills values (1,4,'Strength Training Lv4',6,1,0,1,0,0,0,0,34,0,2,1,0,0,4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,23,34,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,GetDate())
INSERT INTO PS_GameDefs.dbo.Skills values (1,5,'Strength Training Lv5',6,1,0,1,0,0,0,0,45,0,2,1,0,0,4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,23,42,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,GetDate())
INSERT INTO PS_GameDefs.dbo.Skills values (1,6,'Strength Training Lv6',6,1,0,1,0,0,0,0,56,0,2,1,0,0,4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,23,50,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,GetDate())
INSERT INTO PS_GameDefs.dbo.Skills values (1,7,'Strength Training Lv7',6,1,0,1,0,0,0,0,67,0,3,1,0,0,4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,23,58,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,GetDate())
INSERT INTO PS_GameDefs.dbo.Skills values (1,8,'Strength Training Lv8',6,1,0,1,0,0,0,0,78,0,4,1,0,0,4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,23,66,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,GetDate())
INSERT INTO PS_GameDefs.dbo.Skills values (1,9,'rfu',6,1,0,1,0,0,0,0,0,0,0,1,0,0,4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,GetDate())


etc.. etc... you get the idea it's long

I get the following error

Code:

(0 row(s) affected)
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'PS_GameDefs.dbo.Skills' can only be specified when a column list is used and IDENTITY_INSERT is ON

Now I have looked around for the answer for this (outside of shaiya development AND in) but I am either not finding it or it is just not clicking in my head. I have tried adding

Code:

SET IDENTITY_INSERT [PS_GameDefs].[dbo].[Skills] ON
GO

to the beginning of the query to no effect, same error. The only other solution i have found is to name the identity of each column.. for each comma separated entry, and this being an insert of 6000+ lines that just isnt a viable solution. I am sure the query could be written to accomplish this but that is a little deeper than I have learned (so far) and I just don't think this is normally this problematic.. else I don't think Castor, being among the best I've seen developing here would have them generated in the format they are. More than anything I just want to learn something, as I normally never ask for help unless I am at my wits end lol. Any help is greatly appreciated.

01/16/2015 23:07 wallerus#2

Use this to drop and recreate your Skills table, then try rerunning the script generated from ShStudio. I think it may be a problem with the RowID column.

Code:

USE [PS_GameDefs]
GO

ALTER TABLE [dbo].[Skills] DROP CONSTRAINT [DF_Skills_UpdateDate]
GO

DROP TABLE [dbo].[Skills]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Skills](
	[RowID] [int] IDENTITY(1,1) NOT NULL,
	[SkillID] [smallint] NOT NULL,
	[SkillLevel] [tinyint] NOT NULL,
	[SkillName] [varchar](30) NOT NULL,
	[Country] [tinyint] NOT NULL,
	[Attackfighter] [tinyint] NOT NULL,
	[Defensefighter] [tinyint] NOT NULL,
	[Patrolrogue] [tinyint] NOT NULL,
	[Shootrogue] [tinyint] NOT NULL,
	[Attackmage] [tinyint] NOT NULL,
	[Defensemage] [tinyint] NOT NULL,
	[PrevSkillID] [smallint] NOT NULL,
	[ReqLevel] [smallint] NOT NULL,
	[Grow] [tinyint] NOT NULL,
	[SkillPoint] [tinyint] NOT NULL,
	[TypeShow] [tinyint] NOT NULL,
	[TypeAttack] [tinyint] NOT NULL,
	[TypeEffect] [tinyint] NOT NULL,
	[TypeDetail] [smallint] NOT NULL,
	[NeedWeapon1] [tinyint] NOT NULL,
	[NeedWeapon2] [tinyint] NOT NULL,
	[NeedWeapon3] [tinyint] NOT NULL,
	[NeedWeapon4] [tinyint] NOT NULL,
	[NeedWeapon5] [tinyint] NOT NULL,
	[NeedWeapon6] [tinyint] NOT NULL,
	[NeedWeapon7] [tinyint] NOT NULL,
	[NeedWeapon8] [tinyint] NOT NULL,
	[NeedWeapon9] [tinyint] NOT NULL,
	[NeedWeapon10] [tinyint] NOT NULL,
	[NeedWeapon11] [tinyint] NOT NULL,
	[NeedWeapon12] [tinyint] NOT NULL,
	[NeedWeapon13] [tinyint] NOT NULL,
	[NeedWeapon14] [tinyint] NOT NULL,
	[NeedWeapon15] [tinyint] NOT NULL,
	[Shield] [tinyint] NOT NULL,
	[SP] [smallint] NOT NULL,
	[MP] [smallint] NOT NULL,
	[ReadyTime] [tinyint] NOT NULL,
	[ResetTime] [smallint] NOT NULL,
	[AttackRange] [tinyint] NOT NULL,
	[StateType] [tinyint] NOT NULL,
	[AttrType] [tinyint] NOT NULL,
	[Disable] [smallint] NOT NULL,
	[SuccessType] [tinyint] NOT NULL,
	[SuccessValue] [tinyint] NOT NULL,
	[TargetType] [tinyint] NOT NULL,
	[ApplyRange] [tinyint] NOT NULL,
	[MultiAttack] [tinyint] NOT NULL,
	[KeepTime] [smallint] NOT NULL,
	[Weapon1] [tinyint] NOT NULL,
	[Weapon2] [tinyint] NOT NULL,
	[Weaponvalue] [tinyint] NOT NULL,
	[Bag] [tinyint] NOT NULL,
	[Arrow] [smallint] NOT NULL,
	[DamageType] [tinyint] NOT NULL,
	[DamageHP] [smallint] NOT NULL,
	[DamageSP] [smallint] NOT NULL,
	[DamageMP] [smallint] NOT NULL,
	[TimeDamageType] [tinyint] NOT NULL,
	[TimeDamageHP] [smallint] NOT NULL,
	[TimeDamageSP] [smallint] NOT NULL,
	[TimeDamageMP] [smallint] NOT NULL,
	[AddDamageHP] [smallint] NOT NULL,
	[AddDamageSP] [smallint] NOT NULL,
	[AddDamageMP] [smallint] NOT NULL,
	[AbilityType1] [tinyint] NULL,
	[AbilityValue1] [smallint] NULL,
	[AbilityType2] [tinyint] NULL,
	[AbilityValue2] [smallint] NULL,
	[AbilityType3] [tinyint] NULL,
	[AbilityValue3] [smallint] NULL,
	[HealHP] [smallint] NOT NULL,
	[HealSP] [smallint] NOT NULL,
	[HealMP] [smallint] NOT NULL,
	[TimeHealHP] [smallint] NOT NULL,
	[TimeHealSP] [smallint] NOT NULL,
	[TimeHealMP] [smallint] NOT NULL,
	[DefenceType] [tinyint] NOT NULL,
	[DefenceValue] [tinyint] NOT NULL,
	[LimitHP] [tinyint] NOT NULL,
	[FixRange] [tinyint] NOT NULL,
	[ChangeType] [smallint] NOT NULL,
	[ChangeLevel] [smallint] NOT NULL,
	[UpdateDate] [datetime] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Skills] ADD  CONSTRAINT [DF_Skills_UpdateDate]  DEFAULT (getdate()) FOR [UpdateDate]
GO

01/17/2015 08:10 DeviL125#3

It most definitely has to be, I used CREATE SCRIPT to generate a script of the empty table and sent both to a friend of a friend who regenerated the script with the column list in the INSERT statement as follows:

Code:

DELETE FROM [PS_GameDefs].[dbo].[Skills] WHERE SkillLevel<100;
GO
INSERT INTO PS_GameDefs.dbo.Skills ([SkillID]  ,	[SkillLevel]  ,	[SkillName]  ,	[Country]  ,	[Attackfighter]  ,	[Defensefighter]  ,	[Patrolrogue]  ,[Shootrogue]  ,[Attackmage]  ,	[Defensemage]  ,	[PrevSkillID]  ,	[ReqLevel]  ,	[Grow]  ,	[SkillPoint]  ,	[TypeShow]  ,	[TypeAttack]  ,	[TypeEffect]  ,[TypeDetail]  ,	[NeedWeapon1]  ,	[NeedWeapon2]  ,	[NeedWeapon3]  ,	[NeedWeapon4]  ,	[NeedWeapon5]  ,	[NeedWeapon6]  ,[NeedWeapon7]  ,	[NeedWeapon8]  ,	[NeedWeapon9]  ,	[NeedWeapon10]  ,	[NeedWeapon11]  ,	[NeedWeapon12]  ,	[NeedWeapon13]  ,[NeedWeapon14]  ,	[NeedWeapon15]  ,	[Shield]  ,	[SP]  ,	[MP]  ,	[ReadyTime]  ,	[ResetTime]  ,	[AttackRange]  ,	[StateType]  ,	[AttrType]  ,[Disable]  ,	[SuccessType]  ,	[SuccessValue]  ,	[TargetType]  ,	[ApplyRange]  ,	[MultiAttack]  ,	[KeepTime]  ,	[Weapon1]  ,	[Weapon2]  ,[Weaponvalue]  ,	[Bag]  ,	[Arrow]  ,	[DamageType]  ,	[DamageHP]  ,	[DamageSP]  ,	[DamageMP]  ,	[TimeDamageType]  ,	[TimeDamageHP]  ,[TimeDamageSP]  ,	[TimeDamageMP]  ,	[AddDamageHP]  ,	[AddDamageSP]  ,	[AddDamageMP]  ,	[AbilityType1]  ,	[AbilityValue1]  ,[AbilityType2]  ,	[AbilityValue2]  ,	[AbilityType3]  ,	[AbilityValue3]  ,	[HealHP]  ,	[HealSP]  ,	[HealMP]  ,	[TimeHealHP]  ,[TimeHealSP]  ,	[TimeHealMP]  ,	[DefenceType]  ,	[DefenceValue]  ,	[LimitHP]  ,	[FixRange]  ,	[ChangeType]  ,	[ChangeLevel]  ,	[UpdateDate] )  values (1,1,'Strength Training Lv1',6,1,0,1,0,0,0,0,1,0,1,1,0,0,4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,23,10,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,GetDate())
INSERT INTO PS_GameDefs.dbo.Skills ([SkillID]  ,	[SkillLevel]  ,	[SkillName]  ,	[Country]  ,	[Attackfighter]  ,	[Defensefighter]  ,	[Patrolrogue]  ,[Shootrogue]  ,[Attackmage]  ,	[Defensemage]  ,	[PrevSkillID]  ,	[ReqLevel]  ,	[Grow]  ,	[SkillPoint]  ,	[TypeShow]  ,	[TypeAttack]  ,	[TypeEffect]  ,[TypeDetail]  ,	[NeedWeapon1]  ,	[NeedWeapon2]  ,	[NeedWeapon3]  ,	[NeedWeapon4]  ,	[NeedWeapon5]  ,	[NeedWeapon6]  ,[NeedWeapon7]  ,	[NeedWeapon8]  ,	[NeedWeapon9]  ,	[NeedWeapon10]  ,	[NeedWeapon11]  ,	[NeedWeapon12]  ,	[NeedWeapon13]  ,[NeedWeapon14]  ,	[NeedWeapon15]  ,	[Shield]  ,	[SP]  ,	[MP]  ,	[ReadyTime]  ,	[ResetTime]  ,	[AttackRange]  ,	[StateType]  ,	[AttrType]  ,[Disable]  ,	[SuccessType]  ,	[SuccessValue]  ,	[TargetType]  ,	[ApplyRange]  ,	[MultiAttack]  ,	[KeepTime]  ,	[Weapon1]  ,	[Weapon2]  ,[Weaponvalue]  ,	[Bag]  ,	[Arrow]  ,	[DamageType]  ,	[DamageHP]  ,	[DamageSP]  ,	[DamageMP]  ,	[TimeDamageType]  ,	[TimeDamageHP]  ,[TimeDamageSP]  ,	[TimeDamageMP]  ,	[AddDamageHP]  ,	[AddDamageSP]  ,	[AddDamageMP]  ,	[AbilityType1]  ,	[AbilityValue1]  ,[AbilityType2]  ,	[AbilityValue2]  ,	[AbilityType3]  ,	[AbilityValue3]  ,	[HealHP]  ,	[HealSP]  ,	[HealMP]  ,	[TimeHealHP]  ,[TimeHealSP]  ,	[TimeHealMP]  ,	[DefenceType]  ,	[DefenceValue]  ,	[LimitHP]  ,	[FixRange]  ,	[ChangeType]  ,	[ChangeLevel]  ,	[UpdateDate] )  values (1,2,'Strength Training Lv2',6,1,0,1,0,0,0,0,12,0,2,1,0,0,4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,23,18,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,GetDate())
INSERT INTO PS_GameDefs.dbo.Skills ([SkillID]  ,	[SkillLevel]  ,	[SkillName]  ,	[Country]  ,	[Attackfighter]  ,	[Defensefighter]  ,	[Patrolrogue]  ,[Shootrogue]  ,[Attackmage]  ,	[Defensemage]  ,	[PrevSkillID]  ,	[ReqLevel]  ,	[Grow]  ,	[SkillPoint]  ,	[TypeShow]  ,	[TypeAttack]  ,	[TypeEffect]  ,[TypeDetail]  ,	[NeedWeapon1]  ,	[NeedWeapon2]  ,	[NeedWeapon3]  ,	[NeedWeapon4]  ,	[NeedWeapon5]  ,	[NeedWeapon6]  ,[NeedWeapon7]  ,	[NeedWeapon8]  ,	[NeedWeapon9]  ,	[NeedWeapon10]  ,	[NeedWeapon11]  ,	[NeedWeapon12]  ,	[NeedWeapon13]  ,[NeedWeapon14]  ,	[NeedWeapon15]  ,	[Shield]  ,	[SP]  ,	[MP]  ,	[ReadyTime]  ,	[ResetTime]  ,	[AttackRange]  ,	[StateType]  ,	[AttrType]  ,[Disable]  ,	[SuccessType]  ,	[SuccessValue]  ,	[TargetType]  ,	[ApplyRange]  ,	[MultiAttack]  ,	[KeepTime]  ,	[Weapon1]  ,	[Weapon2]  ,[Weaponvalue]  ,	[Bag]  ,	[Arrow]  ,	[DamageType]  ,	[DamageHP]  ,	[DamageSP]  ,	[DamageMP]  ,	[TimeDamageType]  ,	[TimeDamageHP]  ,[TimeDamageSP]  ,	[TimeDamageMP]  ,	[AddDamageHP]  ,	[AddDamageSP]  ,	[AddDamageMP]  ,	[AbilityType1]  ,	[AbilityValue1]  ,[AbilityType2]  ,	[AbilityValue2]  ,	[AbilityType3]  ,	[AbilityValue3]  ,	[HealHP]  ,	[HealSP]  ,	[HealMP]  ,	[TimeHealHP]  ,[TimeHealSP]  ,	[TimeHealMP]  ,	[DefenceType]  ,	[DefenceValue]  ,	[LimitHP]  ,	[FixRange]  ,	[ChangeType]  ,	[ChangeLevel]  ,	[UpdateDate] )  values (1,3,'Strength Training Lv3',6,1,0,1,0,0,0,0,23,0,2,1,0,0,4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,23,26,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,GetDate())

But now I get this:

Code:

(0 row(s) affected)
Msg 110, Level 15, State 1, Line 1
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

The only column I can see missing in the INSERT statement is RowID which is also not given a value in the VALUES statement. However after running your script then executing ShStudio's it generated the same error 8101. I am not sure why this is happening but normally the RowID would get its values automatically.

Edit: actually after counting it out... there are 83 columns in the skills table NOT including RowID and 95 values in the VALUES statement... therefore I just discovered my own dilemma... and one I'm sure everyone else has discovered. You cant use ep6+ skills which have 95 values. in an ep5.4 database, which has 83 values per skill. There is data for more non-existing columns.. which I wish I had lol.

Made me feel like an idiot for not thinking of it sooner..but....Yay for discoveries. Can't knock a good lesson, and better to discover by doing... you remember it that way.

These extra columns seem to be new AbilityType and AbilityValue columns... which the data for these is 0 throughout the entire skill.sdata except for 3-4 unused item skills.

09/13/2019 10:53 luisdaboit#4

How did you fix it?