One of our clients in Russia is reporting this error: "Column name or number of supplied values does not match table definition." This is happening on an INSERT command being sent to SQL Server. When we look at the command in SQL Server Profiler we see numeric fields having a comma in place of the decimal point. This is a parameterized INSERT query being sent using ADO. Our application is in Delphi, but the language should be irrelevant. The Delphi ADO components are direct interfaces to the native ADO objects - basically just wrappers calling the corresponding methods in the ADO interface.
Our actual insert has over 200 columns. For the sake of clarity in this post, I've pared it down to 4 columns. The table has this schema:
CREATE TABLE [dbo].[equip](
[ac_main_mccb_no] [varchar](50) NULL,
[ac_sub_mccb_no] [varchar](50) NULL,
[design_pressure] [numeric](9, 4) NULL,
[design_temp] [numeric](4, 0) NULL)
The INSERT statement looks like this:
INSERT INTO equip
(ac_main_mccb_no,ac_sub_mccb_no,design_pressure,design_temp)
VALUES (:ac_main_mccb_no,:ac_sub_mccb_no,:design_pressure,:design_temp)
When the client looks at the insert
in SQL Server Profiler they see this in the trace:
exec sp_executesql N'INSERT INTO equip
(ac_main_mccb_no,ac_sub_mccb_no,design_pressure,design_temp)
VALUES (@P1,@P2,@P3,@P4)',
N'@P1 varchar(50),@P2 varchar(50),@P3 float,@P4 float',
NULL,NULL,3,5,380
The value we're passing for design_pressure is 3.5 but is showing up in the trace as "3,5". This is happening in all their float parameters. The SQL shown here is clearly wrong, but we don't produce this SQL. This is just what the trace in SQL Server Profiler is showing.
I imagine this is somehow related to the fact that in Russia the decimal separator is the comma, but our code is not formatting these strings. They're just parameters passed as floating point values to the ADO interface. I believe this is borne out by the parameter datatype being float, and the lack of quote characters around the field, as string parameters have.
This worked before the customer made some change. They're blaming an upgrade of our software, but I believe this is very unlikely, since we have been using the same ADO query for this insert since 2013. We suspect some change in their database configuration but we don't know where to look.
From the viewpoint of an application developer, the path from client application ADO component to SQL Server instance on the server is a "black box." It appears that somewhere along that path our parameters are being corrupted. Can someone with expertise in this area can give us a clue where to look for anything that could cause this behavior?
The customer said they tried changing the Windows setting for decimal separator to the period, but it made no difference. I've also tried setting my decimal separator to comma in an attempt to duplicate their issue. It had no effect on my machine either.
What I have tried:
The customer said they tried changing the Windows setting for decimal separator to the period, but it made no difference. I've also tried setting my decimal separator to comma in an attempt to duplicate their issue. It had no effect on my machine either.