|
You last visited: Today at 12:08
Advertisement
[Help] Error inserting data into skills table (shstudio 7.5.5)
Discussion on [Help] Error inserting data into skills table (shstudio 7.5.5) within the Shaiya Private Server forum part of the Shaiya category.
01/16/2015, 10:44
|
#1
|
elite*gold: 0 
Join Date: Jul 2010
Posts: 46
Received Thanks: 12
|
[Help] Error inserting data into skills table (shstudio 7.5.5)
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
|
#2
|
elite*gold: 0
Join Date: Apr 2014
Posts: 300
Received Thanks: 472
|
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
|
#3
|
elite*gold: 0
Join Date: Jul 2010
Posts: 46
Received Thanks: 12
|
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
|
#4
|
elite*gold: 0
Join Date: May 2011
Posts: 18
Received Thanks: 1
|
How did you fix it?
|
|
|
|
 |
Similar Threads
|
Error: Login Data load error, Try again
03/28/2023 - Shaiya Private Server - 2 Replies
Hello Epvpers, Today i was setting up and started our new server after reinstalling everything accept Windows on the VPS.
After everything was done, i started the services. Everything was fine untill we started the client. We contain a Error "Login data load error, Try again."
I tryed to see if the password got messed up or if i hade missed something within it but nothing. Im totaly out of ideas so i reach for some help out there. Anyone got those problems before and succesed to fix it?
...
|
Schedule data inserting, shardmanager.
11/12/2013 - SRO Private Server - 2 Replies
Hello, anyone knows this problem?
When i turn fortresswar off from smc schedule the errors are gone, when i add them again its back..
Help would be great!!
Has to do with fortresswar i guess!
http://i.imgur.com/4ec3OjY.png
|
[Help]Error DB-Table monarch
02/07/2013 - Metin2 Private Server - 0 Replies
How do I fix this error?
SYSERR: Feb 4 16:21:10 :: DirectQuery: AsyncSQL::DirectQuery : mysql_query error: Column 'name' in field list is ambiguous
query: SELECT empire, pid, name, money, windate FROM monarch a, player b where a.pid=b.id
Help me please
|
[Request] Default character data user_character table
10/08/2009 - Dekaron Private Server - 5 Replies
hehehe, i was cleaning abit too much data today :mofo:
Can someone post a tsql-txt dump of the default chars in user_char ?
thanks alot! :handsdown:
|
table error.and other things
06/24/2009 - EO PServer Hosting - 2 Replies
im having trouble with my cq_rbn_rqr im getting this error and idk what to do.
cq_eudemon_rbn_rqr does not have a primary key.
updates to this table with be done using the following pseudo statement:
Update cq_eudemon_rbn_rqr SET modifiedFieldsandValues WHERE AllFieldsandOldValues LIMIT 1.
if u can help post here or add me on msn at [email protected] and help me if u can with other problems and many questions i have lol.
|
All times are GMT +2. The time now is 12:08.
|
|
![[Help] Error inserting data into skills table (shstudio 7.5.5)](https://www.elitepvpers.com/forum/iconimages/shaiya-private-server/help-error-inserting-data-into-skills-table-shstudio-7-5-5-a_ltr.gif){kind=small}
