Databases
StackOverflow2013
Postsdbo
POCall to Session.CreateSQLQuery ExecuteUpdate fails in NHibernate
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCall to Session.CreateSQLQuery ExecuteUpdate fails in NHibernate
    text
    copied!<p>I have a stored procedure that is used to synchronize data from another system through a linked server. I execute this call using NHibernate in a Windows Service that runs tasks using Quartz.net. One of these tasks is the synchronize data task and it is really just the sproc call:</p> <pre><code>using(var tx = Session.BeginTransaction()) { Session .CreateSQLQuery("exec dbo.spSyncData") .ExecuteUpdate(); tx.Commit(); } </code></pre> <p>This stored procedure takes no parameters and returns no results. When this call is completed, I then load the data that was synchronized like this,,,</p> <pre><code>return Session.CreateCriteria(typeof(MyData)) .Add(Restrictions.Eq("Status", Status.Waiting)) .List&lt;MyData&gt;(); </code></pre> <p>However, this call fails with a ADOException with the message "Invalid attempt to call Read when reader is closed."</p> <p>I discovered that the sproc was set to SET NOCOUNT ON so I changed that and now I am getting a different exception...</p> <p>"There is already an open DataReader associated with this Command which must be closed first."</p> <p>This error occurs when the sproc call is committed.</p> <p>Any ideas? Thanks, Steve</p> <p>UPDATE: Some of the issues I discovered to be related to access of the session object across multiple threads, which I had not expected. I was able to clean this up, but haven't had a chance to try the recommendation again of using the Session.Connection object to execute a IDbCommand. That had failed before, but I think that was related to the threading issue as well. I hope to get back to that attempt soon.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload