Error with Retrieve Result Shape

[topry]

Using latest Q3 release and VS2010 with SQL2000
Using the stored procedure editor, to create complex types.
It works for some, but on others I get the following error:
Invalid object name: '#tmp'.

The stored procedure is parameter-less and returns a single result set containing 12 columns. Nothing unique in the result set, no use of cursors in the stored procedure, just a simple SELECT, no JOINS.

The cause of the problem appears to be the following that is within the stored procedure - it executes another stored procedure to identify the user via SPID, using:

Declare @MyID uniqueidentifier
exec usp_SelectEmployeeIDFromSPID @MyID output

Within this (^) stored procedure is a temporary table named, '#tmp' and I would presume is the cause of the error, the TSQL for the sproc follows:

ALTER PROCEDURE [dbo].[usp_SelectEmployeeIDFromSPID]
    @EmployeeID uniqueidentifier OUTPUT
AS

SET NOCOUNT ON

create table #tmp(
spid int,
ecid int,
status varchar(50),
loginame varchar(50),
hostname varchar(50),
blk integer,
dbname varchar(50),
cmd varchar(50))

DECLARE @loginame varchar(50), @hostname varchar(50)
INSERT INTO #tmp exec sp_who @@spid

SELECT @EmployeeID=S.EmployeeID FROM Security AS S
    INNER JOIN #tmp As T ON S.LoginName=T.Loginame
    INNER JOIN Clients As C on S.EmployeeID=C.ClientID
RETURN

If I rem the above, the error does not occur - but we utilize this methodology to identify the invoking user in numerous sprocs.
FYI - once I retrieve the shape and create the complex type, I can un-rem the above and the sproc executes without error.

Is this a known issue or is there a work around for this?

[PetarP]

Hi topry,

 I was able to reproduce your case here locally. The reason for your problem is that the we are obtaining the schema result via  a data reader internal and it seems that the data reader itself cannot work correctly with that temporary table. 
At this point I can offer you 2 work arounds:
1. You can manually add a domain method return shape and add all the properties you method returns since you already know them. The problem here is that you will sacrifice the automation the stored procedure editor would provide in creating the domain method for you. 
What you will need to do is go to the model explorer right click on the domain method return shapes and select Add new Domain Method Return Shape. From there you can also add properties to the same shape. When you are done with adding the shape you can open the stored procedure editor and map your procedure to your already available return shape.
2. You can create a view that matches the shape of your stored procedure and reverse map in your model. Later on you can map your stored procedure result to return entities based on the view shape.
We understand that while this will work it is not the best solution and we will do our utmost to try and fix that problem for our future releases.
Please find your Telerik Points updated for bringing this issue to our attention.

Greetings,
Petar
the Telerik team

[Kurniadi]

Hi, I have exact same problem. I use temp table in my stored procedure. However on the domain model explorer I could not find option to add new Domain Method Return Shape. When I right click on that node all I get is Delete All option. What I have done is I created a new Domain Class as I already know what the stored procedure is going to return. Then I was able to use Store Procedure editor and selected persitance type as the Stored Procedure's return shape using the model I just created. All generated nicely. When I ran my app however, I received an error:

Invalid object name 'voa_keygen'. Telerik.OpenAccess.RT.sql.SQLException: Invalid object name 'voa_keygen'.
   at Telerik.OpenAccess.RT.Adonet2Generic.Impl.StatementImp.executeQuery(String sql)
   at OpenAccessRuntime.Relational.sql.HighLowRelationalKeyGenerator.IsCached(Connection con, RelationalKeyGeneratorInfoCache infoCache, String tab, String kCol, String vCol, String key, Boolean& ret)
   at OpenAccessRuntime.Relational.sql.HighLowRelationalKeyGenerator.init(String className, RelationalTable theClassTable, Connection con, RelationalKeyGeneratorInfoCache relationalKeyGeneratorInfoCache)
   at OpenAccessRuntime.Relational.RelationalStorageManagerFactory.init(Boolean full)

My guess was this happened because I did not have primary key in my domain class model. So I went ahead and edited my result shape domain class and used one of my fields as identity which it is. Now my app returned a different error message:

Mapping the result to instances of the class 'Namespace.Object.SearchResult' failed because the required primary key column 'risk_id' is not present

The name of my identity field is RiskId which matches the field name returned by the stored procedure. I don't understand why it is looking for risk_id.
Please can someone help point out what I have done wrong?  Do I need to map each field returned by stored procedure to the domain class's properties manually? When I clicked on the new Class diagram on the diagram area, the Mapping Details Editor at the bottom showed no mappings and I could not edit anything there to create new mappings.

