Actually this is a problem. I'm having the same issue and there is nothing wrong with the stored procedure. The issue is in particular coming from when building a sql string and then doing

exec

sp_executesql @sql just like they do in _GetPaged stored procedures.

I found that in file TemplateLib\CommonSqlCode.cs function GetReturnCustomProcReturnType that the code has changed from the previous versions. They added another if test in the else "else if (command.CommandResults != null && command.CommandResults.Count > 0)". Where as before it was just plain else.

I've had to add to bottom of my custom stored procure select 'dummy' to fake a table. I also tried Select @@RowCount, but after generating the code the stored procedure had been re-written and Select @@RowCount has been deleted.

Anybody else having issues or have another solution besides adding select 'dummy' to bottom of stored procedure? I could edit the Template file, but don't want to.

It has to with how CodeSmith executes the stored procedure to determine the result set.

Also, what version of the templates are you using (check changes.log file)? The reason I ask is that there used to be bug where custom procedures were being dropped and re-created when the templates were executed. This was fixed a while back, so you may want to update to a new build of netTiers.

BenJohnson------------------------------ Member of the .NetTiers team Visit http://www.nettiers.com------------------------------

We are using the latest public release, v2.0.1.471. I would rather stick with official public release then get nightly builds, but if this has been fixed or straight forward work around then I would consider using a latest nightly build.

It doesn't sound like there is anything really to 'fix' due to the limitations of SET FMTONLY ON not being able to intepret dynamic sql string. So perhaps another solution would be to add another check for maybe this naming convention _<TableName>_DS_<GetMethod> when a _DS_ is found then a dataset is just returned. We modified a older version of the templates to do this about 2 years ago. With version 2(?) of the templates it automatically detected dataset or TList which worked for us, so we didn't re-modify the template again. So without doing some hack like adding Select @@RowCount or Select 'Dummy' to bottom of procedure we might have to modify templates to check for _DS_. Of course I rather not have to do that because we'd continually have to modify templates until something was put in public release.

Anybody had any luck with getting your own customizations that might make sense for the general public added into the public release? I've also added a very cool feature that I'd love to see in there. I added a automatic compile the generated code and then copy to a shared location for other developers to reference the dlls. I added a property to save the path to the shared location and a true/false to even do it.

Actually the other option to 'fix' this would be to change the Else block in TemplateLib\CommonSqlCode.cs function GetReturnCustomProcReturnType. Remove "else if (command.CommandResults != null && command.CommandResults.Count > 0)" and just go back to "else". But I'm sure that would break something somebody is trying to use a void for.

But this is why it hasn't been a problem for us in the older versions of the templates.