I've just upgraded from the version 2 beta 2 to version 22.214.171.1247.
I have several custom stored procs in my app that contain construct sql dynamically and retrun the result by calling EXEC sp_executesql...
In the beta 2 version for my procedure "usp_Address_Lookup" .netTiers generated the corresponding method "IDataReader Lookup(...)" which is what I want.
In version 126.96.36.1997 for the same procedure the return type is void instead of IDataReader i.e. "void Lookup(...)".
This seems like a bug to me. Below is one of the stored procs that I'm having this problem with:
-------ALTER procedure [dbo].[usp_Address_Lookup](@Info varchar(100) = NULL,@Unit varchar(50) = NULL,@Lot varchar(50) = NULL,@Number varchar(50) = NULL,@Street varchar(100) = NULL,@Suburb varchar(100) = NULL,@State varchar(10) = NULL,@Postcode varchar(10) = NULL,@MaxRows int = 10,@Debug bit = 0)asdeclare @sql nvarchar(max), @paramlist nvarchar(max)
select @sql = 'SELECT TOP ' + CONVERT(varchar(10), @MaxRows) + ' '
select @sql = @sql + 'AddressId, Info, Unit, Lot, Number, Street, Suburb, StateProvince, Postcode from Address'
select @sql = @sql + ' WHERE 1 = 1'
if (@Info IS NOT NULL) AND (@Info <> '') select @sql = @sql + ' AND Address.Info LIKE @xInfo + ''%'''
if (@Unit IS NOT NULL) AND (@Unit <> '') select @sql = @sql + ' AND Address.Unit LIKE ''%'' + @xUnit + ''%'''
if (@Lot IS NOT NULL) AND (@Lot <> '') select @sql = @sql + ' AND Address.Lot LIKE ''%'' + @xLot + ''%'''
if (@Number IS NOT NULL) AND (@Number <> '') select @sql = @sql + ' AND Address.Number LIKE ''%'' + @xNumber + ''%'''
if (@Street IS NOT NULL) AND (@Street <> '') select @sql = @sql + ' AND Address.Street LIKE @xStreet + ''%'''
if (@Suburb IS NOT NULL) AND (@Suburb <> '') select @sql = @sql + ' AND Address.Suburb LIKE @xSuburb + ''%'''
if (@State IS NOT NULL) AND (@State <> '') select @sql = @sql + ' AND Address.StateProvince LIKE @xState + ''%'''
if (@Postcode IS NOT NULL) AND (@Postcode <> '') select @sql = @sql + ' AND Address.Postcode LIKE @xPostcode + ''%'''
select @paramlist = '@xInfo varchar(100), @xUnit varchar(50), @xLot varchar(50), @xNumber varchar(50), @xStreet varchar(100), @xSuburb varchar(100), @xState varchar(100), @xPostcode varchar(100)'
if @Debug = 1 print @sql
EXEC sp_executesql @sql, @paramlist, @Info, @Unit, @Lot, @Number, @Street, @Suburb, @State, @Postcode-------
Any ideas? It seems to me that netTiers is assuming that this proc does not return any rows. Is there something else I need to specify in the proc to let netTiers know what to expect as a result?
Please take a look at the following thread for more information: http://community.codesmithtools.com/support_forums1/f/9/t/11437.aspx. The key is to check the result set being returned by the following queries. The NULL Statements need to be added for each parameter of your stored procedure.
SET FMTONLY ON
SET FMTONLY OFF
-- Table Valued Function..
SET FMTONLY ON
SELECT * FROM [dbo].[sql_function] (NULL)
SET FMTONLY OFF
I would check to see if each parameter is null and if so return a set of columns like select * from dbo.tablename... Sometimes, SQL Server has a hard time parsing the results. If this is the case, this is the only work around to solving this issue. As with the post above, FMTONLY is somewhat limited and will evaluate all queries that are inside of if logic..
If configuring your stored procedure and debugging it doesn't pan out (very rarely is this the case ). The only way to modify what is generated is to manually update the templates to account for this stored procedure.
@GRIER, you would need to specify the TotalRowCount as an output parameter in the actual stored procedure parameter definition.
CodeSmith Tools, LLC. Software Development Engineer
.NetTiers team | Visit http://www.nettiers.com
When CodeSmith tries to discover the type of resultset returned from stored procs, it does so by issuing the SET FMTONLY ON command to SQL Server. When this option is activated, the procedures are run with limited rights and therefore your call to an external stored procedure is not executed. See this thread for more info:
You can check for a user_name() is null to determine if this is the case and return a sample record so that the proper metadata is returned to CodeSmith. For example, you can add this to the begining of your procedure:
if user_name() is nullbegin select AddressId, Info, Unit, Lot, Number, Street, Suburb, StateProvince, Postcode from Address where 1=0 returnend
Hope that helps!
That fixed it!Apreciate your help Bobby!
I have a very similar sp, but wen i try to generate the code with nettier codesmith tell me that the sp is not in the expected format.
I really don't know what to do...
Still seeing this issue with Net Tiers 2.2 ... is this a 'live one'?
Returning a dummy parameter list doesn't fix it for me...
I used the dummy code and I am now returning an IDataReader. I also want this method to be able to return a @TotalRowCount variable as well. The value is being set properly in the SPROC and the SQL Profiler looks like this:
declare @p6 intset @p6=33exec dbo._Listing_GetSearchResults @VisitorID='D35E2D4B-CA34-4DBB-A0C0-47DA1C8DAF9F',@WhereClause='Listing.BoardID = 1 AND AreaID IN(480)',@OrderBy='listprice DESC',@PageIndex=0,@PageSize=20,@TotalRowCount=@p6 outputselect @p6
Again, it IS populating the DataReader, but is not returning the @TotalRowCount... which, in this case is 33.