Data Warehouse Store Procedure - INSERT failed as DateID can't be null -

[esobrino]

I am getting a strange behaviour that in the execution of an SP trying to INSERT values and it fails stating that the column don't allow nulls.  I am 100% sure that its value is good and not-null and even added the test on the IF statement and to not insert null-values...  and in top of it added a SELECT statement showing that the value is not null and still fail for that reason.   Take a look and let me know your thoughts.  Here is the fragment that fails with the statement "DateID" don't allow NULL values:

      -- insert data if needed
      SET @dateid = @syear+@smonth+@sday

      select @yd,        @smyear,     @syear,     @smonth,
             year(@yd),  month(@yd),  @sday,      @syear+'-'+@smonth+'-'+@sday,             
             day(@yd),   @i,          @wd,        datepart(ww,@yd), 
             @dalias,    @qua,        @Tri,       @daytype, 
             @mw,        @mn,         @dcount,    @dateid

      IF NOT EXISTS(SELECT * FROM Chronology.DateDetail
                     WHERE Date = @yd) AND @dateid is not null
         INSERT INTO Chronology.DateDetail
            ([Date],    MonthAlias,  YearText,    MonthText,
             [Year],    [Month],     DayText,     DateText,
             [Day],     DayOfMonth,  WeekDay,     YearWeek,
             DayAlias,  QuaterNo,    TrimesterNo, WeekDayTypeId, 
             MonthWeek, MonthName,   DayCount,    DateID)
         VALUES
            (@yd,        @smyear,     @syear,     @smonth,
             year(@yd),  month(@yd),  @sday,      @syear+'-'+@smonth+'-'+@sday,             
             day(@yd),   @i,          @wd,        datepart(ww,@yd), 
             @dalias,    @qua,        @Tri,       @daytype, 
             @mw,        @mn,         @dcount,    @dateid)

4:18:34 PMStarted executing query at Line 216
Statement ID: {2A26F9A2-662C-4090-90FE-E8AC89C4CF97} | Query hash: 0x62122C31B734175A | Distributed request ID: {DE532DE8-BF70-42EF-9009-EDB967C09CB5}
Statement ID: {AC007A8E-7C2A-47B3-9F1B-E978CBD13AAD} | Query hash: 0x46E2534D8500F033 | Distributed request ID: {2CA64EE1-9C4E-4307-AA51-1B5CB2967A82}
Statement ID: {CB399B75-0F03-4DCF-8DFC-1A628D554C16} | Query hash: 0x11DE74A0C552C8A6 | Distributed request ID: {1830B351-0609-4380-BD6B-1721934E882B}
Statement ID: {A7744399-491E-4C4B-80D7-87B04003E6BE} | Query hash: 0x97EB5560DCF0AC4E | Distributed request ID: {50AEECAA-2846-41D7-B9AA-489A2D2ED283}
Msg 515, Level 16, State 7, Procedure Chronology.PrepareDateDetail, Line 195
Cannot insert the value NULL into column 'DateID', table 'DateDetail'; column does not allow nulls. INSERT fails.
Total execution time: 00:00:00.969

I can see all the columns with values (none of those are null) in the "Results" TAB.

This code has been working for many years...

What could be happening?

[esobrino · Accepted solution]

Thanks all for the inmediate help and follow-up.  I further work on this and eventually realized once I run it on a different sql-instance that I commented out a "DEFAULT" value assignment within the "CREATE TABLE" since "Warehouses" don't support "defaults" yet.  

The "error statement" presented while running the query in a Datawarehouse stated that the "dateid" can't be null but it actually was the column that had the DEFAULT commented out.

I updated the SP to assign a none-null value to that other column before inserting a record and all was resolved.

It will be nice that DEFAULT gets supported sometime in the future.

Again thanks for your support.

[esobrino]

OK.  Now that is working as expected I like to report that it takes too long to finish executing (over 4 minutes) when compared to running the SP in MS-SQL it takes less than a second.

What can be done so it does not takes that long to run (the SP)?

The SP prepares a collection of records one per day of a given year (around 365 rows).  It should not take more than a few fractions of a second.

[esobrino · Accepted solution]

The Team that help me in the presented issue guide me to further readings regarding performance enhancements (see link below) and there I found a good discussion about the issue I am facing while inserting single records.  I greatly appreciate the guidance and understood the issue.

Warehouse performance guidelines - Microsoft Fabric | Microsoft Learn

[esobrino · Accepted solution]

Thanks all for the inmediate help and follow-up.  I further work on this and eventually realized once I run it on a different sql-instance that I commented out a "DEFAULT" value assignment within the "CREATE TABLE" since "Warehouses" don't support "defaults" yet.  

The "error statement" presented while running the query in a Datawarehouse stated that the "dateid" can't be null but it actually was the column that had the DEFAULT commented out.

I updated the SP to assign a none-null value to that other column before inserting a record and all was resolved.

It will be nice that DEFAULT gets supported sometime in the future.

Again thanks for your support.

[v-nikhilan-msft]

Hi @esobrino 
Thanks for using Fabric Community.

The error message indicates that a NULL value is being inserted into the ‘DateID’ column of the ‘DateDetail’ table, which does not allow NULLs. However, from your code and description, it seems you’ve taken precautions to ensure that the ‘@dateid’ variable is not NULL.

Here are a few things you could check:

1. Data Types: Ensure that the data type of the ‘@dateid’ variable matches the data type of the ‘DateID’ column in the ‘DateDetail’ table. If there’s a mismatch, it could potentially cause issues.

2. String Concatenation: You’re creating the ‘@dateid’ variable by concatenating ‘@syear’, ‘@smonth’, and ‘@sday’. Make sure that none of these variables are NULL. If any of them are NULL, the entire ‘@dateid’ variable would become NULL.

