TinyTds::Error: Error converting data type varchar to numeric.

[no1-knows]

TinyTds::Error: Error converting data type varchar to numeric.

[aidanharan]

The query that is throwing the error is:

SELECT CAST('' AS numeric(18,0)) AS value

It appears the default value of a column is an empty string but the column type is a number, which doesn't make sense. If you run the following query then you might be able to work out which column is causing this issue and what the fix might be.

EXEC sp_executesql N'SELECT o.name AS [table_name], c.name AS [name], t.name AS [type], d.definition AS [default_value], CASE WHEN t.name IN (''decimal'', ''bigint'', ''int'', ''money'', ''numeric'', ''smallint'', ''smallmoney'', ''tinyint'') THEN c.scale END AS [numeric_scale], CASE WHEN t.name IN (''decimal'', ''bigint'', ''int'', ''money'', ''numeric'', ''smallint'', ''smallmoney'', ''tinyint'', ''real'', ''float'') THEN c.precision END AS [numeric_precision], CASE WHEN t.name IN (''date'', ''datetime'', ''datetime2'', ''datetimeoffset'', ''smalldatetime'', ''time'') THEN c.scale END AS [datetime_precision], c.collation_name AS [collation], ROW_NUMBER() OVER (ORDER BY c.column_id) AS [ordinal_position], CASE WHEN t.name IN (''nchar'', ''nvarchar'') AND c.max_length > 0 THEN c.max_length / 2 ELSE c.max_length END AS [length], CASE c.is_nullable WHEN 1 THEN 1 END AS [is_nullable], CASE WHEN ic.object_id IS NOT NULL THEN 1 END AS [is_primary], c.is_identity AS [is_identity] FROM .sys.columns c INNER JOIN .sys.objects o ON c.object_id = o.object_id INNER JOIN .sys.schemas s ON o.schema_id = s.schema_id INNER JOIN .sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id LEFT OUTER JOIN .sys.default_constraints d ON c.object_id = d.parent_object_id AND c.default_object_id = d.object_id LEFT OUTER JOIN .sys.key_constraints k ON c.object_id = k.parent_object_id AND k.type = ''PK'' LEFT OUTER JOIN .sys.index_columns ic ON k.parent_object_id = ic.object_id AND k.unique_index_id = ic.index_id AND c.column_id = ic.column_id WHERE o.name = @0 AND s.name = schema_name() ORDER BY c.column_id', N'@0 nvarchar(128)', @0 = N'WB_MSS'

[aidanharan]

@no1-knows, Did the issue only start with v7.0.4.0? Does v7.0.3.0 have the same issue? Would you be able to create a script so that I can reproduce the issue? Thanks

[no1-knows]

@aidanharan v7.0.2.0 had same issue. not tried v7.0.3.0.
I was developing sqlserver in docker which is no problem.
Now switch to production server which is not docker and have problem only this model.
I'm not sure if I can create a script because we're short on time before the deadline...

[aidanharan]

You need to find the SQL that is actually being sent to your database as it looks like the issue is with the generated SQL.

If you open a Rails console and run WbMss.all.count then you should see the SQL. You might need to change your logging level to see it. If that doesn't work then run following from your Rails console.

ActiveSupport::Notifications.subscribe('sql.active_record') do |_name, _start, _finish, _id, payload|
   puts payload[:sql]
end
WbMss.all.count

[no1-knows]

Thanks for your info!
I'm newbie but I understand a bit.

The flow to execute SQL is like follows:

1. Rails access DB server and get table and columns info
2. Rails create sql based on 1st info
3. Error occur at WbMss

This means 1st info might be not correct.
That's why u said this.

it looks like the issue is with the generated SQL.

So I have 2 approaches

1. Use to_sql and exec_query

sql = "SELECT [WB_MSS].* FROM [WB_MSS]"
result = ActiveRecord::Base.connection.exec_query(sql)

