Skip to content

x Want to ask a question or reply? Click here to join the group.
Seema Singla

[Fixed] SQLError: Cannot insert the value NULL into column 'Libr_LibraryId', table 'CRM.dbo.Library'; column does not allow nulls. INSERT fails

4 minute read time.

Hiya,

If you come across an error while upgrading from very older version that says: 

Errors 16 Items

                After line 40: SQLError: Cannot insert the value NULL into column 'Libr_LibraryId', table 'CRM.dbo.Library'; column does not allow nulls. INSERT fails (INSERT INTO Library (Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ('Letter', 'Marketing', 'Global Templates\US', 'Initial Inquiry Acknowledgment Template.html', 'Initial Inquiry Acknowledgment', 'Final', 'Y', 'US', 'Y', 'Y', 'Lead'); )

                After line 40: SQLError: Cannot insert the value NULL into column 'Libr_LibraryId', table 'CRM.dbo.Library'; column does not allow nulls. INSERT fails (INSERT INTO Library (Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ('Letter', 'Support', 'Global Templates\US', 'Case Progress Confirmation Template.html', 'Case Progress Confirmation', 'Final', 'Y', 'US', 'Y', 'Y', 'case'); )

This could be fixed by ES script provided below on 2025R2. (I have also attached the script as text document. Change the extension before using the file)

//Delete Library entries if existing 


RUNSQL("DELETE FROM Library WHERE libr_filename = 'Customer Service Response Template.html' ");
RUNSQL("DELETE FROM Library WHERE libr_filename = 'Case Progress Confirmation Template.html' ");
RUNSQL("DELETE FROM Library WHERE libr_filename = 'Complaint Resolution Template.html' ");
RUNSQL("DELETE FROM Library WHERE libr_filename = 'Case Update Notification Template.html' ");
RUNSQL("DELETE FROM Library WHERE libr_filename = 'Initial Inquiry Acknowledgment Template.html' ");
RUNSQL("DELETE FROM Library WHERE libr_filename = 'Product Information and Pricing Template.html' ");
RUNSQL("DELETE FROM Library WHERE libr_filename = 'Introduction to Sales Team Template.html' ");
RUNSQL("DELETE FROM Library WHERE libr_filename = 'Case Study-Success Story Template.html' ");
RUNSQL("DELETE FROM Library WHERE libr_filename = 'Prospecting Introduction Template.html' ");
RUNSQL("DELETE FROM Library WHERE libr_filename = 'Needs Assessment Letter Template.html' ");
RUNSQL("DELETE FROM Library WHERE libr_filename = 'Proposal Letter Template.html' ");
RUNSQL("DELETE FROM Library WHERE libr_filename = 'Thank you letter Template.html' ");
RUNSQL("DELETE FROM Library WHERE libr_filename = 'Letter to CEO Template.html' ");
RUNSQL("DELETE FROM Library WHERE libr_filename = 'Letter to CFO Template.html' ");
RUNSQL("DELETE FROM Library WHERE libr_filename = 'Letter to CTO Template.html' ");
RUNSQL("DELETE FROM Library WHERE libr_filename = 'Letter to COO Template.html' ");




//insert again 


RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Support',	'Global Templates\\US',	'Customer Service Response Template.html',	'Customer Service Response',	'Final',	'Y',	'US',	'Y',	'Y',	'case');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Support',	'Global Templates\\US',	'Case Progress Confirmation Template.html',	'Case Progress Confirmation',	'Final',	'Y',	'US',	'Y',	'Y',	'case');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Support',	'Global Templates\\US',	'Complaint Resolution Template.html',	'Complaint Resolution',	'Final',	'Y',	'US',	'Y',	'Y',	'case');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Support',	'Global Templates\\US',	'Case Update Notification Template.html',	'Case Update Notification',	'Final',	'Y',	'US',	'Y',	'Y',	'case');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Marketing',	'Global Templates\\US',	'Initial Inquiry Acknowledgment Template.html',	'Initial Inquiry Acknowledgment',	'Final',	'Y',	'US',	'Y',	'Y',	'Lead');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Marketing',	'Global Templates\\US',	'Product Information and Pricing Template.html',	'Product Information and Pricing',	'Final',	'Y',	'US',	'Y',	'Y',	'Lead');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Marketing',	'Global Templates\\US',	'Introduction to Sales Team Template.html',	'Introduction to Sales Team',	'Final',	'Y',	'US',	'Y',	'Y',	'Lead');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Marketing',	'Global Templates\\US',	'Case Study-Success Story Template.html',	'Case Study/Success Story',	'Final',	'Y',	'US',	'Y',	'Y',	'Lead');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Sales',	'Global Templates\\US',	'Prospecting Introduction Template.html',	'Prospecting Introduction',	'Final',	'Y',	'US',	'Y',	'Y',	'Opportunity');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Sales',	'Global Templates\\US',	'Needs Assessment Letter Template.html',	'Needs Assessment Letter',	'Final',	'Y',	'US',	'Y',	'Y',	'Opportunity');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Sales',	'Global Templates\\US',	'Proposal Letter Template.html',	'Proposal Letter',	'Final',	'Y',	'US',	'Y',	'Y',	'Opportunity');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Sales',	'Global Templates\\US',	'Thank you letter Template.html',	'Thank you letter',	'Final',	'Y',	'US',	'Y',	'Y',	'Opportunity');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Sales',	'Global Templates\\US',	'Letter to CEO Template.html',	'Letter to CEO',	'Final',	'Y',	'US',	'Y',	'Y',	'person');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Sales',	'Global Templates\\US',	'Letter to CFO Template.html',	'Letter to CFO',	'Final',	'Y',	'US',	'Y',	'Y',	'person');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Sales',	'Global Templates\\US',	'Letter to CFO Template.html',	'Letter to CFO',	'Final',	'Y',	'US',	'Y',	'Y',	'person');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Sales',	'Global Templates\\US',	'Letter to CTO Template.html',	'Letter to CTO',	'Final',	'Y',	'US',	'Y',	'Y',	'person');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Sales',	'Global Templates\\US',	'Letter to CFO Template.html',	'Letter to CFO',	'Final',	'Y',	'US',	'Y',	'Y',	'person');");

RUNSQL("INSERT INTO Library (Libr_LibraryId, Libr_Type, Libr_Category, libr_filepath, libr_filename, libr_note, libr_status, libr_active, Libr_language, Libr_Global, libr_mergetemplate, libr_entity) VALUES ((select top 1  Libr_LibraryId + 1 from [Library] Order by 1 desc), 'Letter',	'Sales',	'Global Templates\\US',	'Letter to COO Template.html',	'Letter to COO',	'Final',	'Y',	'US',	'Y',	'Y',	'person');");

Save the above in a document with extension .es

This script can be run with RunES tool present under location: FTP > Customer\SageCRM Tools\RunES

Copy the entire RUNES folder to your machine.

1. Backup the database.
2. Run the .es script using the instructions present under readme.md in the RunES folder

 

  • in reply to AlisonA

    Hi   - Hope you are doing well! The above mentioned script will work on 2025 R2 release. Once you are on 2025 R1, you can run this script and the issue will no longer appear when upgrading to 2025 R2. 

    Hope this helps!

    Kind Regards,

    Seema Singla

  • Having built a component from the ES file and applied this to an affected system the core issue which causes the upgrade to error does not appear to have been addressed. 

    During the upgrade process the installer is creating new records in the Library entity (table), which presumably is related to the following new feature https://help.sagecrm.com/on_premise/en/2024R2/ReleaseNotes/Content/ReleaseNotes/WhatsNew-NewTemplates.htm.  I'm assuming that the installer has not been designed to take pre 7.2 databases into account where the record IDs are handled differently.  So when upgrading a pre 7.2 database the upgrade errors as the installer is unable to insert records into the Library table.

    The ES script above adds the missing Library table entries but does not fix the fundamental issue.  In my view there installer needs to either

    • Change the way pre 7.2 databases generate record IDs so that record IDs are generated in the same way for both pre and post 7.2 systems (preferred)
    • The installer needs to recognise when it is upgrading a pre 7.2 database and use an appropriate method to add new records

    As discussed above I have test upgraded an affected system to Sage CRM 2024 R2 then applied the ES file.  Then I have applied Sage CRM 2024 R2.3 and the same error occurs during that upgrade, even though the 'fix' has been applied.  So it appears that this issue will continue to occur until there is a proper resolution to this issue.

    P.S. the reason for not applying Sage CRM 2025 R1 is that this system is hosted under Windows Server 2019 which is not supported with Sage CRM 2025 R1, even though Windows Server 2019 is still supported by Microsoft.  The restrictive nature of the Sage CRM system requirements is a big barrier to upgrading clients.

*Community Hub is the new name for Sage City