[PetarP]

Hi Kurniadi,

 Can you please verify you have the correct mapping in the class you have created. The missing voa_keygen is caused because one of your classes has the Highlow key generator (or is not mapped) and this caused our runtime to search for that table.
If you map all your classes and select some other key generator everything should work as expected.
If you in fact want to use the Highlow key generator than please execute the update database wizard and modify your database in accordance to your model.

Regards,
Petar
the Telerik team

[Kurniadi]

Hi Petar,
You are correct, the class I created is not mapped. I cannot map this to anything on database because it is supposed to be a custom class for the purpose of returning stored procedure result shape. I cannot map this to the store procedure because of the error described here earlier by topry due to use of temp table in SP. I apologise for hijacking his original post. I have created another new thred on my issue.
The suggested work around to add new Domain Method Return Shape cannot be achieved because the option is not available. Hence why I created the custom class manually and edit the stored procedure to use this class as its return shape. How can I make this work Petar?

[PetarP]

Hello Kurniadi,

 When are you getting this exception? Basically you should not see it unless you have created your class on the design surface. In that case OpenAccess treats it like a real table and would require all fields that are expected to be there as per your settings. 
If you want to create a POCO class just for the stored procedure you will need to do it using the stored procedure editor as described in our help.

All the best,
Petar
the Telerik team

[Kurniadi]

Hi Petar,

The error occurs when I try to create a complex result shape using stored procedure editor as described here by topry.
To replicate this error, right click on a domain method from OpenAccess Model Explorer. Select Edit Domain Method. On the dialog box in the middle there is an option to 'Retrieve Result Shape to create a complex type'. Click that and I get a error dialog pop up which says "Unable to retieve the result shape".
In your response to topry, you suggested two work arounds. The first:
[copy and paste from below]
1. You can manually add a domain method return shape and add all the properties you method returns since you already know them. The problem here is that you will sacrifice the automation the stored procedure editor would provide in creating the domain method for you.
What you will need to do is go to the model explorer right click on the domain method return shapes and select Add new Domain Method Return Shape. From there you can also add properties to the same shape. When you are done with adding the shape you can open the stored procedure editor and map your procedure to your already available return shape.

I am having problem with the step I highlighted in bold above. When I right click on the domain method return shape, I only have option to Delete All. Can you explain how I can create a new domain method return shape manually?

[PetarP]

Hello Kurniadi,

 This option proved unstable and we had to remove it temporary that is why you are not able to locate it. Can you please share with me the backend you are using?
A possible work around would be to not use a temporary table but have a view instead. Will that work for you?

All the best,
Petar
the Telerik team

[Kurniadi]

Hi Petar,

I am in the process of upgrading an existing app and therefore not flexible as to the existing database to use. We use SQL server 2008 with lots of storedprocedures and those used for datagrid all make use of temp table.
If OpenAccess is unable to work with temp tables nor has a stable work around, then I will have to find another ORM solution.
That will be such a shame considering the efforts that have been put in to investigate its suitability to date.

[PetarP]

Hello Kurniadi,

 Currently the only workaround I can provide you with is to manually edit the rlinq file and add the required entities in xml format. This will force our code generation to create some methods for it. The other way would be to just create the entities on hand.
Since the Q2 release is right around the corner we will try to provide some improvements there so that you are able to create a domain method return shape from the dsl itself.

All the best,
Petar
the Telerik team

[Alois]

Hi Petar,

in the meantime, is it possible with OpenAccess ORM 2012 Q3 Release to access stored procedure
with temp tables?

A.Kleine

[PetarP]

Hello Alois,

 I am afraid that no improvements regarding tempt tables have been made for the Q3 release. How important is that for you and your project? If that is something that is blocking for you we will definitely try to increase the priority of this feature request.

Greetings,
Petar
the Telerik team

[Alois]

Hello Petar,

for my projects this means a no go for using telerik orm.

I my projects the procedures makes a lot of use of temp tables.

Actually I use other OR mapper.

If this would be fixed I would be happy, because the rest I found very interesting eg. webapi, etc.

A.Kleine

[Ivailo]

Hello Alois,

While we cannot provide a time-frame for developing this feature, we encourage you to vote for the Public Issue Tracking System item we have created to get maximum feedback while we are planning the future releases.


Kind regards,

Ivailo
the Telerik team