BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: No events within the next 45 days.

Database connector error in Crystal Reports storedprocedure


 
Search this topic... | Search Crystal Reports... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Crystal Reports  Previous TopicPrint TopicNext Topic
Author Message
KVK
Forum Member
Forum Member



Joined: 17 Apr 2012

Posts: 10



PostPosted: Fri Aug 10, 2012 2:09 pm 
Post subject: Database connector error in Crystal Reports storedprocedure

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


Last edited by KVK on Wed Aug 15, 2012 2:24 pm, edited 1 time in total
Back to top
KirtiKale
Forum Member
Forum Member



Joined: 13 Aug 2012

Posts: 1
Location: Pune


flag
PostPosted: Mon Aug 13, 2012 3:42 am 
Post subject: Re: Database connector error in Crystal Reports storedproce

Hello,

Have you compiled and executed stored procedure successfully at the backend?
If not, then please do it first then Log off and then log on your specified data source connection from Database(menu)>Log On or Off server... option.
Back to top
sudarsan29
Forum Member
Forum Member



Joined: 03 Aug 2012

Posts: 9



PostPosted: Mon Aug 13, 2012 3:51 am 
Post subject: Re: Database connector error in Crystal Reports storedproce

Can you try creating a stored procedure with the query and adding this to the report? if possible paste query here
Back to top
KVK
Forum Member
Forum Member



Joined: 17 Apr 2012

Posts: 10



PostPosted: Tue Aug 14, 2012 12:48 pm 
Post subject: Re: Database connector error in Crystal Reports storedproce

Yes, I compiled the stored procedure successfully and able to get the expected output in MS SQL Server 2005.

What I observed was once I am trying to get the output value I am getting the Database error. If I don't have any output parameters I am not getting the error.

I have pasted the query below.

Thank you



ALTER PROCEDURE [dbo].[get_measures]

-- Add the parameters for the stored procedure here
(@meas_system_seqno Numeric(icon_cool.gif,
@inlet_rate CHAR(10) OUTPUT,
@inlet_size CHAR(10) OUTPUT,
@inlet_temp CHAR(10) OUTPUT)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here

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
);


END;
Back to top
KVK
Forum Member
Forum Member



Joined: 17 Apr 2012

Posts: 10



PostPosted: Fri Aug 17, 2012 2:13 pm 
Post subject: Re: Database connector error in Crystal Reports storedproce

The below query worked for me. Just wanted to share, so it might help someone.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER
PROCEDURE [dbo].[get_measures_with_output_para]

(@meas_system_seqno Numeric(icon_cool.gif)

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;




Thanks
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Crystal Reports  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0286 seconds using 17 queries. (SQL 0.0027 Parse 0.0009 Other 0.0250)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo