1

We are seeing a very strange problem when populating a field in a DB via a SQL Server DB project publish action.

This is the table definition

CREATE TABLE [dbo].[CatalogueItemExtensionFields] 
(
    [RowID] tinyint identity not null,
    [FieldType] tinyint not null,
    [Description] varchar(120) not null,
    [Nullable] bit not null,
    [DefaultValue] varchar(100) null,
    [Active_Flag] bit null,
    [OrderPriority] tinyint not null,
    [ContextGuid] uniqueidentifier not null
);

This is the population script

set identity_insert CatalogueItemExtensionFields on

INSERT INTO CatalogueItemExtensionFields (rowid, fieldtype, description, nullable, defaultvalue, active_flag, orderpriority) 
VALUES (dbo.ConstantProductGroupRowId(), 3, 'Product Group', 0, '', 1, dbo.ConstantProductGroupRowId()),

set identity_insert CatalogueItemExtensionFields off

If I run the INSERT script manually all works fine. When I run it as part of the DB project publish, it inserts "0".

I have looked at the publish.sql script that is generated, and all looks fine.

BTW, the only similar post I have found is this, but it does not apply to our case because the field we are inserting into is defined as varchar.

This is driving us mad. Any ideas?

Improve this question
4
  • Inserts 0 where? There are 8 columns there. I believe these scripts are run in SQLCMD mode. So set SSMS to SQLCMD mode and see if it does the same thing (inserts '0'). What does it do when it works properly? Is dbo.ConstantProductGroupRowId() a user defined scalar function? What does it look like? Commented Jan 29, 2015 at 12:20
  • Just to clarify, I am trying to insert the empty string into the "defaultvalue" column. Commented Jan 29, 2015 at 13:16
  • @NickMcDermaid, we just tried running the publish script in SQLCMD mode and got this error when trying to write a valid string: 'Msg 245, Level 16, State 1, Line 7 Conversion failed when converting the varchar value 'test' to data type int.' So this means that the SQLCMD thinks the field is an INT, but that is not how it appears in the table definition. Is there any obscure DB setting used in SQLCMD mode? Commented Jan 29, 2015 at 13:17
  • I don't see the word 'test' in your sample script. I also see an additonal unneeded trailing comma in your VALUES clause. This is all very confusing. Do you have any triggers on this table? Is this the only script you are running? What does dbo.ConstantProductGroupRowId() do? Does it return a string or a number? Commented Jan 29, 2015 at 22:10

3 Answers 3

Reset to default
1

We at our company finally found out that if you use SQLCMD / DBProj then it is super import to ENABLE Quoted Identifiers. Or else installer changes inputs in the exactly same way as @maurocam explained. If you enable this, then it works same as in SQL Management Studio for example.

To enable it:

  • SQLCMD
    • just use parameter -I (capital is important here, small is for file).
    • Example sqlcmd -S localhost -d DBNAME -U User -P Password -i path/to/sql/file.sql -I
  • SQL Itself
    • SET QUOTED_IDENTIFIER { ON | OFF }
  • DBProj
    • can be set at the project or object (proc, func, ...) level. Just click on a proj/file -> Properties and check if QUOTED_IDENTIFIER is enabled there.
    • For schema compare it can be set via "Ignore quoted identifiers"

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-quoted-identifier-transact-sql?view=sql-server-ver16

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

APOLOGIES - MY MISTAKE!!! (But a very useful one to document)

I have summarised again below (also to make it clearer respect to my initial post)

TABLE DEFINITION

CREATE TABLE [dbo].[CatalogueItemExtensionFields] 
(
[RowID] tinyint identity not null,
[FieldType] tinyint not null,
[Description] varchar(120) not null,
[Nullable] bit not null,
[DefaultValue] varchar(100) null,
[Active_Flag] bit null,
[OrderPriority] tinyint not null
);

INSERT STATEMENT

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),
(9, 3, N'Additional GTINs', 0, N'', 1, 9)

set identity_insert CatalogueItemExtensionFields off

Because I am inserting multiple rows, when SQL parses the statement it see I am trying to insert a numeric defaultvalue = 1 for RowID = 8. As a result, even though the column is defined as a varchar, SQL decides that the INSERT statement is inserting INTs. So the empty string values (for RowIDs 7 and 9) are converted to zero. I referred to a post I had found relating to actual INT column, which results in the same behaviour.

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),
(9, 3, N'Additional GTINs', 0, N'', 1, 9)

So, now the question is, why does SQL server ignore the column type definition and instead decides the type from the value in my INSERT statement?

Improve this answer

Comments

0

Answer from SqlServerCentral

"empty string converts to an int without an error, and it's value is zero. it has to do with the precidence of implicit conversions"

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.