Custom view sprocs have wrong return type (not VList, but DataSet)

[GoogleCodeExporter]

What steps will reproduce the problem?
1. Create a custom sproc based on a view
2. Generate build 739 

What is the expected output? What do you see instead?
Return type should be VList<Entity>, but is DataSet.
(This worked as expected in build 662)

What version of .netTiers and CodeSmith are you using?
nettiers: 2.3.739, CodeSmith: 4.1

Additiona info:
Changing line 4686 in build 739 from:
if (command.CommandResults.Count != 1)

back to:
if (command.CommandResults.Count == 0)

fixes the problem.

Original issue reported on code.google.com by ejls...@hotmail.com on 16 Jun 2008 at 9:30

[GoogleCodeExporter]

Tested it and its working fine for me. Could you please give more details on 
this?
If possible, post the view schema and the procedure you want to create. Thanks.

Original comment by vbandr...@gmail.com on 20 Jul 2008 at 11:56

[GoogleCodeExporter]

This is usually happening when you create a custom proc after altering a View.
To avoid the problem, you must execute a sp_refreshview 'myView'.
We use to run a query that refresh all the views of our database before any 
generation.

Original comment by jrol...@gmail.com on 21 Jul 2008 at 7:21

[GoogleCodeExporter]

I have never had issues with this.  I also cannot recreate this.  Unless anyone 
objects, I am going to close this one.

jroland, could this be an indexed view issue?  Do you know why you have to run 
that?  Is it a Sql Bug?

Original comment by jmhin...@gmail.com on 28 Jul 2008 at 12:30

[GoogleCodeExporter]

Yes it is a sql behavior, it keep the schema of the view "cached" until a
sp_refreshview 'myView' is executed.

to reproduce the error:
- create a view 'MyView' with 2 columns
- create a custom proc like this one:
create view _MyView_Random
AS
select * from MyView order by newid()
GO
- generate the view with codesmith / nettiers
- alter the view by adding a new column
- generate again
- you should see that the custom proc is now generate in the way to return a 
DataSet
instead of a VList<MyView>

My solution is to execute this query before any generation:

-----------------------------------------------
select 
name as vname
into #temp
from sysobjects 
where 
    xtype = 'V'

declare my_cursor Cursor
for 
select vname from #temp
open my_cursor
declare @Name varchar(1024)
fetch next from my_cursor into @Name
while (@@Fetch_status <> -1)
begin
    exec sp_refreshview @ViewName = @Name
    -- print @Name
    fetch next from my_cursor into @Name
end
close my_cursor
deallocate my_cursor
-------------------------------------------------

I also force a recompilation of the templates.

Original comment by jrol...@gmail.com on 28 Jul 2008 at 7:32

[GoogleCodeExporter]

There are two possible problems here:
1st - You changed the view or the cust sp and the cust sp is not returning the 
same
fields as in the vw
2nd - The schema of the database is being cached by the nettiers template and 
the
change of the vw or sp is not the real one. The columns returned are not the 
same
than the ones on the vw.
Those are the only two reasons why you may not be able to get the list of 
entities of
the view and you are getting a dataset instead.

Solution >> Recompile your nettiers template, refresh the schema database by
selecting a different one and selecting it again so that it will take the new 
schema
changes.

Hope this helped you on solving the issue :)

Regards,
DiegoGravi

Original comment by diegogr...@gmail.com on 21 Aug 2008 at 12:31

[GoogleCodeExporter]

Please note that this problem only occurs because of a code change in 
CommonSqlCode.cs, as noted in the initial post. Reverting this change fixes the 
problem!

Original comment by ejls...@hotmail.com on 21 Aug 2008 at 1:38

[GoogleCodeExporter]

It's becuase some custom procs will return more than 1 result.

Custom view stored procedures need to return only 1 result.  Most that I hav 
seen 
return the @@rowcount that some of the Nettiers procedures do.  The code does 
nothing with @@rowcount so take it out.

closing this issue.

Original comment by jmhin...@gmail.com on 6 Sep 2008 at 8:23

• Changed state: WontFix

[GoogleCodeExporter]

Yes, we had SELECT @@ROWCOUNT... Thanks for clarifying this.

Original comment by ejls...@hotmail.com on 10 Sep 2008 at 9:42