Lightspeed designer help with Stored Procedures

 Hi guys,

I have recently noticed that when I drag and drop a stored procedure on the designer it doesn't bring the blue <StoredProcName>Result class over... I only see the pink <StoreProcName> class.

So lets say I am deleting and re-adding an exiting stored procedure my existing data access class is expecting a Result class but it is not generated.

Any idea what settings did change or what am I doing wrong. It used to work fine.

Thanks,

This may mean that LightSpeed has identified an existing entity as the return type of the stored proc.  For example, if you deleted the pink sproc shape, but left the blue XxxResult shape, then re-dragged the sproc, LightSpeed would spot that the result schema of the sproc matched the existing XxxResult entity, and use that rather than creating a new entity type.

To check this, look at the type shown in the pink shape above the name of the sproc.  If it says IList<XxxResult> (or some other entity type) then LightSpeed has matched it to another type.  (You can also look at Entity Type in the Properties window.)

If the shape says IList<...> and the Entity Type is blank, then that means LightSpeed has been unable to work out the schema for the sproc results.  This may mean that there was an error when we called the sproc with dummy data, or that the sproc has been changed to return a scalar value rather than a result set.  Show the Output window (View > Output) and re-drag the sproc: any warnings will be printed to the output window.

 The situation has gotten a little more interesting...

Let me give you examples... I work better with example

Lets say I have a store proc sp_A that returns long Id (as a ls sp must return an Id), string, string, datetime, bit and string.

Now I have another stored proc sp_B that return long Id

When I delete and re-add sp_A it doesn't give me the Result class sp_AResult instead it is thinking that the return type is sp_BResult which only contains Id

I have no idea why it would think that way.

Thanks,

Thanks for the additional info.  That sounds like a bug and I'll check into it.

In the meantime the workaround is to keep your sp_AResult entity type around, and after re-dragging sp_A, manually change the Entity Type of sp_A to sp_AResult.

Could you let me know what build of LightSpeed you're on?  We have made some fixes in this area in the nightly build.  If you are using 3.0 RTM, could you try the current nightly and let me know if this solves the problem?  Thanks!

 I did not get the nightly build. But found something mysterious in my lsmodel. one of the store procs had two items on the canvas. Which is not good. Don't know how it happened... but I deleted all my stored procs as I said I only had a handful and re-added them. Lightspeed didn't like the stored proc that had its twin on the canvas. There was temp table in the stored procedure. LightSpeed doesn't like temp tables in the stored procs... don't know why. I would like to know the reason.

But when I converted the temp table to a local table variable in the stored proc and then added it... I got expected behaviour.

Except for one stored proc. Where the return a set of bigint as Id and int as ReturnValue but LS keeps thinking it is only returning an Id and thinks that class definition already exists... which is true I have a class definition that only returns Id. But this store proc has a different return format.

A little head way into the mystery. Now I need to figure out why a class {bigint, int } same as {bigint }

Thank you for your help.

I'm also curious as to why LightSpeed wouldn't like temp tables: all we care about in a stored procedure is the result set schema.  If you have a reasonably simple procedure that demonstrates the problem then we'd certainly be interested to see it -- thanks!

The { bigint, int } being inferred as the same class as { bigint } would have happened with the RTM build but *should* be fixed in the nightlies.  If it continues to misbehave though, you can always create the { bigint, int } entity class manually (by dragging the Entity icon from the Toolbox) and set the sproc's Return Type to this new entity class in the Properties window.

About the temp tables. I have a simple stored proc. If you create this stored proc and try to drag and drop on the desinger you get errors reported in the output window. Please scroll below to see the error. The work around is... since temp tables are more effiecient... you can create a store proc with local table variable then drag and drop... once the code is generated then go behind the sence to the stored proc script to change it to a temp table. Now LS doesn't know if it is a temp table or local variable :)

Let me know why LS is not liking the temp table.

Thanks,

CREATE PROCEDURE test
 @input as bigint
AS
BEGIN
 SET NOCOUNT ON;
 create table #Account
 (
 AccountNumber bigint
 )
 Insert into #Account (AccountNumber)
 Values (1)
 Insert into #Account (AccountNumber)
 Values (2)
 Insert into #Account (AccountNumber)
 Values (3)
 
 SELECT * FROM #Account

END

Exception while processing Server Explorer drag: System.Data.SqlClient.SqlException: Invalid object name '#Account'.
   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__d.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)

This appears to be an issue with the way SQL Server processes FMTONLY ON calls:

https://connect.microsoft.com/VisualStudio/feedback/details/314650/sqm1014-sqlmetal-ignores-stored-procedures-that-use-temp-tables

Like sqlmetal, we call stored procedures with the CommandBehaviour.SchemaOnly flag (which generates the SQL FMTONLY ON statement) to prevent them from executing (because we only want the result schema, we don't want the sproc to run).  The discussion on Connect suggests you can override this by calling FMTONLY OFF within your sproc (but in this case you will have to deal with the designer calling your sproc with dummy parameters).  Unfortunately, it appears from the Connect issue that there's nothing we can do about it from the LightSpeed side.

 That's pretty cool. Thank you I appreciate your help.

I puts me to ease when I understand the reason behind a specific behaviour.

Thanks,