Report using stored procedure with dynamic query

4 posts, 0 answers
  1. Priya
    Priya avatar
    2 posts
    Member since:
    Aug 2015

    Posted 17 Aug 2015 Link to this post

    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

  2. Stef
    Admin
    Stef avatar
    3036 posts

    Posted 20 Aug 2015 Link to this post

    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
  3. DevCraft banner
  4. Priya
    Priya avatar
    2 posts
    Member since:
    Aug 2015

    Posted 23 Aug 2015 in reply to Stef Link to this post

    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

  5. Stef
    Admin
    Stef avatar
    3036 posts

    Posted 26 Aug 2015 Link to this post

    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
Back to Top