Cannot insert the value NULL into column.

Applies to: Microsoft SQL Server 2008 R2, Linux Red Hat, Oracle.
 

Problem Description.
 

We have a stored procedure on a Oracle server that we run as a cron on Linux. From there we run a stored procedure on a SQL Server instance to insert data on a table, but when we run the stored procedure on the SQL Server instance we are getting the following error:

 

2012-04-01 17:35:16,299 com.XXXXXX.XXXXjob.oracle.OracleClientsQueryImpl - doInCallableStatement(CallableStatement) - end

2012-04-01 17:35:16,299 com.XXXXXX.XXXXjob.oracle.OracleClientsQueryImpl - doInCallableStatement(CallableStatement) - end

2012-04-01 17:35:16,311 com.XXXXXX.XXXXjob.oracle.OracleClientsQueryImpl - getClients() - end

2012-04-01 17:35:16,311 com.XXXXXX.XXXXjob.oracle.OracleClientsQueryImpl - getClients() - end

2012-04-01 17:35:16,311 com.XXXXXX.XXXXjob.mssql.MssqlClientsStoreProcImpl - updateClients(List<Client>) - start

2012-04-01 17:35:16,311 com.XXXXXX.XXXXjob.mssql.MssqlClientsStoreProcImpl - updateClients(List<Client>) - start

2012-04-01 17:35:17,830 com.XXXXXX.XXXXjob.mssql.MssqlClientsStoreProcImpl - Client Names: XXXXX XXXXXX

2012-04-01 17:35:17,830 com.XXXXXX.XXXXjob.mssql.MssqlClientsStoreProcImpl - Client Names: XXXXX XXXXXX

2012-04-01 17:35:18,491 com.XXXXXX.XXXXjob.mssql.MssqlClientsStoreProcImpl - Client Names: XXXXXXXX

2012-04-01 17:35:18,491 com.XXXXXX.XXXXjob.mssql.MssqlClientsStoreProcImpl - Client Names: XXXXXXXX

2012-04-01 17:35:19,044 com.XXXXXX.XXXXjob.mssql.MssqlClientsStoreProcImpl - updateClients(List<Client>)

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'CustomerId', table 'XXXXX.XX.Customer'; column does not allow null

s. INSERT fails.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:390)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:340)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:4575)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1400)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:179)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:154)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:322)

at com.XXXXXX.XXXXjob.mssql.MssqlClientsStoreProcImpl.updateClients(Unknown Source)

at com.XXXXXX.XXXXjob.runtime.XXXXCronRun.doTask(Unknown Source)

at com.XXXXXX.XXXXjob.runtime.XXXXCron.main(Unknown Source)

2012-04-01 17:35:19,044 com.XXXXXX.XXXXjob.mssql.MssqlClientsStoreProcImpl - updateClients(List<Client>)

com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert the value NULL into column 'CustomerId', table 'XXXXX.XX.Customer'; column does not allow null

s. INSERT fails.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1493)

Cause.

The stored procedure executed on SQL Server was expecting the primary key (column CustomerId) on that table to have the identity property set to "yes". The stored procedure was trying to insert on that table with assigning a value on the ColumnId based on the assumption that the column had the identity property set.


Solution.

We defined the primary key as identity column also.

 

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.