Skip to Content
Author's profile photo Former Member

Database connector error in Crystal Report when accessing stored procedure

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

Assigned Tags

      12 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Noel Scheaffer
      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

      Author's profile photo Former Member
      Former Member

      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.


      Author's profile photo Noel Scheaffer
      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;

      Author's profile photo Noel Scheaffer
      Noel Scheaffer

      Veni,

      Did what I suggest make sense?  Did it work for you?

      Thanks,

      Noel

      Author's profile photo Former Member
      Former Member

      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.

      Author's profile photo Noel Scheaffer
      Noel Scheaffer

      I am not sure.  Sorry.

      Noel

      Author's profile photo Former Member
      Former Member

      No, problem.

      I will post the solution once I resolve this, it might help someone.

      Thanks

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      Sorry.  I am new to this. 

      Thanks