This is a migrated thread and some comments may be shown as answers.

Report using stored procedure with dynamic query

6 Answers 442 Views
Report Designer (standalone)
This is a migrated thread and some comments may be shown as answers.
Priya
Top achievements
Rank 1
Priya asked on 17 Aug 2015, 11:40 PM

I am using the Telerik standalone Report designer - and connecting to SQL database. Tried to link to a stored procedure with dynamic query. The stored procedure executes fine and returns the results in SQL database.

 

Even "Execute Query" on the Telerik report wizard works fine and returns data rows.

When trying to add as a Telerik datasource it fails throwing the following error

 System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'UNION'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Telerik.Reporting.Processing.Data.SqlSchemaAdapter.<GetFields>d__1.MoveNext()
   at Telerik.Reporting.Processing.Data.SimpleSchemaAdapter.Fill(SimpleDataModel model)
   at Telerik.Reporting.Processing.Data.SimpleDataProvider`1.GetSchemaCore()
   at Telerik.Reporting.Processing.Data.DataProvider`1.Telerik.Reporting.Processing.Data.IDataProvider.GetSchema()
   at Telerik.Reporting.Data.Design.DataSourceDesignerBase.Telerik.Reporting.Design.Interfaces.IDataSchemaProvider.GetSchema()
   at Telerik.Reporting.Data.Design.DataSourceService.GetSchemaFromContext(Object context)
   at Telerik.Reporting.Design.DataExplorerControl.<>c__DisplayClass2.<RefreshDataSource>b__0()
ClientConnectionId:b24a70f0-fdac-4dda-94a8-a732936c2ad9
Error Number:156,State:1,Class:15

6 Answers, 1 is accepted

Sort by
0
Stef
Telerik team
answered on 20 Aug 2015, 07:58 AM
Hi Priya,

The reporting engine uses standard ADO.NET classes, the installed on the machine data providers and the provided connection string.

After configuring a SqlDataSource component, the Data Explorer needs to obtain the data schema in order to load the available fields. If the query does not return always the same schema, there might be errors on the attempt for loading the fields.


In order to investigate further the problem, please send us the SQL query used in the report that causes the problem.

Regards,
Stef
Telerik
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Priya
Top achievements
Rank 1
answered on 23 Aug 2015, 11:25 PM

Hi Stef,

 

I have attached the dynamic stored procedure and the table structure for the Query.

 It basically loops the column numbers and fetches the Components and SuppliedBy values as Rows.

 Stored Procedures:

CREATE PROCEDURE [dbo].[usp_GetQuoteComponents]
(
@P_QuoteNo VARCHAR(50),
@P_QuoteVersion VARCHAR(5) 
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @COLNO INT, @ARTNO VARCHAR(2), @SQL_QRY VARCHAR(MAX), @P_JobNo VARCHAR(50)

SET @SQL_QRY = ''
SET @COLNO = 1

WHILE @COLNO <= 20
BEGIN

SET @ARTNO = RIGHT('0' + CONVERT(VARCHAR, @COLNO), 2)

IF @SQL_QRY <> '' 
SET @SQL_QRY = @SQL_QRY + ' UNION '

SET @SQL_QRY = @SQL_QRY + ' SELECT COMPONENTS' + @ARTNO + ' AS ''Description'', SUPPLIEDBY'+ @ARTNO +' AS ''Supplied By'', CONVERT(INT, '''+@ARTNO+''') AS SeqNo FROM IDM_QUOTE_COMPONENTS WHERE QUOTENO = ''' + @P_QuoteNo + ''' AND QUOTEVERSION = ''' + @P_QuoteVersion + ''' AND LEN( COMPONENTS' + @ARTNO + ') > 0'

--SELECT @SQL_QRY



SET @COLNO = @COLNO + 1
END

SET @SQL_QRY = @SQL_QRY + ' ORDER BY SeqNo'

EXEC (@SQL_QRY)

END

 

 Table Structure:

CREATE TABLE [dbo].[IDM_QUOTE_COMPONENTS](
[QUOTENO] [varchar](50) NULL,
[PRESORTARTICLE] [varchar](max) NULL,
[PRINTPOSTARTICLE] [varchar](max) NULL,
[PRESORTRESPRICE] [varchar](50) NULL,
[PRESORTURESPRICE] [varchar](50) NULL,
[PRINTPOSTSAMEPRICE] [varchar](50) NULL,
[PRINTPOSTOTHPRICE] [varchar](50) NULL,
[GRANDTOTAL] [varchar](50) NULL,
[DATAQUANTITY] [varchar](50) NULL,
[COMPONENTS01] [varchar](max) NULL,
[COMPONENTS02] [varchar](max) NULL,
[COMPONENTS03] [varchar](max) NULL,
[COMPONENTS04] [varchar](max) NULL,
[COMPONENTS05] [varchar](max) NULL,
[COMPONENTS06] [varchar](max) NULL,
[COMPONENTS07] [varchar](max) NULL,
[COMPONENTS08] [varchar](max) NULL,
[COMPONENTS09] [varchar](max) NULL,
[COMPONENTS10] [varchar](max) NULL,
[COMPONENTS11] [varchar](max) NULL,
[COMPONENTS12] [varchar](max) NULL,
[COMPONENTS13] [varchar](max) NULL,
[COMPONENTS14] [varchar](max) NULL,
[COMPONENTS15] [varchar](max) NULL,
[COMPONENTS16] [varchar](max) NULL,
[COMPONENTS17] [varchar](max) NULL,
[COMPONENTS18] [varchar](max) NULL,
[COMPONENTS19] [varchar](max) NULL,
[COMPONENTS20] [varchar](max) NULL,
[SUPPLIEDBY01] [varchar](max) NULL,
[SUPPLIEDBY02] [varchar](max) NULL,
[SUPPLIEDBY03] [varchar](max) NULL,
[SUPPLIEDBY04] [varchar](max) NULL,
[SUPPLIEDBY05] [varchar](max) NULL,
[SUPPLIEDBY06] [varchar](max) NULL,
[SUPPLIEDBY07] [varchar](max) NULL,
[SUPPLIEDBY08] [varchar](max) NULL,
[SUPPLIEDBY09] [varchar](max) NULL,
[SUPPLIEDBY10] [varchar](max) NULL,
[SUPPLIEDBY11] [varchar](max) NULL,
[SUPPLIEDBY12] [varchar](max) NULL,
[SUPPLIEDBY13] [varchar](max) NULL,
[SUPPLIEDBY14] [varchar](max) NULL,
[SUPPLIEDBY15] [varchar](max) NULL,
[SUPPLIEDBY16] [varchar](max) NULL,
[SUPPLIEDBY17] [varchar](max) NULL,
[SUPPLIEDBY18] [varchar](max) NULL,
[SUPPLIEDBY19] [varchar](max) NULL,
[SUPPLIEDBY20] [varchar](max) NULL,
[CREATIONDATE] [varchar](50) NULL,
[PRESORTRESPRICESURFACE] [varchar](50) NULL,
[PRESORTURESPRICESURFACE] [varchar](50) NULL,
[LODGEMENTACCOUNT] [varchar](50) NULL,
[QUOTEVERSION] [varchar](5) NULL
) ON [PRIMARY]

GO

0
Stef
Telerik team
answered on 26 Aug 2015, 03:14 PM
Hi Priya,

After checking with SQL profile what is executed on loading the fields in the Data Explorer, it appears the schema cannot be obtained from the dynamic query without adding the following lines in the beginning of the stored procedure:
IF 1=0 BEGIN
     SET FMTONLY OFF
   END

Please test the above setting and let us know if you need any further help.


Regards,
Stef
Telerik
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Marcel Rossi
Top achievements
Rank 2
answered on 11 Jun 2020, 04:12 PM

This 3 "magic Lines" solved also my problem.

 

I tried to use a Procedure with multiple RecordSets. The Procedure can return each single ResultSet as a single Result, by setting a Parameter on calling the Procedure. For Example @ResultSet = 3.

 

Before this Fix, the Report Designer showed always the Columns/Fields of the first ResultSet. Even if the parameter for "Set 3" was set.

 

Now it's working perfect!

The Report Designer shows the Columns for the requested ResultSet.

 

Thanks!

0
devdutt
Top achievements
Rank 1
answered on 07 Sep 2020, 08:38 AM
Really That's working fine..Thanks for Supporting
0
Roberto
Top achievements
Rank 1
answered on 14 Apr 2021, 02:34 PM

 BEGIN
     SET FMTONLY OFF
   END

 

Working fine to me.

Thanks

Tags
Report Designer (standalone)
Asked by
Priya
Top achievements
Rank 1
Answers by
Stef
Telerik team
Priya
Top achievements
Rank 1
Marcel Rossi
Top achievements
Rank 2
devdutt
Top achievements
Rank 1
Roberto
Top achievements
Rank 1
Share this question
or