Hi all -



I just went through a pretty intense debugging session based on two BOs that are hooked up to two separate SQL Server tables, both of which have a set of Instead-of triggers, some of which fire off calls to other stored procedures. The journey through the various paths is a bit too convoluted to recap here.



Now that I have come out on the other side there are some questions I have regarding BOs wired up to tables on which triggers exist. I am letting the data layer gen the SQL for .Save()s (i.e., not using stored procedures to handle the Save()).



Part of the problem I was chasing down had to do with the following exception:

BusinessLayerException

An error occurred while saving an the data to the server.

DataLayerSavingException

Invalid attempt to read when no data is present.

InvalidOperationException

Invalid attempt to read when no data is present.



Source : MicroFour StrataFrame Business



Stack Trace:

at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)

at System.Data.SqlClient.SqlDataReader.get_Item(String name)

at MicroFour.StrataFrame.Data.SqlDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler, Boolean RecreateCommand)

at MicroFour.StrataFrame.Data.DbDataSourceItem.UpdateRow(QueryInformation QueryInfo, DataRow RowToUpdate, ConcurrencyExceptionHandler ConcurrencyHandler, AddRowErrorHandler RowErrorHandler)

at MicroFour.StrataFrame.Data.DataLayer.UpdateDataTableThread(Object ThreadParams)

at MicroFour.StrataFrame.Data.DataLayer.UpdateDataTable(DataTable TableToUpdate, Boolean Transactional, String TransactionKey)

at MicroFour.StrataFrame.Data.DataLayer.SaveByForm(DataTable TableToSave, Boolean Transactional, String TransactionKey)

at MicroFour.StrataFrame.Business.BusinessLayer.SaveByForm(Boolean Transactional, String TransactionKey)

at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.Save(Boolean Transactional, String TransactionKey)

at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.Save()

at MicroFour.StrataFrame.UI.Windows.Forms.MaintenanceFormToolStrip.cmdSave_Click(Object sender, EventArgs e)

at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)

at System.Windows.Forms.ToolStripButton.OnClick(EventArgs e)

at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)

at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)

at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)

at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)

at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)

at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message& m)

at System.Windows.Forms.ScrollableControl.WndProc(Message& m)

at System.Windows.Forms.ToolStrip.WndProc(Message& m)

at System.Windows.Forms.Control.ControlNativewindow.OnMessage(Message& m)

at System.Windows.Forms.Control.ControlNativewindow.WndProc(Message& m)

at System.Windows.Forms.Nativewindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Looking at some of the postings on here led me to consider changing the SET NOCOUNT setting on a few of the triggers. That worked for some, but not all of them. I had also previously read about the need to have stored procedures do a SELECT rather than a RETURN to give StrataFrame something to lock into on the return.



Based on the above:



1.) It's looking to me like whatever happens on the backend better be returning a result set... even if it's a "bogus" one that is created by doing something like SELECT @@ROWCOUNT. Yes?



2.) Where can I find a write-up that discusses this, and any other requirements that would come into play such that future triggers are developed with these requirements in mind?



3.) What does StrataFrame interrogate in the data returned from SQL Server? As part of the problem I was troubleshooting there was a scenario where a SELECT in a separate sproc called by one of the triggers was actually allowing the trigger--which would have otherwise thrown the above exception--to "succeed." This seems a bit dangerous to me.



TIA



________________

_____/ Regards,

____/ al


________________
_____/ Regards,
____/ al

This really has less to do with SF and more of how you return data or call your sprocs.







Well, it depends on how you are calling the sproc. If you are changing the logic in the sproc to perform an UPDATE or an INSERT, then by default, the ExecuteNonQuery is expecting a result for notification. But return a result set, no. A result set indicates that data is going to be returned and populated into the BO, so in that case no, you do not need to return a result set. In fact, I guess I am not sure exactly what you are doing here because there are MANY times that we never return a result set. In fact, it is actually a standard of ours to always call SET NOCOUNT ON at the top of sprocs to improve query performance. So I am thinking that this may just be a matter of how you are calling the sproc. Need more information on this one. Sorry.







The whole point of a trigger is NOT to have to program for anything client side. So I am getting more confused with each question as to what you are doing here.







It depends on how a SPROC is called as to what will be looked at on a return. In fact, most of what SF is doing is nothing more than passing through to a DbCommand in this regard. Aside from that, as I mentioned on the last question, the whole point of a trigger is to be triggered from an INSERT, UPDATE, or DELETE and all of this occurs server side and should have NO bearing on what is coming back in 99% of the cases. So again, I need more detail as to what you are doing because I think that you may be fighting against yourself on this one.

Hi Trent!



You think your confused??? LOL You ain't seen nothin' yet!



Actually of all the scenarios that played out the most interesting has to do with the following setup. Assume a BO mapped to a SQL Server table, on which there are Insert, Update and Delete triggers. From the SF BO perspective .Saves() are being handled by StrataFrame (i.e., I am not using sprocs to perform the insert, update or delete). The only place a stored procedure comes into play is that the triggers call a small stored procedure that calculates the next-unique value to use for some column (let's call this sproc usp_GetNext).



The triggers mentioned above were added to the table after the client app was built and tested, and up until then all insert, updates and deletes (i.e., bo.Save()s) worked as expected. Once the triggers were added, the exception I posted in my original message (... DataLayerSavingException... Invalid attempt to read when no data is present... Invalid attempt to read when no data is present.) began to occur.



So, in troubleshooting this problem I noticed that if I changed usp_GetNext to use a SELECT (rather than a RETURN) the exception went away. BTW, the reason I thought of doing this had to do with a SF forum posting I had read that indicated a SELECT was needed by SF in certain scenarios.



Once the above "solved" the problem, I decided to try removing the call to usp_GetNext from within the triggers. From that point on the only way I found to not get the exception was to introduce a SELECT at the end of the trigger, leading me to the thought that something must be sent back by SQL Server for the BO's .Save() to be happy.



Things are working now, but I would like us to better understand the hows and whys...



TIA

________________
_____/ Regards,
____/ al

Hi Alex,



I think your trigger code is almost certainly broken - my reason for saying this is 'been there, done that...'



Try the following:



Open Query in Enterprise Manager.



Write a simple piece of Update code on your table.



Run the code and see if you get an error - if so the problem is entirely within the database and the error is simply being propergated back up the chain.



Repeat with an Insert and Delete test.





Also, you could try SET NOCOUNT ON in the trigger. While I can't remember the circumstance I know doing this has fixed certain problems in the past.



Cheers, Peter

Hello Peter!



Thanks for the suggestions. One of the first troubleshooting steps was to run INSERTs and UPDATEs from EM to ensure the triggers were working correctly. This was done even before firing up the StrataFrame application and testing transactions from there. When run in EM (or Toad) the Inserts/Updates worked perfectly (i.e., the triggers did not cause the issue we saw when running the SF application).



I am guessing the introduction of the triggers changed what comes back from SQL Server to the SF BO, and thus created the problem. By my including a "bogus" SELECT @@rowcount to the end of the trigger I think I'm giving the BO something to lock onto, thus eliminating the datareader error. ...at least that's my theory.



Of course, we'd all like to really understand what's going on rather than just relying on guesswork to "solve" the problem.



Thanks.

________________
_____/ Regards,
____/ al

This is am ambiguous issue, Alex. As such, these types of things generally require more concrete re-production measures. Would it be possible for your to create a simple database and sample reproducing this behavior?