no field selection if using temp table (not SP)

[Robert]

Hi,

 I have a SalDataSource, and I ​wrote queries inside there instead of store procedure. Everything works, the reports generates without any issue but in the report design, I can not see the list of the fields, I need to type it manually, is there anyway to get around it so I can see the fields because my query is very complicated and I need to use temp tables, moreover, the project team decide not use any store procedure. thanks

 To make is simple, here is the sample to reproduce the issue:

1. sqldatascource =

"select * into #abc FROM tableAbc

select * from #abc"

2. In the "field expression", I want to display the value in fieldD column by using : "Fields.fieldD" but it is empty, I cannot click on anything. However, I can manually type it and the report works without any issue.

 

thanks!

 

[Stef]

Hi Robert,

Please test the settings from the How to configure Stored Procedure with Temporary Tables for use with SqlDataSource component KB article.

Regards,
Stef
Telerik

[Robert]

Thank you but these are two different issues. 

"SET FMTONLY OFF" is related to the Store Procedure with SQL and I tried it few weeks ago and it didn't work. However, my issue is that the report runs and I can see the data, but not displaying fields in the design view. I think this is a bug from Telerik.

[Stef]

Hello Robert,

Fields will not be listed in the Expression editor or the Data Explorer if the data schema cannot be obtained (due to settings of the stored procedure), or if the configured data source component is not bound to the corresponding data item.

If you need further help, please post the code for generating the report and the stored procedure create statement.

Regards,
Stef
Telerik

[Robert]

Thanks for your reply but I think you may misunderstand the question. This issue happens by "NOT" using Store Procedure; therefore, this is "NOT" a "Store Procedure" related question/bug.

 I am ​NOT using the Store Procedure so I have no create statement to post. I just use the regular SQL statement in the sqldatasource. I have posted how to reproduce the issue in the first post but I will post again.

 Thanks,

 

 To make is simple, here is the sample to reproduce the issue:
1. sqldatascource =
"

select * into #abc FROM tableAbc
select * from #abc

"
2. In the "field expression", I want to display the value in fieldD column. (There is no list of fields for me to double click)

 

[Stef]

Hi Robert,

The settings suggested in the How to configure Stored Procedure with Temporary Tables for use with SqlDataSource component KB article are related to the usage of temporary tables in general, not to stored procedures. Data schema cannot be obtained from temporary objects unless you use the settings forcing the SQL server to return such scheme.

The following query can be executed successfully and the Data Explorer loads the available fields. Tested with Telerik Reporting Q2 2015 SP1 v9.1.15.731:

SET NOCOUNT ON;
    IF 1=0 BEGIN
      SET FMTONLY OFF
    END

   CREATE TABLE #Temp
   (
       ProductID   integer NOT NULL,
       Name        nvarchar(50) COLLATE DATABASE_DEFAULT NOT NULL
   );

   INSERT INTO #Temp
       ([ProductID], [Name])
   SELECT
       p.[ProductID], p.[Name]
   FROM Production.Product AS p

   SELECT
       t.[Name], t.[ProductID]
   FROM #Temp AS t

   DROP TABLE #Temp;

I hope this helps you.


Regards,
Stef
Telerik

[Robert]

I forget to try "CREATE TABLE #Temp". I just create temp table by using "into". I am going to try that.

thanks.