Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > ORM Profiler Section > Bugs & Issues> Exception when viewing result set for stored proc
 

Pages: 1
Bugs & Issues
Exception when viewing result set for stored proc
Page:1/1 

  Print all messages in this thread  
Poster Message
TrueWill
User



Location:
Iowa
Joined on:
05-Mar-2014 17:36:53
Posted:
13 posts
# Posted on: 18-Mar-2014 15:51:36.  
We're using SD.Tools.OrmProfiler.Interceptor.dll with a .NET 4 project that uses ADO.NET. This is with MS SQL Server and the System.Data.SqlClient.

Everything works fine, unless we select a stored procedure query, go to the Result Set tab, and click Execute. That results in the following exception:

Code:
Exception information.
=============================================================
ORM Profiler version: v1.5. Build: November 18th, 2013

Exception details:
=====================
Message: Invalid object name '#MixInventory'.
Source: .Net SqlClient Data Provider
Stack trace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at SD.Tools.OrmProfiler.Client.Core.ResultsetRetriever.RetrieveResultset(Int32 limit) in c:\Myprojects\VS.NET Projects\OrmProfilerv1.5_hg\SD.Tools.OrmProfiler.Client.Core\ResultsetRetriever.cs:line 61
at SD.Tools.OrmProfiler.Client.Controls.ResultsetViewer._queryExecutorBW_DoWork(Object sender, DoWorkEventArgs e) in c:\Myprojects\VS.NET Projects\OrmProfilerv1.5_hg\SD.Tools.OrmProfiler.Client\Controls\ResultsetViewer.cs:line 210
at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

Inner exception: <null>


In this case the actual stored procedure name is dbo.MixOligoInventoryDetailsLoadAll. It takes no parameters and returns a single result set.

We've seen this error with other stored procedures as well; it's not specific to this one.

Executing profiled SQL statements works fine.

Thank you!
  Top
Otis
ORM Profiler Team



Location:
The Hague, The Netherlands
Joined on:
22-Aug-2011 10:26:38
Posted:
611 posts
# Posted on: 19-Mar-2014 09:04:25.  
That's a temp table. Is the stored procedure originally executed in a transaction combined with other procedures which create the temp table outside the proc which fails? If so, that's the problem, because executing the proc individually then makes it fail as the temptable isn't there.

Frans Bouma
ORM Profiler / LLBLGen Pro Lead Developer | Blog | Twitter
 
Top
TrueWill
User



Location:
Iowa
Joined on:
05-Mar-2014 17:36:53
Posted:
13 posts
# Posted on: 19-Mar-2014 16:56:56.  
Otis wrote:
That's a temp table. Is the stored procedure originally executed in a transaction combined with other procedures which create the temp table outside the proc which fails? If so, that's the problem, because executing the proc individually then makes it fail as the temptable isn't there.


I checked the code; it is not being executed in a transaction with other queries.

The object name '#MixInventory' appears to be coming from the stored procedure itself. The code, in part:

****

CREATE PROCEDURE [dbo].[MixOligoInventoryDetailsLoadAll]
AS
BEGIN
SET NOCOUNT ON;

create table #MixInventory (

-- Lots of other queries to populate the temp table

select
    inv.ContainerId,

    -- Etc.
    invdetail.AreAllComponentsInInventory
from #MixInventory inv with (nolock)
left join #MixInventoryDetail invdetail with (nolock) on inv.ContainerId = invdetail.ContainerId
order by inv.ContainerId, invdetail.SampleContainerId;

drop table #MixInventory;
drop table #MixInventoryDetail;
drop table #JoinBreaker;
DROP TABLE #MixComponentsCheckedInCount;
DROP TABLE #MixComponentsExpectedCount;

END

****

If necessary I can probably replicate it with a simple application and stored proc; please let me know if you need that. Thanks!
  Top
Otis
ORM Profiler Team



Location:
The Hague, The Netherlands
Joined on:
22-Aug-2011 10:26:38
Posted:
611 posts
# Posted on: 19-Mar-2014 17:57:28.  
Ok thanks, we'll look into it. Very odd though... the code simply executes the proc again with the parameters (if any) of the original call...

Frans Bouma
ORM Profiler / LLBLGen Pro Lead Developer | Blog | Twitter
 
Top
Otis
ORM Profiler Team



Location:
The Hague, The Netherlands
Joined on:
22-Aug-2011 10:26:38
Posted:
611 posts
# Posted on: 20-Mar-2014 11:42:01.  
The error comes from the code which tries to obtain the schema of the resultset. This is done using a SchemaOnly call on the datareader:

using(var reader = _currentCommand.ExecuteReader(CommandBehavior.SchemaOnly))

The SqlClient performs this by doing the following:

SET FMTONLY ON;
<call proc>
SET FMTONLY OFF;

however, this fails when the proc call has a temptable, as these aren't created during the proc call. I suddenly remembered this as we used to use this in LLBLGen Pro as well (back in the v2 days). So I looked at the code in SqlCommand and indeed it uses this, SqlCommand.GetSetOptionsString().

In sql server 2012 they introduced a different way to do this, however this isn't used by SqlClient. http://technet.microsoft.com/en-us/library/ms173839.aspx.

It's a bit of a problem because using this datareader trick is the only one to not execute the query twice. If the query takes a long time to complete you don't want to wait twice as long for the results. Normally this goes OK, but indeed, it doesn't with procs and a temptable due to the FMTONLY setting.

We'll add a safety net for this, so the resultset will be returned, the schema will not due to the error.
Frans Bouma
ORM Profiler / LLBLGen Pro Lead Developer | Blog | Twitter
 
Top
Otis
ORM Profiler Team



Location:
The Hague, The Netherlands
Joined on:
22-Aug-2011 10:26:38
Posted:
611 posts
# Posted on: 20-Mar-2014 12:39:17. Goto attachments  
Added two safety nets for this (and an issue where the schema table could be null, e.g. when picking oledb).

Please use the attached debug build to make it work. The release build will be released tomorrow.


Frans Bouma
ORM Profiler / LLBLGen Pro Lead Developer | Blog | Twitter
 
Top
TrueWill
User



Location:
Iowa
Joined on:
05-Mar-2014 17:36:53
Posted:
13 posts
# Posted on: 20-Mar-2014 15:43:49.  
Otis wrote:
Added two safety nets for this (and an issue where the schema table could be null, e.g. when picking oledb).

Please use the attached debug build to make it work. The release build will be released tomorrow.


Tested; that works. Thank you very much! Regular Smiley
  Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.09082011 Final.