Hi

I currently have the following issue and hopefully someone can assist.

We trieve information from our clients database via Store Procedures only. The initial setup was fine and all was working. Our client then had to update some of these Store Procedures. After I refreshed the relational model data all seemed fine initial until I proceeded to build my appication. I got an error where one of the Store Procedures no longer seems to exist in the RetrievalProcedures. This Store Procedure worked initially but after the changes were made then the mentioned issue occured.

After connecting to our clients database and executing this Store Procedure results are returned. I noticed that on this particular Store Procedure there was no ResultSets present, so I proceeded to refresh the relational model data again. Upon the final step where one specifies the Store Procedures for which the resultset relational model has to be determined, the associated Store Procedure is not checked. No matter how many times I re-check it, it always remains unchecked.

Could this be the cause of the issue and that although the Store Procedure can be executed in the Database that there may still be some small issue that is preventing me from getting the updated Store Procedure?

Thanks to anyone who can assist or at least give me some pointers in resolving this issue.

Regards,

Hi Daz77,

What LLBLGen version are you using? (http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7722)

Daz77 wrote:

Upon the final step where one specifies the Store Procedures for which the resultset relational model has to be determined, the associated Store Procedure is not checked. No matter how many times I re-check it, it always remains unchecked.

Please elaborate more on this. So, you check the SP to retrieve the resulset metadata, then what happens? Is the metadata retrieved? Then in next refreshes the SP is unchecked again?

Hi Daelmo

As requested: Version: 3.1 final

As for the steps:

1) I am refreshing the relational Model Data from the Database, 2) All Store Procedures for the specified elements to be retrieved are checked (these remained checked each time I update the project) 3) The issue is with the next step and that is specifying the Store Procedures for which the resultset relational model data has to be determined, one Store Procedure always remains unchecked even after re-checking it. It is this Store Procedure that is then missing when trying to call it using "RetrievalProcedures.XXXX". Basically I get an error message stating that there is no definition for XXXX.

The above used to work fine until as mentioned our client updated a few of the store procedures. And as mentioned as well the store procedure can be executed via MS Studio.

Inform me if you need any other information or if I need to elaborate even more if possible. Thanks for the assistance,

Skip step 3. And then Open the CatalogExplorer right click on the SP and and "Set Number of ResultSets" pick 1 or 2 as appropriate.

Then save the project and try again with the refresh including step 3.

Hi Walaa

Thank you for the feedback.

I did as mentioned but the Store Procedure has no resultsets after completing the steps, only the parameters?

Is it possible that the issue could be on the database where the Store Procedure is retrieved from even if one can excute it?

Another Store Procedure was changed and all went well in terms of updating it on our side. It is just this one that is causing issues.

Regards,

Could you please attach the database and the project file. You can do this in a helpdesk thread, for privacy.

FIRST please check the application output window in the designer after you refreshed the catalog and check whether it reports an error of some kind related to your stored procedure.

Hi Otis

Thank you for the feedback.

I checked the Application Output and there is an error:

Stored procedure 'SSS_GetPublicTeams' caused an exception during resultset retrieval. Its resultsets (if any) probably aren't determined in full: Conversion failed when converting from a character string to uniqueidentifier.

Could it then be that as mentioned the issue is with the Store Procedure on the clients database even if it can be executed without any issues? Or is there something else I can perhaps do.

thanks,

LLBLGen tries to execute the SP to reveal its resulset. It's executed with NULL as parameteres and directives that allow to show the metadata of the resulset. However, for some reason the SP crashes somewhere when it's executed with NULL params. Related thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=18967

Please attach your project and the DLL script. Its better if you can reduce it to the problematic SPs only and still reproducible. You can do this in a HelpDesk thread, which is private.

Hi Daelmo

Please find attached the requested information.

Regards,

Hi Daz,

Your LLBLGen project doesn't have any entities and tables. If I try to create an script from your project, it wont contain any table. Obviously, if I execute the SP from SQLManager with NULL parameters it will fail:

Msg 208, Level 16, State 1, Procedure SSS_GetPublicTeams, Line 16
Invalid object name 'Website_TeamProfiles'.

... because Website_TeamProfiles doesn't exist. Maybe that is the problem, I don't know. Please try to execute the SP from your SQLManager with NULL parameters to see if it crashes there. If it crashes, then that's why LLBLGen can't execute it either.

If you want us to reproduce, please attach the full DDL script, at least enough so the SP run fine.

Hi

Thank you for the feedback.

The reason for there being no entities/tables is that we are only using LLBLGen to retrieve SP's from our clients database on their server. Regarding the SP, the reason that it would fail if you run in on your SQLManager is that it references other databases on the clients server. When executing this SP on the clients server then all is fine. I unfortunately only have access to the database that contains these SP's only. I apologise for this as in my haste last week I totally forgot about the above mentioned regarding the SP's.

If the SP parameter was not to be null, would this may resolve the issue? We also have the new version of 3.5 for LLBLGen, would this perhaps resolve the issue if I was to upgrade (infact we will be upgrading all our current projects anyway)?

Regards,

Daz77 wrote:

Hi

Thank you for the feedback.

The reason for there being no entities/tables is that we are only using LLBLGen to retrieve SP's from our clients database on their server. Regarding the SP, the reason that it would fail if you run in on your SQLManager is that it references other databases on the clients server. When executing this SP on the clients server then all is fine. I unfortunately only have access to the database that contains these SP's only. I apologise for this as in my haste last week I totally forgot about the above mentioned regarding the SP's.

The error given in the application output window shows that the 'execution' of the procedure gives the error in the convert. As the parameter is an nvarchar, the driver will pass an empty string as the parameter value, as it can only assume it is a string parameter. The first thing you do in the proc is convert a guid from the string, which fails as the string is an empty string and thus not a valid guid. As this fails, the proc doesn't return a resultset and therefore the driver skips it.

The problem with this procedure is however that for 1 particular input it returns 2 resultsets, while in the other situations it returns 1. This won't work with the designer, as it won't specify the input necessary to produce 1 or 2 resultsets.

So you should manually specify '2' as the # of resultsets by right-clicking the procedure in catalog explorer and then select 'Set number of resultsets'. If you don't, you'll get 0 or as I see you already set it to 1.

If the SP parameter was not to be null, would this may resolve the issue? We also have the new version of 3.5 for LLBLGen, would this perhaps resolve the issue if I was to upgrade (infact we will be upgrading all our current projects anyway)?

Regards,

No, the problem will be the same as your proc chokes on the input the driver passes as parameter value (""), which fails in the convert. As I described above, it'll be problematic anyway to get a typed resultset from this, so use the untyped resultset feature, i.e. set the # of resultsets to 2.