2. Fix SQL Server setting

Is this correct?

ActiveSupport::Notifications.subscribe('sql.active_record') do |_name, _start, _finish, _id, payload|
   puts payload[:sql]
end
WbMss.all.count

DBCC USEROPTIONS WITH NO_INFOMSGS
USE [SKWEB]
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WITH (NOLOCK) WHERE TABLE_CATALOG = DB_NAME() AND TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'WB_MSS' AND TABLE_TYPE = N'VIEW' ORDER BY TABLE_NAME
EXEC sp_executesql N'SELECT o.name AS [table_name], c.name AS [name], t.name AS [type], d.definition AS [default_value], CASE WHEN t.name IN (''decimal'', ''bigint'', ''int'', ''money'', ''numeric'', ''smallint'', ''smallmoney'', ''tinyint'') THEN c.scale END AS [numeric_scale], CASE WHEN t.name IN (''decimal'', ''bigint'', ''int'', ''money'', ''numeric'', ''smallint'', ''smallmoney'', ''tinyint'', ''real'', ''float'') THEN c.precision END AS [numeric_precision], CASE WHEN t.name IN (''date'', ''datetime'', ''datetime2'', ''datetimeoffset'', ''smalldatetime'', ''time'') THEN c.scale END AS [datetime_precision], c.collation_name AS [collation], ROW_NUMBER() OVER (ORDER BY c.column_id) AS [ordinal_position], CASE WHEN t.name IN (''nchar'', ''nvarchar'') AND c.max_length > 0 THEN c.max_length / 2 ELSE c.max_length END AS [length], CASE c.is_nullable WHEN 1 THEN 1 END AS [is_nullable], CASE WHEN ic.object_id IS NOT NULL THEN 1 END AS [is_primary], c.is_identity AS [is_identity] FROM .sys.columns c INNER JOIN .sys.objects o ON c.object_id = o.object_id INNER JOIN .sys.schemas s ON o.schema_id = s.schema_id INNER JOIN .sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id LEFT OUTER JOIN .sys.default_constraints d ON c.object_id = d.parent_object_id AND c.default_object_id = d.object_id LEFT OUTER JOIN .sys.key_constraints k ON c.object_id = k.parent_object_id AND k.type = ''PK'' LEFT OUTER JOIN .sys.index_columns ic ON k.parent_object_id = ic.object_id AND k.unique_index_id = ic.index_id AND c.column_id = ic.column_id WHERE o.name = @0 AND s.name = schema_name() ORDER BY c.column_id', N'@0 nvarchar(128)', @0 = N'WB_MSS'
SELECT CAST('' AS char(2)) AS value
SELECT CAST('' AS char(1)) AS value
SELECT CAST('' AS varchar(10)) AS value
SELECT CAST('' AS varchar(6)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(24)) AS value
SELECT CAST('' AS char(8)) AS value
SELECT CAST('' AS char(9)) AS value
SELECT CAST('' AS char(12)) AS value
SELECT CAST('' AS numeric(18,0)) AS value
/usr/local/bundle/gems/activerecord-sqlserver-adapter-7.0.4.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:454:in `each': TinyTds::Error: Error converting data type varchar to numeric. (ActiveRecord::StatementInvalid)
/usr/local/bundle/gems/activerecord-sqlserver-adapter-7.0.4.0/lib/active_record/connection_adapters/sqlserver/database_statements.rb:454:in `each': Error converting data type varchar to numeric. (TinyTds::Error)

ActiveSupport::Notifications.subscribe('sql.active_record') do |_name, _start, _finish, _id, payload|
   puts payload[:sql]
end
WbFzi.all.count


SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WITH (NOLOCK) WHERE TABLE_CATALOG = DB_NAME() AND TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'WB_FZI' AND TABLE_TYPE = N'VIEW' ORDER BY TABLE_NAME
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WITH (NOLOCK) WHERE TABLE_CATALOG = DB_NAME() AND TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'WB_FZI' AND TABLE_TYPE = N'VIEW' ORDER BY TABLE_NAME
EXEC sp_executesql N'SELECT o.name AS [table_name], c.name AS [name], t.name AS [type], d.definition AS [default_value], CASE WHEN t.name IN (''decimal'', ''bigint'', ''int'', ''money'', ''numeric'', ''smallint'', ''smallmoney'', ''tinyint'') THEN c.scale END AS [numeric_scale], CASE WHEN t.name IN (''decimal'', ''bigint'', ''int'', ''money'', ''numeric'', ''smallint'', ''smallmoney'', ''tinyint'', ''real'', ''float'') THEN c.precision END AS [numeric_precision], CASE WHEN t.name IN (''date'', ''datetime'', ''datetime2'', ''datetimeoffset'', ''smalldatetime'', ''time'') THEN c.scale END AS [datetime_precision], c.collation_name AS [collation], ROW_NUMBER() OVER (ORDER BY c.column_id) AS [ordinal_position], CASE WHEN t.name IN (''nchar'', ''nvarchar'') AND c.max_length > 0 THEN c.max_length / 2 ELSE c.max_length END AS [length], CASE c.is_nullable WHEN 1 THEN 1 END AS [is_nullable], CASE WHEN ic.object_id IS NOT NULL THEN 1 END AS [is_primary], c.is_identity AS [is_identity] FROM .sys.columns c INNER JOIN .sys.objects o ON c.object_id = o.object_id INNER JOIN .sys.schemas s ON o.schema_id = s.schema_id INNER JOIN .sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id LEFT OUTER JOIN .sys.default_constraints d ON c.object_id = d.parent_object_id AND c.default_object_id = d.object_id LEFT OUTER JOIN .sys.key_constraints k ON c.object_id = k.parent_object_id AND k.type = ''PK'' LEFT OUTER JOIN .sys.index_columns ic ON k.parent_object_id = ic.object_id AND k.unique_index_id = ic.index_id AND c.column_id = ic.column_id WHERE o.name = @0 AND s.name = schema_name() ORDER BY c.column_id', N'@0 nvarchar(128)', @0 = N'WB_FZI'
EXEC sp_executesql N'SELECT o.name AS [table_name], c.name AS [name], t.name AS [type], d.definition AS [default_value], CASE WHEN t.name IN (''decimal'', ''bigint'', ''int'', ''money'', ''numeric'', ''smallint'', ''smallmoney'', ''tinyint'') THEN c.scale END AS [numeric_scale], CASE WHEN t.name IN (''decimal'', ''bigint'', ''int'', ''money'', ''numeric'', ''smallint'', ''smallmoney'', ''tinyint'', ''real'', ''float'') THEN c.precision END AS [numeric_precision], CASE WHEN t.name IN (''date'', ''datetime'', ''datetime2'', ''datetimeoffset'', ''smalldatetime'', ''time'') THEN c.scale END AS [datetime_precision], c.collation_name AS [collation], ROW_NUMBER() OVER (ORDER BY c.column_id) AS [ordinal_position], CASE WHEN t.name IN (''nchar'', ''nvarchar'') AND c.max_length > 0 THEN c.max_length / 2 ELSE c.max_length END AS [length], CASE c.is_nullable WHEN 1 THEN 1 END AS [is_nullable], CASE WHEN ic.object_id IS NOT NULL THEN 1 END AS [is_primary], c.is_identity AS [is_identity] FROM .sys.columns c INNER JOIN .sys.objects o ON c.object_id = o.object_id INNER JOIN .sys.schemas s ON o.schema_id = s.schema_id INNER JOIN .sys.types t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id LEFT OUTER JOIN .sys.default_constraints d ON c.object_id = d.parent_object_id AND c.default_object_id = d.object_id LEFT OUTER JOIN .sys.key_constraints k ON c.object_id = k.parent_object_id AND k.type = ''PK'' LEFT OUTER JOIN .sys.index_columns ic ON k.parent_object_id = ic.object_id AND k.unique_index_id = ic.index_id AND c.column_id = ic.column_id WHERE o.name = @0 AND s.name = schema_name() ORDER BY c.column_id', N'@0 nvarchar(128)', @0 = N'WB_FZI'
SELECT CAST('' AS char(2)) AS value
SELECT CAST('' AS char(2)) AS value
SELECT CAST('' AS char(1)) AS value
SELECT CAST('' AS char(1)) AS value
SELECT CAST('' AS varchar(10)) AS value
SELECT CAST('' AS varchar(10)) AS value
SELECT CAST('' AS varchar(6)) AS value
SELECT CAST('' AS varchar(6)) AS value
SELECT CAST('' AS char(12)) AS value
SELECT CAST('' AS char(12)) AS value
SELECT CAST('' AS char(3)) AS value
SELECT CAST('' AS char(3)) AS value
SELECT CAST((0) AS smallint) AS value
SELECT CAST((0) AS smallint) AS value
SELECT CAST((0) AS numeric(9,2)) AS value
SELECT CAST((0) AS numeric(9,2)) AS value
SELECT CAST('' AS char(12)) AS value
SELECT CAST('' AS char(12)) AS value
SELECT CAST('' AS char(8)) AS value
SELECT CAST('' AS char(8)) AS value
SELECT CAST('' AS char(2)) AS value
SELECT CAST('' AS char(2)) AS value
SELECT CAST('' AS varchar(12)) AS value
SELECT CAST('' AS varchar(12)) AS value
SELECT CAST('' AS varchar(8)) AS value
SELECT CAST('' AS varchar(8)) AS value
SELECT CAST('' AS varchar(10)) AS value
SELECT CAST('' AS varchar(10)) AS value
SELECT CAST('' AS varchar(4)) AS value
SELECT CAST('' AS varchar(4)) AS value
SELECT CAST('' AS char(2)) AS value
SELECT CAST('' AS char(2)) AS value
SELECT CAST('' AS varchar(10)) AS value
SELECT CAST('' AS varchar(10)) AS value
SELECT CAST('' AS varchar(8)) AS value
SELECT CAST('' AS varchar(8)) AS value
SELECT CAST((0) AS numeric(6,3)) AS value
SELECT CAST((0) AS numeric(6,3)) AS value
SELECT CAST((0) AS numeric(5,1)) AS value
SELECT CAST((0) AS numeric(5,1)) AS value
SELECT CAST('' AS char(5)) AS value
SELECT CAST('' AS char(5)) AS value
SELECT CAST('' AS varchar(18)) AS value
SELECT CAST('' AS varchar(18)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS char(5)) AS value
SELECT CAST('' AS char(5)) AS value
SELECT CAST('' AS varchar(18)) AS value
SELECT CAST('' AS varchar(18)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS char(3)) AS value
SELECT CAST('' AS char(3)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(24)) AS value
SELECT CAST('' AS varchar(24)) AS value
SELECT CAST('' AS varchar(14)) AS value
SELECT CAST('' AS varchar(14)) AS value
SELECT CAST('' AS varchar(14)) AS value
SELECT CAST('' AS varchar(14)) AS value
SELECT CAST('' AS varchar(14)) AS value
SELECT CAST('' AS varchar(14)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS char(7)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('00000' AS char(5)) AS value
SELECT CAST('00000' AS char(5)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(16)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT CAST('' AS varchar(40)) AS value
SELECT COUNT(*) FROM (SELECT 1 AS one FROM [WB_FZI]) subquery_for_count
SELECT COUNT(*) FROM (SELECT 1 AS one FROM [WB_FZI]) subquery_for_count
=> 2174

[no1-knows]

DB column was wrong which rails don't create.
Thanks for your support!!!