Stored proc error after upgrading to LS 5 - "No Id returned from stored procedure, Check that the stored procedure selects the identity column"

We've recently upgraded to LS 5 from 3.1.1834; After the upgrade all of a sudden our existing code that calls the stored proc is failing with the error.

All we did was simply update the reference to the new Lightspeed DLL's; The suggested workaround from this thread http://www.mindscapehq.com/forums/thread/886577 shows that we need to modify our proc's that is not an option for us at the moment, the only alternative is to roll back to LS version 3.1 which we don't want to do unless we have too :-(, any help/tips/workaround on how to get past this would be greatly appreciated.

No Id returned from stored procedure 'test_proc'. Check that the stored procedure selects the identity column

here is the Lightspeed generated code in the model code behind file

public System.Collections.Generic.IList<TestResult> TestProc(long test1, string test2, int test3, int test4, int test5, string test6, string test7)
    {
      ProcedureParameter test1Parameter = new ProcedureParameter("test1", test1);
      ProcedureParameter test2Parameter = new ProcedureParameter("test2", test2);
      ProcedureParameter test3Parameter = new ProcedureParameter("test3", test3);
      ProcedureParameter test4Parameter = new ProcedureParameter("test4", test4);
      ProcedureParameter test5Parameter = new ProcedureParameter("test5", test5);
      ProcedureParameter test6Parameter = new ProcedureParameter("test6", test6);
      ProcedureParameter test7Parameter = new ProcedureParameter("test7", test7);
      ProcedureQuery procedureQuery = new ProcedureQuery("test_proc", test1Parameter, test2Parameter, test3Parameter, test4Parameter, test5Parameter, test6Parameter, test7Parameter);
      System.Collections.Generic.IList<TestResult> valueToReturn = Find<TestResult>(procedureQuery);

      return valueToReturn;
    }

Here is the proc, fairly straight forward insert and select;

CREATE PROCEDURE [dbo].[test_proc]  
@test1 BIGINT, @test2 VARCHAR (25), @test3 INT, @test4 INT, @test5 INT, @test6 VARCHAR (255), @test7 VARCHAR (50)
AS  

-- test_table  
INSERT INTO [dbo].[test_table] (  
 [test1],   
 [test3],   
 [DateEntered],   
 [test6],   
 [DealerId],   
 [EnteredBy]  
)  
VALUES (  
 @test1,   
 @test3,   
 GETDATE(),   
 @test6,   
 @test4,   
 @test5  
)  

-- test_tableExtra  
INSERT INTO [dbo].[test_tableExtra] (  
 [test1],  
 [EmailEnabled],  
 [OrderNumber],  
 [DID],  
 [test2]

)  
VALUES (  
 @test1,  
 0,   
 @test7,  
 @test4,  
 @test2 
)  

SELECT 
    [test1],   
    [test3],   
    [DateEntered],   
    [test6],   
    [DId],   
    [EnteredBy]
FROM
    [dbo].[test_table]
WHERE
    [test1] = @test1

Hi,

Im afraid Im not sure why this worked in LightSpeed 3.11 as as far as I am aware we have always had a restriction on procedure queries needing to have an Id column specified. This is required because you are loading back entities and all entities need an Id value.

That said we have had an increasing number of requests to support ProcedureQueries in projections to avoid this situation (e.g. you just want to project back a list of T) and Im happy to report Ive merged in the changes for this today so this will be available in the next nightly build. To use this there are now overloads for all of the UnitOfWork.Project methods to take a ProcedureQuery argument.

Jeremy

Thanks for getting back Jeremy, I will grab the build on Monday morning and try it out; so does this mean that we dont call the autogenerated code from the stored proc model file and call the new overload method?

Yes thats correct, you will need to call these new methods manually but once you have made these changes and testing that its working as you would like then you could look at switching the code generation for procedures over to use these new methods by altering the code generation templates accordingly.

See: http://www.mindscapehq.com/blog/index.php/2009/09/16/customising-lightspeed-entity-templates/ for more details on how to set this up on a per project basis.

Jeremy

Thank you Jeremy for the quick turn around, the nightly build with the overload method works well, we are now in the process of upgrading our repositories with the overload, thanks a lot for the help.

After the nightly build we are noticing a small issue, We've updated to the Project overload method; but now the nullable Id properties are not being populated.

for the below class, after the stored proc call via project, only the name and manager are being populated, the other two fields are just null, please advice

[System.Diagnostics.DebuggerNonUserCode]
    public System.Nullable<int> TeamId
    {
      get { return Get(ref _teamId, "TeamId"); }
      set { Set(ref _teamId, value, "TeamId"); }
    }

    [System.Diagnostics.DebuggerNonUserCode]
    public string Name
    {
      get { return Get(ref _name, "Name"); }
      set { Set(ref _name, value, "Name"); }
    }

[System.Diagnostics.DebuggerNonUserCode]
public System.Nullable<int> ManagerId
{
  get { return Get(ref _managerId, "ManagerId"); }
  set { Set(ref _managerId, value, "ManagerId"); }
}

[System.Diagnostics.DebuggerNonUserCode]
public string Manager
{
  get { return Get(ref _manager, "Manager"); }
  set { Set(ref _manager, value, "Manager"); }
}

Can you send us through a small repro project and associated proc + test data which triggers this behaviour please as Im unable to reproduce this with a similar example here.

Thanks!
Jeremy

Hi Jeremy,

I am attaching the sample repo project;

I've just created a new db called TestDb and only added the below stored proc and if you run the sample project you can see that the teamid and manager id are not being populated.

CREATE PROCEDURE [dbo].[Test_Proc] 
    @SalesUserId int = 0
AS
BEGIN   
    SELECT 
        1234 as [TeamId],
        'TestName' as [Name],
        12345 AS 'ManagerId',
        'TestManager' AS 'Manager'
END
GO

I got the last nightly build on the site 5.0.2477.0 and its still an issue, you can try it with the sample repo project I've attached.

Thanks for sending through the repro, Ive identified the issue and we are currently testing a fix so I should be able to get this into the nightlies for you tomorrow. I will update you again once this has been merged in.

Jeremy

This fix has now been merged in and will be available in the next nightly build.

Jeremy