3. Implicit Conversion: SQL Server might be trying to implicitly convert the ‘@dateid’ variable to the data type of the ‘DateID’ column. If this conversion fails, it could result in a NULL value.

4. Trigger: Check if there’s a trigger on the ‘DateDetail’ table that’s modifying the data.

5. Debugging: Try printing the ‘@dateid’ variable immediately before the INSERT statement to ensure its value is not NULL at the time of insertion.

If none of these suggestions help I will request you to please go ahead with Microsoft support on this. 
https://support.fabric.microsoft.com/en-US/support/
The support team would understand the root cause better and help you. After creating the support ticket please provide the details here.

Thanks

[esobrino]

Thanks for the notes...

Look at the code...  it specifically states not to insert a record if @dateid is null...  and there is a preceeding SELECT as follows:

      select @yd,        @smyear,     @syear,     @smonth,
             year(@yd),  month(@yd),  @sday,      @syear+'-'+@smonth+'-'+@sday,             
             day(@yd),   @i,          @wd,        datepart(ww,@yd), 
             @dalias,    @qua,        @Tri,       @daytype, 
             @mw,        @mn,         @dcount,    @dateid

The previous display all the values showing/demonstrating that there are no nulls.  #1 through #4 don't apply since the values displayed through the SELECT have actual values.  #5 for debuging was done the original post (look at the code).

I guess is that the constraint is being checked and failed even before the actual insert is done!

Anyways I will try to request for support soon...  Again, thanks for the quick response.

[v-nikhilan-msft]

Hi @esobrino 
For this line: SET @dateid = @syear+@smonth+@sday Were @syear, @smonth and @sday all populated? (if any were null, it would cause @dateid to be null)

Also, if you are running this from the fabric webpage, make sure you run all of the code at the same time (each time you run a chunk of code, it creates a new connection, runs the code and then disconnects which means no data is carried between each time code is ran.

Hope this helps. Please let me know if you have any other questions.

[esobrino]

As already mentioned and explained all variables are display with none-null values and in top of that there is a statement that says don't insert if "DateID" is null... take a look:

      IF NOT EXISTS(SELECT * FROM Chronology.DateDetail
                     WHERE Date = @yd) AND @dateid is not null

and therefore I should not get the following statement:

3:00:55 PM
Started running query at line 1
Cannot insert the value NULL into column 'DateID', table 'DateDetail'; column does not allow nulls. INSERT fails.
Msg 515, Level 16, State 7, Code line 195
3:01:01 PM
Total execution time: 00:00:06.054

I just run it and again all variables dispaly values none of those null when executing the following line:

      select @yd,        @smyear,     @syear,     @smonth,
             year(@yd),  month(@yd),  @sday,      @syear+'-'+@smonth+'-'+@sday,             
             day(@yd),   @i,          @wd,        datepart(ww,@yd), 
             @dalias,    @qua,        @Tri,       @daytype, 
             @mw,        @mn,         @dcount,    @dateid

Still when executing the insert it complain about @dateid being null!!!!  

It feels that it checks the constraint and emits the issue without actually checking the value of @dateid...

If you like I can send you the Table definition and the SP so you can play with it... let me know.

Next steps?

[v-nikhilan-msft]

Hi @esobrino 
We haven’t heard from you on the last response and was just checking back to see if your query has been resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks

[v-nikhilan-msft]

Hi @esobrino 
We haven’t heard from you on the last response and was just checking back to see if your query has been resolved. Otherwise, will respond back with the more details and we will try to help.
Thanks

[esobrino]

I think I already replied somewhere else...  To brief on the current state of the reported issue please consider:

The preparation of a Date dimension is driven by the identification of days, weeks, months, periods and others and each data element is computed and guarantied never to be "null".  Therefore the argument that "@dateid" could be null at any point its impossible.

Since I got some issues that were not expected on an SP that has worked as expected for probably 15 years.  I added some statemetns like "AND @dateid is not null" in the "NOT EXISTS" where condition and for debuging purposes added the output of all variables to be inserted that show that all of those have values and are not null.

Today earlier I wrote:

As already mentioned and explained all variables are display with none-null values and in top of that there is a statement that says don't insert if "DateID" is null... take a look:

     IF NOT EXISTS(SELECT * FROM Chronology.DateDetail
                     WHERE Date = @yd) AND @dateid is not null

and therefore I should not get the following statement:

3:00:55 PM
Started running query at line 1
Cannot insert the value NULL into column 'DateID', table 'DateDetail'; column does not allow nulls. INSERT fails.
Msg 515, Level 16, State 7, Code line 195
3:01:01 PM
Total execution time: 00:00:06.054

I just run it and again all variables dispaly values none of those null when executing the following line:

      select @yd,        @smyear,     @syear,     @smonth,
             year(@yd),  month(@yd),  @sday,      @syear+'-'+@smonth+'-'+@sday,             
             day(@yd),   @i,          @wd,        datepart(ww,@yd), 
             @dalias,    @qua,        @Tri,       @daytype, 
             @mw,        @mn,         @dcount,    @dateid

Still when executing the insert it complain about @dateid being null!!!!  

It feels that it checks the constraint and emits the issue without actually checking the value of @dateid...

If you like I can send you the Table definition and the SP so you can play with it... let me know.

Next steps?

[v-nikhilan-msft]

Hi @esobrino 
The internal team wants you to create a support ticket to understand the issue better. I will request you to please go ahead with Microsoft support on this. 
https://support.fabric.microsoft.com/en-US/support/
After creating the ticket please provide the ticket details here so that it would be helpful for internal tracking.

Thanks

[esobrino]

The support # is:  2405080040012232