Why is it that NULL value will be automatically embedded in square brackets in insertion script?

  • Hello all,

    I have encountered following problem lately:

    when an insertion script is done via

    INSERT INTO myTable (col1,col2,col3) VALUES ('TEST1','TEST2',NULL)

    the value NULL will be somehow automatically embedded in square brackets thus making the statement invalid.

    In this case the insert script looks like this and thus fails:

    INSERT INTO myTable (col1,col2,col3) VALUES ('TEST1','TEST2',[NULL])

    What we have is actually a situation where the values are provided from "outside" (a CSV flat file) and there are placeholders in the insertion script, so that the script looks actually sth. like this:

    INSERT INTO myTable (col1,col2,col3) VALUES (@Placeholder1,@Placeholder2,@Placeholder3)

    col3 where @Placeholder3 value should be written is a nullable varchar(254) field.

    I have made sure that value in @Placeholder3 is indeed a NULL value (=there is no process in between that would embed the NULL value in square brackets before insert script is executed)

    And now the part that is really confusing: when the script is run on one particular SQL server (2014) with exactly the same CSV file it would run without issues (NULL will not be embedded in square brackets)?! On other SQL Server (also 2014) it would fail due to described embedding.

    So it boils down to the fact that there must be(?) some kind of setting or configuration on SQL Server instance that does this embedding of NULL?

    Has anybody encountered this before and can point me in the right direction?

    Many thanks for any hints,

    Marin

  • marin-231997 (2/22/2016)


    Hello all,

    I have encountered following problem lately:

    when an insertion script is done via

    INSERT INTO myTable (col1,col2,col3) VALUES ('TEST1','TEST2',NULL)

    the value NULL will be somehow automatically embedded in square brackets thus making the statement invalid.

    In this case the insert script looks like this and thus fails:

    INSERT INTO myTable (col1,col2,col3) VALUES ('TEST1','TEST2',[NULL])

    What we have is actually a situation where the values are provided from "outside" (a CSV flat file) and there are placeholders in the insertion script, so that the script looks actually sth. like this:

    INSERT INTO myTable (col1,col2,col3) VALUES (@Placeholder1,@Placeholder2,@Placeholder3)

    col3 where @Placeholder3 value should be written is a nullable varchar(254) field.

    I have made sure that value in @Placeholder3 is indeed a NULL value (=there is no process in between that would embed the NULL value in square brackets before insert script is executed)

    And now the part that is really confusing: when the script is run on one particular SQL server (2014) with exactly the same CSV file it would run without issues (NULL will not be embedded in square brackets)?! On other SQL Server (also 2014) it would fail due to described embedding.

    So it boils down to the fact that there must be(?) some kind of setting or configuration on SQL Server instance that does this embedding of NULL?

    Has anybody encountered this before and can point me in the right direction?

    Many thanks for any hints,

    Marin

    Where is this insert statement being generated from?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • marin-231997 (2/22/2016)


    Hello all,

    I have encountered following problem lately:

    when an insertion script is done via

    INSERT INTO myTable (col1,col2,col3) VALUES ('TEST1','TEST2',NULL)

    the value NULL will be somehow automatically embedded in square brackets thus making the statement invalid.

    Marin

    Help us out here. How do you create the insertion script? Do you just write it? When is it being added square braquets? After the code is written? Or when executing something? This might be a 3rd party issue.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Hello Sean,

    many thanks for the quick reply.

    It is a 3rd party software (interface engine) that processes the CSV file and sets the value in the placeholders before running the script.

    Script is static (not created on-the-fly), it is just the placeholder values that are replaced on the fly (one insertion per row from CSV file).

    It is really mysterious that it works perfectly on one SQL server and not on the other...

    Many thanks,

    Marin

  • marin-231997 (2/22/2016)


    Hello Sean,

    many thanks for the quick reply.

    It is a 3rd party software (interface engine) that processes the CSV file and sets the value in the placeholders before running the script.

    Script is static (not created on-the-fly), it is just the placeholder values that are replaced on the fly (one insertion per row from CSV file).

    It is really mysterious that it works perfectly on one SQL server and not on the other...

    Many thanks,

    Marin

    Does not work for me on 2012. Eg

    declare @x varchar(5);

    set @x = [null];

    Gives me an Invalid column name 'null' error.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • marin-231997 (2/22/2016)


    Hello Sean,

    many thanks for the quick reply.

    It is a 3rd party software (interface engine) that processes the CSV file and sets the value in the placeholders before running the script.

    Script is static (not created on-the-fly), it is just the placeholder values that are replaced on the fly (one insertion per row from CSV file).

    It is really mysterious that it works perfectly on one SQL server and not on the other...

    Many thanks,

    Marin

    I would ask the vendor. There is little to no chance anybody can tell you how some unnamed 3rd party software generates insert statements and why it might vary between versions of sql server. The only people who can answer that are the people that wrote the application.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • But it seems that you are suggesting that a different version of the script is generated depending on the SQL Server version.

    To troubleshoot this, you need to be speaking to the third-party software vendors, as it does not appear to be a SQL Server issue.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Hello all,

    many thanks for the quick replies, much appreciated.

    The confusing part is that it is apparently the value that replaces the placeholder is the cause... The SQL servers are on the same version (SQL Server 2014) sorry if this was not clear enough. Same server version, two different instances, on one works OK, on the other one NULLs will be embedded in square brackets...

    The script is static - nothing in the script (body) itself is changed - it is always exactly the same script that is executed.

    It is only the value that is replacing the placeholder on-the-fly that is different (can be NULL or not NULL).

    Anyway, I also asked the vendor for hints, but wanted to check if something similar was already known in SQL server scope, just to narrow down the problem.

    To me it seems as if it might be some kind of config/setting (NULL handling on insertion?), but I might be wrong...

    I will definitely post here what the vendor might come up with.

    Thanks again,

    Marin

  • Hello all,

    many thanks again for the responses.

    The error was indeed caused in the vendor software and now has been resolved internally - nothing related to embedded NULLs.

    Thanks,

    Marin

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply