I have a stored procedure that is used to synchronize data from another system through a linked server. I make this call using NHibernate on a windows service that runs tasks using Quartz.net. One of these tasks is to synchronize the data task, and this is really just a sproc call:
using(var tx = Session.BeginTransaction()) { Session .CreateSQLQuery("exec dbo.spSyncData") .ExecuteUpdate(); tx.Commit(); }
This stored procedure does not accept any parameters and does not return any results. When this call is completed, I then load the data that was synchronized as follows :,
return Session.CreateCriteria(typeof(MyData)) .Add(Restrictions.Eq("Status", Status.Waiting)) .List<MyData>();
However, this call fails with an ADOException error with the message "Invalid attempt to call Read when the reader is closed."
I found that sproc was set to SET NOCOUNT ON, so I changed it, and now I have another exception ...
"There is already an open DataReader associated with this Command, which should be closed first."
This error occurs when making a sproc call.
Any ideas? Thanks Steve
UPDATE. Some of the issues I discovered are related to accessing a session object from multiple threads, which I did not expect. I was able to clear this, but I was not able to try the recommendation again to use the Session.Connection object to execute IDbCommand. This used to be unsuccessful, but I think it was also related to the problem of streaming. I hope to return to this attempt soon.
source share