February 22, 2016 at 9:04 am
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
February 22, 2016 at 9:19 am
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/
February 22, 2016 at 9:22 am
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.
February 22, 2016 at 9:24 am
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
February 22, 2016 at 9:28 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 22, 2016 at 9:31 am
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/
February 22, 2016 at 9:31 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 22, 2016 at 9:49 am
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
February 23, 2016 at 4:28 am
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