Detect Variable error in SQL server 2008 R2

Question

Detect Variable error in SQL server 2008 R2

Navigation

#1 by (1 votes)

0

Well I have the following Insert:

INSERT INTO [PS_GameData].[dbo].[Chars] 
([ServerID], [UserID], [UserUID], [CharName], [New], [Del], [Slot], 
[Family], [Grow], [Hair], [Face], [Size], [Job], [Sex], [Level], 
[StatPoint], [SkillPoint], [Str], [Dex], [Rec], [Int], [Luc], [Wis], 
[HP], [MP], [SP], [Map], [Dir], [Exp], [Money], [PosX], [PosY], [Posz], 
[Hg], [Vg], [Cg], [Og], [Ig], [K1], [K2], [K3], [K4], [KillLevel], 
[DeadLevel], [RegDate], [DeleteDate], [JoinDate], [LeaveDate], 
[RenameCnt], [OldCharName], [RemainTime], [Staff], [LoginStatus], [GS], 
[GM], [Faction], [Pagado_1], [Pagado_2], [Pagado_3], [Pagado_4], 
[Pagado_5], [Pagado_6], [Pagado_7], [Pagado_8], [Pagado_9], [Pagado_10], 
[Pagado_11], [Pagado_12]) 
VALUES ('1', 'jocker1', '2394', '[ADM]Dificil', '1', '1', '0', '3', '2', 
'0', '0', '2', '0', '0', '60', '98', '999', '30', '12', '11', '8', '11', 
'7', '-20725', '-25106', '-24403', '42', '17', '11725422', '56396', 
'43,9692', '-0,4', '42,50782', '0', '200', '0', '0', '0', '12', '0', '0', 
'0', '0', '0', '2018-05-28 02:28:04.403', '2018-07-15 16:41:09.257', 
'2018-07-15 11:40:25.737', '2018-07-13 20:18:14.453', '0', NULL, 
'1239342308', NULL, N'0         ', NULL, NULL, NULL, '0', '0', '0', '0', 
'0', '0', '0', '0', '0', '0', '0', '0');

Whose Tabla estruptura is:

USE [PS_GameData]
GO

/****** Object:  Table [dbo].[Chars]    Script Date: 08/04/2018 14:14:49 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Chars](
    [ServerID] [tinyint] NOT NULL,
    [UserID] [varchar](255) NULL,
    [UserUID] [int] NOT NULL,
    [CharID] [int] IDENTITY(1,1) NOT NULL,
    [CharName] [varchar](30) NULL,
    [New] [tinyint] NOT NULL,
    [Del] [tinyint] NOT NULL,
    [Slot] [tinyint] NOT NULL,
    [Family] [tinyint] NOT NULL,
    [Grow] [tinyint] NOT NULL,
    [Hair] [tinyint] NOT NULL,
    [Face] [tinyint] NOT NULL,
    [Size] [tinyint] NOT NULL,
    [Job] [tinyint] NOT NULL,
    [Sex] [tinyint] NOT NULL,
    [Level] [smallint] NOT NULL,
    [StatPoint] [smallint] NOT NULL,
    [SkillPoint] [smallint] NOT NULL,
    [Str] [smallint] NOT NULL,
    [Dex] [smallint] NOT NULL,
    [Rec] [smallint] NOT NULL,
    [Int] [smallint] NOT NULL,
    [Luc] [smallint] NOT NULL,
    [Wis] [smallint] NOT NULL,
    [HP] [smallint] NOT NULL,
    [MP] [smallint] NOT NULL,
    [SP] [smallint] NOT NULL,
    [Map] [smallint] NOT NULL,
    [Dir] [smallint] NOT NULL,
    [Exp] [int] NOT NULL,
    [Money] [int] NOT NULL,
    [PosX] [real] NOT NULL,
    [PosY] [real] NOT NULL,
    [Posz] [real] NOT NULL,
    [Hg] [smallint] NOT NULL,
    [Vg] [smallint] NOT NULL,
    [Cg] [tinyint] NOT NULL,
    [Og] [tinyint] NOT NULL,
    [Ig] [tinyint] NOT NULL,
    [K1] [int] NOT NULL,
    [K2] [int] NOT NULL,
    [K3] [int] NOT NULL,
    [K4] [int] NOT NULL,
    [KillLevel] [tinyint] NOT NULL,
    [DeadLevel] [tinyint] NOT NULL,
    [RegDate] [datetime] NOT NULL,
    [DeleteDate] [datetime] NULL,
    [JoinDate] [datetime] NULL,
    [LeaveDate] [datetime] NULL,
    [RenameCnt] [tinyint] NOT NULL,
    [OldCharName] [varchar](30) NULL,
    [RemainTime] [int] NOT NULL,
    [Staff] [smallint] NULL,
    [LoginStatus] [nchar](10) NULL,
    [GS] [int] NULL,
    [GM] [int] NULL,
    [Faction] [int] NULL,
    [Pagado_1] [int] NULL,
    [Pagado_2] [int] NULL,
    [Pagado_3] [int] NULL,
    [Pagado_4] [int] NULL,
    [Pagado_5] [int] NULL,
    [Pagado_6] [int] NULL,
    [Pagado_7] [int] NULL,
    [Pagado_8] [int] NULL,
    [Pagado_9] [int] NULL,
    [Pagado_10] [int] NULL,
    [Pagado_11] [int] NULL,
    [Pagado_12] [int] NULL,
 CONSTRAINT [PK_Chars] PRIMARY KEY CLUSTERED 
(
    [CharID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_New]  DEFAULT ((1)) FOR [New]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_Del]  DEFAULT ((0)) FOR [Del]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_level]  DEFAULT ((0)) FOR [Level]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_StatPoint]  DEFAULT ((0)) FOR [StatPoint]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_SkillPoint]  DEFAULT ((0)) FOR [SkillPoint]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_map]  DEFAULT ((0)) FOR [Map]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_dir]  DEFAULT ((0)) FOR [Dir]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_exp]  DEFAULT ((0)) FOR [Exp]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_money]  DEFAULT ((0)) FOR [Money]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_posx]  DEFAULT ((674.442)) FOR [PosX]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_posy]  DEFAULT ((3.640)) FOR [PosY]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_posz]  DEFAULT ((1000.924)) FOR [Posz]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_K1]  DEFAULT ((0)) FOR [K1]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_K2]  DEFAULT ((0)) FOR [K2]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_K3]  DEFAULT ((0)) FOR [K3]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_K4]  DEFAULT ((0)) FOR [K4]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_KillLevel]  DEFAULT ((0)) FOR [KillLevel]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_DeadLevel]  DEFAULT ((0)) FOR [DeadLevel]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_RegDate]  DEFAULT (getdate()) FOR [RegDate]
GO

ALTER TABLE [dbo].[Chars] ADD  CONSTRAINT [DF_Chars_RemainTime]  DEFAULT ((0)) FOR [RemainTime]
GO

ALTER TABLE [dbo].[Chars] ADD  DEFAULT ((0)) FOR [Pagado_1]
GO

ALTER TABLE [dbo].[Chars] ADD  DEFAULT ((0)) FOR [Pagado_2]
GO

ALTER TABLE [dbo].[Chars] ADD  DEFAULT ((0)) FOR [Pagado_3]
GO

ALTER TABLE [dbo].[Chars] ADD  DEFAULT ((0)) FOR [Pagado_4]
GO

ALTER TABLE [dbo].[Chars] ADD  DEFAULT ((0)) FOR [Pagado_5]
GO

ALTER TABLE [dbo].[Chars] ADD  DEFAULT ((0)) FOR [Pagado_6]
GO

ALTER TABLE [dbo].[Chars] ADD  DEFAULT ((0)) FOR [Pagado_7]
GO

ALTER TABLE [dbo].[Chars] ADD  DEFAULT ((0)) FOR [Pagado_8]
GO

ALTER TABLE [dbo].[Chars] ADD  DEFAULT ((0)) FOR [Pagado_9]
GO

ALTER TABLE [dbo].[Chars] ADD  DEFAULT ((0)) FOR [Pagado_10]
GO

ALTER TABLE [dbo].[Chars] ADD  DEFAULT ((0)) FOR [Pagado_11]
GO

ALTER TABLE [dbo].[Chars] ADD  DEFAULT ((0)) FOR [Pagado_12]
GO

My problem when running the query I get the following error:

Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to real.

I know that the PosX,PosY,PosZ are values (Real) according to what I read must be a trigger or procedure that has PosX,PosY,PosZ as value ( Varchar ) and the table has no triggers ( TRIGGERS ) while procedures (STORED PROCEDURES) has THOUSANDS, As the error reads it DOES NOT SAY THAT PROCEDURE has the error. My question is

How can I detect this error in the DB to change it to its (real) value that it should have?

sql sql-server

asked by Juan Carlos Villamizar Alvarez 04.08.2018 в 20:30

source

1 answer

collect firebase data by means of equals pass several php arrays to javascript

score 1 · Answer 1

The insert that you put for example has all the values of type VARCHAR (set the quotes in the numeric values for example '0' instead of 0). This means that when you try to insert a VARCHAR value in a field defined as REAL, an implicit CAST operation is performed.

This operation rejects the values with comma of the type 'x, x' having to put the decimal values using the point 'x.x'

Test to change values with comma:

'43,9692', '-0,4', '42,50782'

By points

'43.9692', '-0.4', '42.50782'

And it should work. You can also use the function

REPLACE('43.9692', ',', '.')

in case the variable varchar already arrives to you.

Note : If you use SQL Server 2012 onwards, you have the PARSE statement that allows you to indicate the language and make it easier (See example here link ) but well, as the title says in a 2008 R2, there is no other than those mentioned.

I hope you get the answer, Greetings