Skip to Content

I have created a stored procedure in MS SQL Server 2005 with one input parameter and three output parameters.  I have executed the query successfully in SQL Server.   Now I am trying to built a report using this stored procedure.  When I select the stored procedure in Crystal Reports database expert  it prompts for the input and output parameters.  When I enter them and press OK then I get the following error.

Database Connector Error:

‘Cannot obtain error message from server.’

My first question is how should I pass the output parameter, I have declared the output parameters as VARCHAR(10) in stored procedure in SQL server.

So, do I have to pass it as for eg. @rate OR {?rate} OR rate.

I don’t think I have any problems with ODBC drivers or Grants. 

If I create a stored procedure with on output paratmeters, I have no issues.  I am able to add it to the datasource and get the data in the report.

I feel I am having issue only with the output parameters.  Did I miss anything? 

Can anyone please help me.

Thanks

To report this post you need to login first.

12 Comments

You must be Logged on to comment or reply to a post.

  1. Zahid Hameed

    If it is happening with Views as well then  follow these steps to avoid this Error:

    right click on Crystal Reports link in Programe Files

    and Select Run as Administrator

    i hope this will work

    Thanks,

    Z

    (0) 
    1. Veni Kosana Post author

      Thank you for your reply.

      I tried it but it didn’t work.

      It is not the same with the views,  I am able to work with the views.

      Thanks

      (0) 
  2. Zahid Hameed

    Veni,

    when you create a parameter within Stored Procedure, there is no need to create a parameter in Crystal, Crystal is gona carete the parameter automatically when you select the stored procedure as a data source the way you define in the stored procedure

    the problem you are having look like a SQL Server user permission issue, the user you are using to connect from Crystal must be sysadmin role

    and also i suggest to gave it a try using SQL Native Client Driver rather than ODBC

    Thanks,

    Z

    (0) 
  3. Noel Scheaffer

    Why do you need to use an output parameter?  Couldn’t you just add that data to the results of query in the stored procedure?

    Perhaps you could post your stored procedure (or a similar sample) here for review.

    Noel

    (0) 
    1. Veni Kosana Post author

      Below is the query

      SET

      ANSI_NULLS ON

      GO

      SET

      QUOTED_IDENTIFIER ON

      GO

      — =============================================

      ALTER

      PROCEDURE [dbo].[get_measures_with_output_para]

      — Add the parameters for the stored procedure here

      (@meas_system_seqno       Numeric(8),

      @inlet_rate VARCHAR(10) OUTPUT,

      @inlet_size VARCHAR(10) OUTPUT,

      @inlet_temp VARCHAR(10) OUTPUT)


      AS

      BEGIN

      SET NOCOUNT ON;

      select @inlet_rate = syum.unit_of_measure_label

      from

      sys_column_dict c inner join sys_table_dict t on t.sys_table_dict_seqno = c.sys_table_dict_seqno

      inner join measure_format mf on c.measure_domain_seqno = mf.measure_domain_seqno and

      sys_meas_system_seqno = @meas_system_seqno

      inner join sys_unit_of_measure syum on syum.sys_unit_of_measure_seqno = mf.sys_unit_of_measure_seqno

      where column_name = ‘inlet_rate’

      and table_name =

      (

      select table_name

      from sys_table_dict t1

      where t1.sys_table_dict_seqno = t.sys_table_dict_seqno

      );


      select @inlet_size = syum.unit_of_measure_label

      from sys_column_dict c inner join sys_table_dict t on t.sys_table_dict_seqno = c.sys_table_dict_seqno

      inner join measure_format mf on c.measure_domain_seqno = mf.measure_domain_seqno and

      sys_meas_system_seqno = @meas_system_seqno

      inner join sys_unit_of_measure syum on syum.sys_unit_of_measure_seqno = mf.sys_unit_of_measure_seqno

      where column_name = ‘inlet_size’

      and table_name =

      (

      select table_name

      from sys_table_dict t1

      where t1.sys_table_dict_seqno = t.sys_table_dict_seqno

      );


      select @inlet_temp = syum.unit_of_measure_label

      from sys_column_dict c inner join sys_table_dict t on t.sys_table_dict_seqno = c.sys_table_dict_seqno

      inner join measure_format mf on c.measure_domain_seqno = mf.measure_domain_seqno and

      sys_meas_system_seqno = @meas_system_seqno

      inner join sys_unit_of_measure syum on syum.sys_unit_of_measure_seqno = mf.sys_unit_of_measure_seqno

      where column_name = ‘inlet_temp’

      and table_name =

      (

      select table_name

      from sys_table_dict t1

      where t1.sys_table_dict_seqno = t.sys_table_dict_seqno

      );


      select

      @inlet_rate inletrate, @inlet_size inletsize, @inlet_temp inlettemp;

      END;

      The reason I am having three select statment was to get vlaues for different column_names like ‘inlet_rate’, ‘inlet_size’ and ‘inlet_temp’.

      I want all these three values in columns and not in rows.  So I have the last select statement to get the values into columns.

      I want to use these in my report to display the measures, I want to drag the columns where ever need in the main report or subreport and as many times as needed.. 

      Thanks.  I really appreciate that.


      (0) 
      1. Noel Scheaffer

        I think if you make your output parameters local variables instead you should be able to achieve your goal.  I think this should work.

        SET

        ANSI_NULLS ON

        GO

        SET

        QUOTED_IDENTIFIER ON

        GO

        — =============================================

        ALTER

        PROCEDURE [dbo].[get_measures_with_output_para]

        — Add the parameters for the stored procedure here

        (@meas_system_seqno       Numeric(8))

        AS

        BEGIN

        SET NOCOUNT ON;

        DECLARE @inlet_rate VARCHAR(10)

        DECLARE @inlet_size VARCHAR(10)

        DECLARE @inlet_temp VARCHAR(10)

        select @inlet_rate = syum.unit_of_measure_label

        from

        sys_column_dict c inner join sys_table_dict t on t.sys_table_dict_seqno = c.sys_table_dict_seqno

        inner join measure_format mf on c.measure_domain_seqno = mf.measure_domain_seqno and

        sys_meas_system_seqno = @meas_system_seqno

        inner join sys_unit_of_measure syum on syum.sys_unit_of_measure_seqno = mf.sys_unit_of_measure_seqno

        where column_name = ‘inlet_rate’

        and table_name =

        (

        select table_name

        from sys_table_dict t1

        where t1.sys_table_dict_seqno = t.sys_table_dict_seqno

        );

        select @inlet_size = syum.unit_of_measure_label

        from sys_column_dict c inner join sys_table_dict t on t.sys_table_dict_seqno = c.sys_table_dict_seqno

        inner join measure_format mf on c.measure_domain_seqno = mf.measure_domain_seqno and

        sys_meas_system_seqno = @meas_system_seqno

        inner join sys_unit_of_measure syum on syum.sys_unit_of_measure_seqno = mf.sys_unit_of_measure_seqno

        where column_name = ‘inlet_size’

        and table_name =

        (

        select table_name

        from sys_table_dict t1

        where t1.sys_table_dict_seqno = t.sys_table_dict_seqno

        );

        select @inlet_temp = syum.unit_of_measure_label

        from sys_column_dict c inner join sys_table_dict t on t.sys_table_dict_seqno = c.sys_table_dict_seqno

        inner join measure_format mf on c.measure_domain_seqno = mf.measure_domain_seqno and

        sys_meas_system_seqno = @meas_system_seqno

        inner join sys_unit_of_measure syum on syum.sys_unit_of_measure_seqno = mf.sys_unit_of_measure_seqno

        where column_name = ‘inlet_temp’

        and table_name =

        (

        select table_name

        from sys_table_dict t1

        where t1.sys_table_dict_seqno = t.sys_table_dict_seqno

        );

        select

        @inlet_rate inletrate, @inlet_size inletsize, @inlet_temp inlettemp;

        END;

        (0) 
          1. Veni Kosana Post author

            Noel,

            Thank you for your reply.

            I tried your idea.  I am able to link it to the data sources and use it in a new report without connecting it to any other tables.

            But If I use it in my exisitng report and link it to other table and run the report I get the following error. 

            failed to retrieve data from the database 08S01 [Microsoft] [SQL Native Client] Communication link

            I am tring to find out why this error is comming.  Any idea?

            Thanks a lot.

            (0) 
                1. Prathamesh Acharya

                  Veni,

                  Your query is appearing as a ‘Blog’. Can you please check if thats the case from your end andclose/delete/end this one.

                  Please re-post as a ‘Discussion’.

                  I liked Noel’s suggestion.

                  Since the rows-to-columns has been done in the SP, that could be the probable reason of the failure of linking with other tables.

                  However, you can workaround this one by using a main report-sub report approach.

                  More…..onceyou re-post this !!

                  Thanks,

                  Prathamesh

                  (0) 

Leave a Reply