Error inserting in Microsoft SQL Server data source with NOCOUNT ON

Applies to: Denodo 7.0 , Denodo 6.0 , Denodo 5.5 , Denodo 5.0
Last modified on: 14 Mar 2018
Tags: JDBC data sources SQL Server Web Services

Download original document

You can translate the document:

Content

When executing an INSERT statement on a base view created on top of a Microsoft SQL Server table, the JDBC driver can return different information about the rows actually inserted in the table depending on the final status of the execution but also on the SQL Server configuration. For instance, supposing that we run an INSERT to add one row only, we can find the scenarios listed below:

  • 0 rows affected.
  • -1 rows affected.
  • 1 rows affected.

The last scenario is the expected one but the other 2 can also happen if the insertion is not successful. Receiving “0” or “-1” as the number of rows affected depends on the driver that you are using and on the connection options that you are using to connect to the data source.

The same scenarios can happen if you send a POST request to insert data using the Denodo RESTFUL web service. For the web service, in case of error, the response will contain the following error message and http code:

404: "The row was not inserted"

In some cases, the INSERT statement will actually be correctly executed and the new row will be inserted in the table but you will still get this error message.

This odd behaviour depends on the driver configuration and the “NOCOUNT” property on the SQL Server configuration.

The NOCOUNT parameter prevents the message indicating the number of rows affected by a Transact-SQL INSERT statement from being returned to the client as part of the results.

When NOCOUNT is SET to ON, the count (indicating the number of rows affected by a Transact-SQL INSERT) is not returned. When SET NOCOUNT is OFF (this is the default configuration), the count is returned.

References

SET NOCOUNT: SQL Server documentation.

Questions

Ask a question
You must sign in to ask a question. If you do not have an account, you can register here

Featured content

DENODO TRAINING

Ready for more? Great! We offer a comprehensive set of training courses, taught by our technical instructors in small, private groups for getting a full, in-depth guided training in the usage of the Denodo Platform. Check out our training courses.

Training