Problem in Stored Procedure with Temporary Table

Hi,

I am trying to use stored procedures in my lightspeed model. It works fine for me for stored procedures which has some parameters and returns a fixed result set.

But now the problem is I have a storedprocedure which creates a temp table on the fly for my own computation, the SQL script looks like this,

  IF object_id('tempdb..#Temp') is not null

DROP TABLE #Temp

--Creating temp table to store the result set of rule engine

CREATE TABLE #Temp (xPKID bigint)

INSERT INTO #Temp EXEC dbo.spEngine @ReportPKID,@EmployeePkid

While drag and drop the stored procedure I am receiving the following error,

Exception while processing Server Explorer drag: System.Data.SqlClient.SqlException: Invalid object name '#Temp'.

Thanks in advance for your support.

Regards,

Vijay 

Sorry for posting the same twice... Discard the previous thread...

This appears to be a strange issue somewhere in the SQL Server stack.

When you drag a sproc onto the designer, we call SqlCommand.ExecuteReader(CommandBehavior.SchemaOnly) to get the result type of the sproc.  For your procedure, it seems that this gives an error.  However, if some other program calls ExecuteReader without the SchemaOnly flag, then when we call it with SchemaOnly, it suddenly starts working!

We'll see if we can gain any insight into this, but for now, try writing a little console application that invokes your stored procedure and does an ExecuteReader() on the results.  After you run that little program, you may be able to successfully drag your procedure (at least that is what we are seeing).  Very strange!

No luck with that approach.

I created a console application that does ExecuteReader() and fetches the result set into SqlDataReader. The reader object contains values after executing the sproc. Eventhen when i drag and drop i receive the same exception as before. The following is the entire exception,

Exception while processing Server Explorer drag: System.Data.SqlClient.SqlException: Invalid object name '#TempSegments'.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()

   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)

   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)

   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.ExecuteReader(CommandBehavior behavior)

   at Mindscape.LightSpeed.Generator.Extraction.SqlServerExtractorBase.<ExtractProcedures>d__2a.MoveNext()

   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)

   at Mindscape.LightSpeed.Generator.Extraction.Provider`1.ExtractProcedures(IEnumerable`1 procedureNames)

   at Mindscape.LightSpeed.Generator.Integration.ProcedureBuilder.AddProceduresToModel(Model model, ILightSpeedDataSource connection, IEnumerable`1 procedureNames, IGenerationLog log)

   at Mindscape.LightSpeed.Generator.Integration.DiagramDragDropHandler.<>c__DisplayClass4.<OnDragDrop>b__3(DragObject dragObject, IGenerationLog log)

   at Mindscape.LightSpeed.Generator.Integration.ServerExplorer.IfIsServerExplorerDrag(DragEventArgs e, DragAction action)

It looks like you will need to map the procedure manually.  Go to the Toolbox and drag on a SelectProcedure or NonQueryProcedure (depending on whether your procedure SELECTs a set of rows, or just performs an INSERT).  Then right-click to define procedure parameters if any.  Finally, if the procedure is a SELECT procedure, drag on an Entity and set up properties corresponding to the SELECTed columns, and set the SelectProcedure's entity type to this Entity.  (You don't need to set up a return type for a NonQueryProcedure.)

Thanks. It works.

Regards,

Vijay

Any updates on this? I have the same problem, but I'd like to try to avoid the manual approach, since the select statement is returning close to 200 columns. Having to define everything manually sort of defeats the purpose of using an ORM. I'm wondering if using a table variable instead of a temp table would work, but since it's 5:00 p.m., I'll have to test that idea out tomorrow. :-)

Thanks, Ken

Hi Ken,

Id be interested to hear if the table variable approach worked for you but for the temp table case you will have to define the entity manually.

Jeremy

Yes, the table variable approach solved the problem!

Thanks, Ken