Results 1 to 4 of 4

Thread: [RESOLVED] SQL Server sproc suddenly can't insert records because columns don't allow nulls

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Resolved [RESOLVED] SQL Server sproc suddenly can't insert records because columns don't allow nulls

    I have a sproc that is suddenly broken. The table it inserts records into has 19 columns. The insert sproc has 11 parameters. So 8 columns used to default. Now they are not, for some reason, and I'm getting an exception when I try to execute the sproc:

    "Cannot insert the value NULL into column 'x', table 'y'; column does not allow nulls. INSERT fails."

    Some maintenance has been done to the table. There was a column CircuitType that was datatype varchar which I changed to int. The sproc has also been modified. CircuitType was one of its parameters so I changed its datatype in the sproc. I also added a column to the sproc that always existed in the table but wasn't getting populated by the sproc.

    And now the sproc doesn't work.

    I was going to say whatever, and go back to the table and define defaults for the columns not supplied. but I can't. SQL Server wants to drop and recreate it and I don't want to lose data.

    The next thing I thought of trying was to define all the parameters in the sproc that the table has columns and default them there. But I feel like that's a big change for a problem I should really try to understand before giving in to it.

    Any suggestions?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server sproc suddenly can't insert records because columns don't allow nulls

    So this wasn't 100% accurate: "...go back to the table and define defaults for the columns not supplied. but I can't. SQL Server wants to drop and recreate it and I don't want to lose data." When I tried to create the defaults in the designer it gave me that error. But I was able to run ADD CONSTRAINT commands on all the columns that were in the table that weren't input to the sproc and it is working now.

    I am still perplexed over what might've happened. Did these defaults somehow get wiped out? They had to have existed for the sproc to have ever worked, right?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  3. #3
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: SQL Server sproc suddenly can't insert records because columns don't allow nulls

    Not sure what feedback you're looking for here. Obviously, only you know what you changed, and you haven't stated if the database could have been modified by others.

    So, basically, you changed the table, you changed the SP, and then the SP didn't work. Yeah, I mean, the logical conclusion is that one or the combination of two or more of the changes you made broke it.

    In a vacuum, I would agree with you that, given an SP inserting data into a table where some columns aren't specified, AND one or more of those unspecified columns don't allow nulls, then there must have been default values defined for those unspecified don't allow null columns.

    If this were me, I would load up a "pre-change" backup of the database and first verify that the default values existed, and if so, repeat all the steps taken, and after each step check to see if things have been broken yet. If you don't have a backup to test with, then the suggestion would be to start making backups before making these types of changes.

  4. #4

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    Re: SQL Server sproc suddenly can't insert records because columns don't allow nulls

    Thank you. I was able to compare the table schema on our dev database vs our live database and the defaults are all there live. I am not sure what I did to wipe them all out. This isn't a problem any longer.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width