Row count but no Fields returned from Stored Procedure in Report wizard

[Mike]

I am trying to create a simple mailing label report. I have gone through the wizard, selected a SQLDataSource, selected a procedure, added the 5 parameters and when I hit the Execute Query button I get a row count of 190 (which is correct) but no data is displayed in the wizard. When I continue on to the report, when I click on 'fields' in the expression builder, it tells me there is no datasource. When I look at the properties of the report, the datasource is filled in with the one I selected in the wizard.

[Mike]

I forgot to mention, the procedures are not in the 'dbo' schema. Not sure if that matters. And I am using Q1 2010 4.0.10.423

[Steve]

Hello Mike,

You have not mentioned what database you're using, but either way we made various fixes and improvements to the Data Source Components in the latest Q2 SP1, so we highly recommend that you upgrade to it and if still having problems, provide more information about setup, database and anything you consider relevant.

Regards,
Steve
the Telerik team

[Mike]

I am connecting to SQL 2008 on the local dev box.
I attached a screen shot of the dialog box when I click execute query... and the expression Dialog and the Report Properties.
Financially I connot upgrade at this point. My subcription ran out Q1 2010 and I cant afford the renewal right now.
Any Ideas/workarounds would be greatly appreciated.
The procedure I am calling is a driver proc that calls one of three others based on the parameters. Would that be an issue?

[Mike]

The data will display on the report as long as you type in the expression for the field name. You just don't have the ability to pick from the field names in the expression builder or drag drop from the field list. So it is more of a nusiance than anything else. If you have any ideas on how to get the fields to show up in the designer that would be great.

[Steve]

Hello Mike,

Are you by any chance using temp tables in your stored procedures? If this is the case, check the following forum post: Temporary Table in Stored Proc.
If not, download the trial version of Q2 SP1 and see if the problem is resolved and we could think of something for the upgrade.

Best wishes,
Steve
the Telerik team

[Mike]

Yep, the driver proc uses a temp table to do additional manipulation to the data after the inner procedure runs. I tried the work around from the post you mentioned and viola there they are.
I'll download the latest and then remove the work around and see if that does anything.
Either way, thanks a lot.

 Mike

[Mike]

I just un-installed Q1 2010 and installed Q2 2010 trial and it appears the issue is partly resolved in the new version.
I recompiled the proc w/o the workaround before starting a test solution for the report.
Picked the same proc for the report ant the execute query did show the field data, but the expression builder still does not display the fields, it only displays - No Data Source

[Steve]

Hello Mike,

Unfortunately that is expected as we cannot get the schema which is needed to show the data source fields. The execute query step works, because previously we have been trying to get the schema there as well, but now we simply bind the grid and it "autogenerates" the columns. There is no easy way to address this problem, especially having in mind that this is a problem of the Microsoft Data Set Designer and not ours. Nevertheless our developers are still looking for out of the box solution to this problem and we hope to have something for Q3. If not, at least a workaround is available.

Greetings,
Steve
the Telerik team

[Eric Moore]

That worked great, thanks! I had just changed my sp to use a temp table and I was banging my head against the wall trying to figure out why it was no longer showing any data in the designer

[Brent]

I am having a similar issue. I'm using Telerik Reporting v5.3 trial version. I've applied the SQL code "SET FMTONLY OFF" to our stored procedures. This made the column names and data types available within the Data Explorer and the Expression Editor. Unfornately, when I go into preview mode, the process breaks down.

I'm receiving an error stating the expression contains the object 'fieldname' that is not defined in the current context. It appears in every column & row. When I change the SQL Data Source to use a SQL view, everything works as correctly. Within this stored procedure, there are no temp tables in use, however, there is an inner EXEC statement.  Here is the code:

SET

 

FMTONLY OFF;

DECLARE

 

@CMD NVARCHAR(MAX);

 

 

 

SELECT @CMD =

 

'SET FMTONLY OFF;

SET NOCOUNT ON;

SET IMPLICIT_TRANSACTIONS OFF;

SET XACT_ABORT ON;

'

 

+ [QUERY_TEXT]

 

 

FROM [dbo].[TSYS_SW_REPORT_REQUESTS] WITH (NOLOCK)

 

 

WHERE [REQUEST_ID] = @REQUEST_ID AND

 

  [AGENCY_ID]

= @AGENCY_ID;

 

 

EXEC(@CMD);

 

In this test case, the result of the above query is the following code:
 

SET FMTONLY OFF;

SET NOCOUNT ON;

SET IMPLICIT_TRANSACTIONS OFF;

SET XACT_ABORT ON;

SELECT TOP 1000 * FROM VW_IMPORT_HISTORY  WHERE AGENCY_ID = 16 ORDER BY ACTION_START_TIME DESC

As you can see, there are no temp tables. Only a nested execute statement.
 
As we are evaluating this product for purchase, we must have the functionality to support stored procedures (temp tables included.) Please, any help you can give will be greatly appreciated.

[Steve]

Hi Brent,

The provided info is insufficient for us to determine where the problem might be. Your select statement does not show us the columns, nor you given the exact column that cannot be found. The problems described in this thread so far is about missing data source fields/columns in the Report Designer (which you have working already) and if you hardcode a column in a TextBox expression like this = Fields.MyColumn then it works at runtime, which is where you're having problems, correct?

From your description it seems you cannot get data when previewing the report, what about when running the actual application? Did you set the SqlDataSource component as DataSource for the report?
If you continue to have problems with this, please open a support ticket and send us a runnable project that exhibits the issue and we would look into it.

Greetings,
Steve
the Telerik team