Hi,
sorry but I had missed a crucial point. I was running multiple INSERT statements, and in some of those I was writing an INT to the varchar field.
As a result, SQL Server "decided" that the field was really (?) an INT, and giving me the error when I tried to insert a string.
Not sure I understand why though.
set identity_insert CatalogueItemExtensionFields on
INSERT INTO CatalogueItemExtensionFields (rowid, fieldtype, description, nullable, defaultvalue, active_flag, orderpriority) VALUES
(6, 3, N'Product Group', 0, N'', 1, 6),
(7, 2, N'Minimum Order Quantity', 1, NULL, 1, 7),
(8, 3, N'Additional HIBCs', 0, 1, 1, 8)
set identity_insert CatalogueItemExtensionFields off
If I instead run the following statement, with a default value of '1' for RowID = 8, it all works fine.
INSERT INTO CatalogueItemExtensionFields (rowid, fieldtype, description, nullable, defaultvalue, active_flag, orderpriority) VALUES
(6, 3, N'Product Group', 0, N'', 1, 6),
(7, 2, N'Minimum Order Quantity', 1, NULL, 1, 7),
(8, 3, N'Additional HIBCs', 0, '1', 1, 8),
So, why does SQL Server do this?